7 ms·

I like to take every possible opportunity to sing the praises of SQLite, which is one of the most widely deployed RDBMS's in the world. Not only is it open sou

I like to take every possible opportunity to sing the praises of SQLite, which is one of the most widely deployed RDBMS's in the world. Not only is it open source, but it's public domain, and it's an incredibly stably developed and well-tested piece of software. Please note that it's not for every application. It is not a client/server architecture. It simply a tightly optimized piece of C code that interacts with a file (or RAM) to create a lightning fast approximation of a SQL server that is ACID compliant. One thing I've used it for the most, is for small, ad-hoc internal web applications. With the webserver as its "single user", it is incredibly speedy and reliable and holds a shockingly large amount of data (still works pretty well when data gets up into the Terabytes).

gerbil 13y ago
I have to agree wholeheartedly. I find SQlite a great choice for web apps and small DBD websites as it's so much easier to deploy and more than fast-enought for most projects. Plus it is easy to upgrade if the need arises as the queries are for the most-part identical to SQL.
ot 13y ago
> it's an incredibly stably developed and well-tested piece of software. It also has a policy of safe by default, fast at your risk (mmap is itself an example, it is disabled by default), which is the only sane attitude when working with databases. Contrast this to "modern" databases such as MongoDB, which is crazy fast out of the box, but then you start enabling durability, synchronous updates, etc... it becomes slower than standard DBs. This makes you win benchmarks, but in the long term causes a lot of headache and drives users away.
- threeseed 13y ago
  it becomes slower than standard DBs Sorry but either you're ignorant or being deliberately disingenuous. Either way this statement is completely untrue for many use cases. Can you not imagine situations where a document database would be orders of magnitude faster than ANY SQL one ? Think about a document with hundreds of embedded documents which equate to joins in SQL.
  - asperous 13y ago
    You can use SQL as a document store, you don't have to use joins. MongoDB queues inserts into memory before flushing it to disk. This means it's not durable in case of a power outage before the data is flushed. Turn on the safety of flushing-first and it's more or less doing the same stuff your sql software is doing.
    - lucian1900 13y ago
      Is is, in fact, much much slower than both SQLite and Postgres. It's even slower than a fully indexed hstore in postgres! (which is pretty much identical to mongo's data model, except consistent, durable, with transactions, etc.)
  - gngeal 13y ago
    That doesn't make sense. Relational databases use relational algebra (which includes joins) to model data. The mere fact that you model it that way doesn't necessarily have to affect the physical representation. True, there is no such thing as an actual RDBMS on the market, but that's hardly Dr. Codd's fault. :-)
mongol 13y ago
It is also an excellent tool to have in your command line toolkit. Rather than | wc | uniq | sort etc, pipe to file and bring into SQLite for more extensive querying.
- DigitalJack 13y ago
  That's pretty interesting. Can you give an example?
  - techtalsky 13y ago
    Just make a quick, loose, all-text-fields schema and dump your data as a CSV: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Then you can run SQL queries against it using the SQLite commandline tool: http://www.sqlite.org/sqlite.html http://www.sqlite.org/sqlite.html
  - deleted 13y ago
    [deleted]
  - deleted 13y ago
    [deleted]
- dalke 13y ago
  I've a different but related use case. I want to make a 5 GB file in a specific format, which requires that the data be sorted. I want it to run on my laptop machine, which has 4 GB of RAM. I dump the unsorted data to a sqlite database, tell it to use up to 1 GB of cache, create an index on the sort field, and pull back the sorted data, which I can stream directly to the file.
  - gngeal 13y ago
    What about simply using the original NoSQL system? http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/nosql/Home%20Page http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/nosql/Home%20P...
    - 13y ago
wslh 13y ago
Yes, and I don't understand why Wordpress does not support it officially and we need to use MySQL instead.
- techtalsky 13y ago
  I think WordPress just tries to cut complexity by using MySQL only so it doesn't have to deal with support for arbitrary RDBMS's.
  - TazeTSchnitzel 13y ago
    More likely, the code is poorly written and heavily reliant on MySQL.
    - jacques_chester 13y ago
      This is more or less the correct answer for Wordpress-the-ecosystem. Wordpress-the-base-install has a thin abstraction layer over the database in the $wpdb object. But plugins and themes can, and regularly do, use PHP's MySQL functions to access and manipulate the DB directly.
- glurgh 13y ago
  The theoretical answer is because SQLite is not the right tool for the sort of things Wordpress does. SQLite supports more concurrency than one might expect from an embedded db - it's still an embedded db, though. Writing, for instance involves a database-global lock. The practical answer is - Wordpress is written in a MySQL-specific way. It doesn't officially support, say, Postgres or Oracle either.
  - masklinn 13y ago
    > The theoretical answer is because SQLite is not the right tool for the sort of things Wordpress does. Which probably isn't true. SQLite is bad at concurrent writes. Your average wordpress install is almost only reads. And with 3.7.0's WAL, readers and writers don't block one another anymore.
