SQLite: Small, Fast, Reliable – Choose any three
charlesleifer.com
charlesleifer.com
Overall, the experience has been a joy. Our application is an engineering simulation application. Our core simulator is written in Matlab for historical reasons, and we can communicate data easily using the database. Writing GUI models and views over the database on the Python end of things is very straightforward, once you have your SQLAlchemy models set up.
I agree that migrations can be a pain, but thankfully our tables are usually small enough that we can alter tables simply be recreating them. I also found this [1] StackOverflow answer that explains how to easily change a column name in place.
Those looking for a GUI to view SQLite databases should check out sqlitebrowser [2]. It's the best I've seen, and the developers are very responsive to bug reports and pull requests. It is also cross platform, and is present in many Linux package repos.
[0] http://www.sqlite.org/appfileformat.html
Recently I've had some success with speeding up (up to 4 threads) on scaling reads - e.g. SELECT xx FROM yy LIMIT ll OFFSET oo ORDER BY zz - where it spreads on different threads "ll" and "oo". Now this ignores the fact that the db might be changed and wont get the same results for each thread (I can't get the same transaction number from different threads/procs as I can with PG) but still works for some tasks.
Recently introduced CTE's are killer feature, since we have a bit of parent/children derived column values, and we keep filling values from the parent into the children, while with CTE this could be evaluated everytime (at some cost, but then less data being filled overall).
Anyone found anything better? And, yes, I know it ships with command line tools but particularly for development it is nicer to have a GUI to quickly set up tables.
Firefox uses SQLite for a bunch of internal data and the bindings are accessible to extensions. So basically Firefox is a cross-OS platform that's guaranteed to be linked against SQLite, with public bindings.
It probably works better than many of the Java-based 'cross-platform' solutions to many problems...
[0] https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage...
I use it sometimes to see table overviews, but generally just work from command line or a short python script specific to what I'm doing.
Edit: Beat by 30 seconds! Ah well.. I guess now you have two recommendations for sqlite manager.
Edit Edit: Now 3 recommendations. I guess the plugin is pretty popular.
(Mac only)
http://www.nucleonsoftware.com/Products/Database-Master/Data...
It's not open source and it's Windows only, but there's a free edition that lets me do most things with SQLite and MongoDB.
P.D: Just because is a commercial product down vote the parent? In this area (Sql managers) the commercial ones are better than the open source ones, by a LOT.
http://www.heidisql.com/download.php
Heidi also runs on Wine as well, so something for everyone :)
Aside from that, the IDE will also detect SQL queries in one's code and syntax check them. It also lets you run the query directly from there with user generated parameters without having to execute the code file itself.
My favourite was SqliteSpy but its statically linked, so had to switch to sqlitebrowser, and compile it with our own custom build of qt5 where "system" sqlite (our custom one) is used.
Maybe one good case where DLL/.so/.dylib shine (if only golang... :) )
Possibly, but it's mostly only useful if you can't recompile the app (i.e. don't have source).
And in that case, you've probably paid money for it, and want support. Which you won't get if you've swapped out a component for one it wasn't tested with.
[But I agree with your general point. DLL search path/LD_LIBRARY_PATH can be a useful 'speed hack' to drop in a different lib with an identical API and minor tweaks. e.g. overload malloc/free/realloc() in some misbehaving app to trace a problem without recompiling.]
Timing 100k reads from database: MongoDB: 43.3 s SQLite: 19.4 s
Same test with 4 parallel threads MongoDB: 29.9 s SQLite: 25.1 s
So as we can see, SQLite is much faster for single thread batch processing that most of other databases. With 4 concurrent threads SQLite is still faster than MongoDB.
About web sites, when using WAL mode, I can handle easily over 200 write transactions / second using very light single core VPS server with SSD. This means that it should be trivial to handle at least a few million hits / day, each with a few write transctions. Basically other things start to block at least with that server, before the pure database lock, write, release cycle becomes the bottle neck.
Edit:
Hold on. As per Slashdot[0] and the license itself, BerkleyDB Open Source is licensed under AGPL, which means that it will be triggered even as part of a web service. That sort of rules it out for me personally, although I'm curious how much a commercial license would cost.
[0] http://developers.slashdot.org/story/13/07/05/1647215/oracle...
This way you can give a full SQL engine to any key-value store out there..
Thats why the Sqlite4 are being designed to be more plugable.. with a shim key-value wrapper over the storage backend that can be changed, in compile time, or even in runtime given its use of C callbacks.. (but dont know any reason someone would want to to that.. to do a runtime switch anyway)
From clause 13 of the AGPL: Notwithstanding any other provision of this License, you have permission to link or combine any covered work with a work licensed under version 3 of the GNU General Public License into a single combined work, and to convey the resulting work. The terms of this License will continue to apply to the part which is the covered work, but the work with which it is combined will remain governed by version 3 of the GNU General Public License.
[1] http://www.gnu.org/licenses/agpl.html
[2] https://en.wikipedia.org/wiki/Affero_General_Public_License#...
Discovering design notes in old textbooks is one of the joys of buying used books.
One item I had hoped to read, which often is mentioned on SQLite reviews, occurs in the area of "When would SQLite not be a good choice?", specifically: "very large amount of traffic... Very large data-sets." I have always hoped for some (even wild) estimates of when that 'very large-ness' occurs. because in my hands, 17 million small molecule (inchi) structures don't even cause it to break a sweat. Will i hit a wall some day?
> An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
Very large amount of traffic:
> SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
Also, (I believe) it was this talk http://www.youtube.com/watch?v=ZvmMzI0X7fE that mentions sqlite being used in adobe lightroom, and it being faster to access thumbnails from an sqlite database.
The one thing that SQLite cannot handle performantly is deleting large numbers of rows (millions) - so don't plan on deleting any data from your tables once they get that big. It appeared to me from a cursory examination of the code that B-tree rebalancing was happening after deleting each row which makes the big deletes very expensive. We got around this problem by sharding our data into a new table and a new database for each week and then mounting all of the databases necessary for a query. When we wanted to delete data we just deleted the database file with the corresponding shard. Obviously that only worked for our particular time series data.
Anyway, the bottom line is that SQLite is more scalable and has better performance than people give it credit for.
They compared it against all the "similar" dbs (embedded, key-value) and it turns out to be the best in benchmarks.
I wrote some python bindings using ctypes if you happen to be using python:
http://charlesleifer.com/blog/python-bindings-for-unqlite-an...
Also, The project I'm working on is a multi-client thing, but the vast majority of what happens would be a silo'd situation on Postgres. The webapp itself and site structure would be shared, but clients would create projects for data processing and analytics. Would it be reasonable to just create a new SQLite db file per project? In some ways that would make backups easier by project, but dumping all data for a client would kind of be a pain. Are there other gotchas about a structure like this?
> An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
Since the whole database is in one file though 140TB may be the filesize limit, searching through a index of 140TB data will still be a lot slower. That is the case even with client-server models or even with Hadoop.
Most people claiming to have a big data problem, actually don't have one. Its just bad understanding of SQL, coupled with NoSQL fashion which powers people to opt out of SQL. One more problem with SQL is, its a career path in itself. There is whole industry built around it, data base design, administration etc etc. And people who find this as high barrier to entry, take the easy way out and choose NoSQL based tools hoping it will act as a panacea- Only to re implement SQL badly at some point in their stack. But SQL has other advantages, it teaches you think about efficient representation of data. Which in turn leads to an overall better design of everything that connects it.
For most of your everyday so called 'Big data' problems, SQLite will work like charm. This covers most of the shops that claim to be doing big data work.
For the real big data problems, well then SQLite wasn't designed for it anyway.
But I really didn't know one could do concurrent writes with Python and Sqlite. I've always been very careful after getting burnt, so this is a good article. I love Sqlite and use it all the time and wish I could use it more. The single file format and lack of complex setup is pure joy.
I looked into switching to Redis AOF or some other append-only system, but adding UPSs and switching to Linux-based systems w power loss protected SSDs seemed solve the problems.
However, we found the WP version of SQLite to be much much slower than SQLite on iOS and Android, where it is built-in. On WP, SQLite cost about 3 seconds extra on application start up. Eventually we replaced SQLite with custom json serialization to get the the same performance as iOS/Android.
SQLite is great and the obvious choice for structured data embedded in an application or on an embedded platform.
SQLite is not meant to be a multi-user shared database. It's never going to replace a database server if that's what you need.
The reverse of your question is: is there a good reason to use PostgreSQL when you can use SQLite?
On the other hand, if SQLite's flavor of SQL is missing some important stuff (I have no idea what) then maybe I should stick with MySQL.
As a rule of thumb, it should be fine if your goal is to learn the basics of SQL, and not-so-fine if you're looking to learn database administration.
I would not use it to learn how to alter tables, or even how to create and enforce a schema, as it doesn't do either quite like some of the other popular dbs out there.
There are work arounds, of course, but I think it's a pretty big downside that it's difficult to set up robust replication with sqlite. It's certainly one of the things that cause people to say that it's not a "real" database.
1. Can not rename column, in a fast iteration project, renaming schema is quite common
2. No easy way to upsert data. There should be an easy equivalent to MySQL's "INSERT ... ON DUPLICATE KEY UPDATE". If you use REPLACE you got PK changed.
INSERT OR IGNORE INTO table_name (item_key, item_count) VALUES (?, 0);
UPDATE table_name SET item_count = item_count+1 WHERE item_key = ?;