4 ms·

I am seeing SQLite pop up more and more in articles that tout its effectiveness and simplicity for many use cases over popular alternatives like PostgreSQL. It

I am seeing SQLite pop up more and more in articles that tout its effectiveness and simplicity for many use cases over popular alternatives like PostgreSQL. It has definitely persuaded me to consider it on future projects.

gaius 8y ago
SQLite is a beautiful little jewel! Every time I think "does it have X feature" it always delights me that it does! And the code is exemplary as well, you can learn loads from just reading it. If you don't need concurrent writers, then SQLite goes a very, very long way.
dom96 8y ago
Oh yeah. SQLite is incredibly versatile and for most web applications it's more than enough. The only pain point I have with it is that all data is stored as a string no matter the table schema types specified, this can lead to some bugs, but if you have a good library in your language for SQLite then it's not a problem.
- hellofunk 8y ago
  I am currently working with a data set that is billions of rows and tens of GB. Will be interesting to see how SQLite handles it, which I plan to try. All of that in one DB file!
  - xyproto 8y ago
    Should be fine. SQLite is fast and capable.
  - dagw 8y ago
    The main place you might run into problems is if doing lots of simultaneous writes. As long you're mostly dealing with reads then it should work.
- hellofunk 8y ago
  > all data is stored as a string no matter the table schema types specified The docs seem to suggest otherwise: > if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. From: https://www.sqlite.org/faq.html#q3 https://www.sqlite.org/faq.html#q3
  - tonyarkles 8y ago
    >The docs seem to suggest otherwise: >> if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. There's nuance to your quote. From my recollection, this means that "321a" will be inserted as "321", but "foo" will be inserted as "foo" (into an INTEGER column). Definitely a wart, on an otherwise fantastic system.
    - hellofunk 8y ago
      But “321” is still a string, not an integer.
    - SQLite 8y ago
      Not quite right. The expression "CAST('321a' AS INTEGER)" will do as you suggest and ignore the trailing 'a' character, yielding an integer 123 result. But that only happens for an explicit CAST. Automatic type conversions must be reversible. That means that '321a' is inserted as a string in an INTEGER column, but '321' (without the trailing 'a') will be converted into an integer 123. PostgreSQL, MySQL, and SQL Server do exactly the same thing for the '321' case. For the '321a' case, the other three throw an error whereas SQLite just cancels the type conversion and inserts the original string.
      - tonyarkles 8y ago
        Ahhh cool! Either way, the fact that you can end up with strings in an Integer column is certainly surprising... sqlite> create table test (foo INTEGER); sqlite> insert into test (foo) values (123); sqlite> insert into test (foo) values ("blah"); sqlite> insert into test (foo) values ("123a"); sqlite> select * from test; 123 blah 123a
- PretzelFisch 8y ago
  How are you handling writes to the data? SQLite isn't known for handling concurrent writes which you need in most basic web applications.
  - hellofunk 8y ago
    That would be a good question for the author of this article’s web stack. There is some explanation here: https://learnbchs.org/ksql.html https://learnbchs.org/ksql.html
  - hellofunk 8y ago
    I think the web library, if it supports SQLite, must manage the writes to that database. Django uses SQLite by default, but if you have it running on just one server, I would expect that Django coordinates the needed writes from all its simultaneous users in a queue or something, one at a time.
    - marktangotango 8y ago
      Has anyone verified this is the case? I tend to doubt django does it. Using a few tricks SQLite easily beats postgres insert performance, just a little obscure.
bpicolo 8y ago
It's great for simple things, but if you're going to need writes from >1 server it's not what you're looking for
cup-of-tea 8y ago
I've used it for many things but for Web stuff it's the django default database. I've actually put that into internal production but I used only the test Web server (one instance). Is it possible to just use gunicorn or something with multiple workers and sqlite with no problems?

gaius 8y ago

SQLite is a beautiful little jewel! Every time I think "does it have X feature" it always delights me that it does! And the code is exemplary as well, you can learn loads from just reading it. If you don't need concurrent writers, then SQLite goes a very, very long way.

dom96 8y ago

Oh yeah. SQLite is incredibly versatile and for most web applications it's more than enough. The only pain point I have with it is that all data is stored as a string no matter the table schema types specified, this can lead to some bugs, but if you have a good library in your language for SQLite then it's not a problem.

hellofunk 8y ago
I am currently working with a data set that is billions of rows and tens of GB. Will be interesting to see how SQLite handles it, which I plan to try. All of that in one DB file!
- xyproto 8y ago
  Should be fine. SQLite is fast and capable.
- dagw 8y ago
  The main place you might run into problems is if doing lots of simultaneous writes. As long you're mostly dealing with reads then it should work.
hellofunk 8y ago
> all data is stored as a string no matter the table schema types specified The docs seem to suggest otherwise: > if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. From: https://www.sqlite.org/faq.html#q3 https://www.sqlite.org/faq.html#q3
- tonyarkles 8y ago
  >The docs seem to suggest otherwise: >> if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. There's nuance to your quote. From my recollection, this means that "321a" will be inserted as "321", but "foo" will be inserted as "foo" (into an INTEGER column). Definitely a wart, on an otherwise fantastic system.
  - hellofunk 8y ago
    But “321” is still a string, not an integer.
  - SQLite 8y ago
    Not quite right. The expression "CAST('321a' AS INTEGER)" will do as you suggest and ignore the trailing 'a' character, yielding an integer 123 result. But that only happens for an explicit CAST. Automatic type conversions must be reversible. That means that '321a' is inserted as a string in an INTEGER column, but '321' (without the trailing 'a') will be converted into an integer 123. PostgreSQL, MySQL, and SQL Server do exactly the same thing for the '321' case. For the '321a' case, the other three throw an error whereas SQLite just cancels the type conversion and inserts the original string.
    - tonyarkles 8y ago
      Ahhh cool! Either way, the fact that you can end up with strings in an Integer column is certainly surprising... sqlite> create table test (foo INTEGER); sqlite> insert into test (foo) values (123); sqlite> insert into test (foo) values ("blah"); sqlite> insert into test (foo) values ("123a"); sqlite> select * from test; 123 blah 123a
PretzelFisch 8y ago
How are you handling writes to the data? SQLite isn't known for handling concurrent writes which you need in most basic web applications.
- hellofunk 8y ago
  That would be a good question for the author of this article’s web stack. There is some explanation here: https://learnbchs.org/ksql.html https://learnbchs.org/ksql.html
- hellofunk 8y ago
  I think the web library, if it supports SQLite, must manage the writes to that database. Django uses SQLite by default, but if you have it running on just one server, I would expect that Django coordinates the needed writes from all its simultaneous users in a queue or something, one at a time.
  - marktangotango 8y ago
    Has anyone verified this is the case? I tend to doubt django does it. Using a few tricks SQLite easily beats postgres insert performance, just a little obscure.

bpicolo 8y ago

It's great for simple things, but if you're going to need writes from >1 server it's not what you're looking for

cup-of-tea 8y ago

I've used it for many things but for Web stuff it's the django default database. I've actually put that into internal production but I used only the test Web server (one instance). Is it possible to just use gunicorn or something with multiple workers and sqlite with no problems?