TazeTSchnitzel 13y ago
I love using SQLite for websites. Unlike MySQL, SQL is a well-written and predictable database, there's no daemon, users or permissions to worry about, and the runtime is built into Python!
- IanChiles 13y ago
  You have to be careful though, as SQLite doesn't support concurrent read/writes, so it blocks, which makes it rather difficult to scale up for websites, or at least it has in my experience with it.
  - genwin 13y ago
    Yes. In the Go language one can use channels to funnel the data to/from a single process that does the SQLite reads/writes. It's trivial to batch up data in transactions as it comes in from users (the current channel length is the number of rows to include in the transaction).
    - tocomment 13y ago
      Could you explain this a bit more, or point me to a tutorial? That actually sounds really promising.
      - genwin 13y ago
        This is how you'd perpetually write data from all users using a single process, batching the data into transactions for better performance; there's not much new here that isn't covered in most tutorials: On app init create a channel into which data will be put as it comes in from users, and start a goroutine that is passed the channel. In the goroutine have an endless for loop, inside of which you wait for data to arrive in the channel, using a select statement. When data arrives start a SQLite transaction, then iterate the length of the channel in a for loop, writing rows to the SQLite table. (The length of the channel is the number of data values currently in it, which will vary depending on how fast you're putting data into it from elsewhere in the app.) After that loop you commit the transaction.
eliben 13y ago
> which is one of the most widely deployed RDBMS's in the world Being inside Chrome, Android, Firefox, Skype and other places (http://www.sqlite.org/famous.html http://www.sqlite.org/famous.html), I'd argue that SQLite is by far the most widely deployed RDBMS in the world :-) And, BTW, I love it too.
- etanol 13y ago
  It is interesting how SQLite is so well known but de SCM built on top of it, Fossil, and by the same author did not catch so much.
  - asperous 13y ago
    Sqlite filled a desperate need with no contenders, while Fossil, released in 2006, was going up against git and hg (both released a year earlier). Though Fossil is much more than a SCM (wiki, bug tracker, blog) and while not mainstream, is still used by tcl.tk and others.
    - bch 13y ago
      SQLite itself is hosted on fossil, as is Tcl/Tk as you mention (and it's various satellite projects), and I know NetBSD was flirting with it too (no formal decision made yet on migrating from CVS afaik). I use fossil for all of my personal projects. It's more than capable, and a joy to use. Everybody should try it for a week. http://www.fossil-scm.org/ http://www.fossil-scm.org/
mercurial 13y ago
One potential pitfall of SQLite is that datatypes declared in your CREATE TABLE statements have purely indicative value. You can in effect store strings or booleans in an integer column. I'd welcome a 'strict mode' which would do away with weak typing and behave more like traditional SQL databases.

gerbil 13y ago

I have to agree wholeheartedly. I find SQlite a great choice for web apps and small DBD websites as it's so much easier to deploy and more than fast-enought for most projects. Plus it is easy to upgrade if the need arises as the queries are for the most-part identical to SQL.

ot 13y ago

> it's an incredibly stably developed and well-tested piece of software. It also has a policy of safe by default, fast at your risk (mmap is itself an example, it is disabled by default), which is the only sane attitude when working with databases. Contrast this to "modern" databases such as MongoDB, which is crazy fast out of the box, but then you start enabling durability, synchronous updates, etc... it becomes slower than standard DBs. This makes you win benchmarks, but in the long term causes a lot of headache and drives users away.

threeseed 13y ago
it becomes slower than standard DBs Sorry but either you're ignorant or being deliberately disingenuous. Either way this statement is completely untrue for many use cases. Can you not imagine situations where a document database would be orders of magnitude faster than ANY SQL one ? Think about a document with hundreds of embedded documents which equate to joins in SQL.
- asperous 13y ago
  You can use SQL as a document store, you don't have to use joins. MongoDB queues inserts into memory before flushing it to disk. This means it's not durable in case of a power outage before the data is flushed. Turn on the safety of flushing-first and it's more or less doing the same stuff your sql software is doing.
  - lucian1900 13y ago
    Is is, in fact, much much slower than both SQLite and Postgres. It's even slower than a fully indexed hstore in postgres! (which is pretty much identical to mongo's data model, except consistent, durable, with transactions, etc.)
- gngeal 13y ago
  That doesn't make sense. Relational databases use relational algebra (which includes joins) to model data. The mere fact that you model it that way doesn't necessarily have to affect the physical representation. True, there is no such thing as an actual RDBMS on the market, but that's hardly Dr. Codd's fault. :-)

mongol 13y ago

It is also an excellent tool to have in your command line toolkit. Rather than | wc | uniq | sort etc, pipe to file and bring into SQLite for more extensive querying.

DigitalJack 13y ago
That's pretty interesting. Can you give an example?
- techtalsky 13y ago
  Just make a quick, loose, all-text-fields schema and dump your data as a CSV: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Then you can run SQL queries against it using the SQLite commandline tool: http://www.sqlite.org/sqlite.html http://www.sqlite.org/sqlite.html
- deleted 13y ago
  [deleted]
- deleted 13y ago
  [deleted]
dalke 13y ago
I've a different but related use case. I want to make a 5 GB file in a specific format, which requires that the data be sorted. I want it to run on my laptop machine, which has 4 GB of RAM. I dump the unsorted data to a sqlite database, tell it to use up to 1 GB of cache, create an index on the sort field, and pull back the sorted data, which I can stream directly to the file.
- gngeal 13y ago
  What about simply using the original NoSQL system? http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/nosql/Home%20Page http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/nosql/Home%20P...
  - 13y ago

wslh 13y ago

Yes, and I don't understand why Wordpress does not support it officially and we need to use MySQL instead.

techtalsky 13y ago
I think WordPress just tries to cut complexity by using MySQL only so it doesn't have to deal with support for arbitrary RDBMS's.
- TazeTSchnitzel 13y ago
  More likely, the code is poorly written and heavily reliant on MySQL.
  - jacques_chester 13y ago
    This is more or less the correct answer for Wordpress-the-ecosystem. Wordpress-the-base-install has a thin abstraction layer over the database in the $wpdb object. But plugins and themes can, and regularly do, use PHP's MySQL functions to access and manipulate the DB directly.
glurgh 13y ago
The theoretical answer is because SQLite is not the right tool for the sort of things Wordpress does. SQLite supports more concurrency than one might expect from an embedded db - it's still an embedded db, though. Writing, for instance involves a database-global lock. The practical answer is - Wordpress is written in a MySQL-specific way. It doesn't officially support, say, Postgres or Oracle either.
- masklinn 13y ago
  > The theoretical answer is because SQLite is not the right tool for the sort of things Wordpress does. Which probably isn't true. SQLite is bad at concurrent writes. Your average wordpress install is almost only reads. And with 3.7.0's WAL, readers and writers don't block one another anymore.

TazeTSchnitzel 13y ago

I love using SQLite for websites. Unlike MySQL, SQL is a well-written and predictable database, there's no daemon, users or permissions to worry about, and the runtime is built into Python!

IanChiles 13y ago
You have to be careful though, as SQLite doesn't support concurrent read/writes, so it blocks, which makes it rather difficult to scale up for websites, or at least it has in my experience with it.
- genwin 13y ago
  Yes. In the Go language one can use channels to funnel the data to/from a single process that does the SQLite reads/writes. It's trivial to batch up data in transactions as it comes in from users (the current channel length is the number of rows to include in the transaction).
  - tocomment 13y ago
    Could you explain this a bit more, or point me to a tutorial? That actually sounds really promising.
    - genwin 13y ago
      This is how you'd perpetually write data from all users using a single process, batching the data into transactions for better performance; there's not much new here that isn't covered in most tutorials: On app init create a channel into which data will be put as it comes in from users, and start a goroutine that is passed the channel. In the goroutine have an endless for loop, inside of which you wait for data to arrive in the channel, using a select statement. When data arrives start a SQLite transaction, then iterate the length of the channel in a for loop, writing rows to the SQLite table. (The length of the channel is the number of data values currently in it, which will vary depending on how fast you're putting data into it from elsewhere in the app.) After that loop you commit the transaction.

eliben 13y ago

> which is one of the most widely deployed RDBMS's in the world Being inside Chrome, Android, Firefox, Skype and other places (http://www.sqlite.org/famous.html http://www.sqlite.org/famous.html), I'd argue that SQLite is by far the most widely deployed RDBMS in the world :-) And, BTW, I love it too.

etanol 13y ago
It is interesting how SQLite is so well known but de SCM built on top of it, Fossil, and by the same author did not catch so much.
- asperous 13y ago
  Sqlite filled a desperate need with no contenders, while Fossil, released in 2006, was going up against git and hg (both released a year earlier). Though Fossil is much more than a SCM (wiki, bug tracker, blog) and while not mainstream, is still used by tcl.tk and others.
  - bch 13y ago
    SQLite itself is hosted on fossil, as is Tcl/Tk as you mention (and it's various satellite projects), and I know NetBSD was flirting with it too (no formal decision made yet on migrating from CVS afaik). I use fossil for all of my personal projects. It's more than capable, and a joy to use. Everybody should try it for a week. http://www.fossil-scm.org/ http://www.fossil-scm.org/

mercurial 13y ago

One potential pitfall of SQLite is that datatypes declared in your CREATE TABLE statements have purely indicative value. You can in effect store strings or booleans in an integer column. I'd welcome a 'strict mode' which would do away with weak typing and behave more like traditional SQL databases.