Let’s get into it. SQLite kicks arse! It wipes the floor with MySQL and comes back for more. The only time not to use SQLite is when your app goes viral and you have six or seven figure hits per day at which point you should go to Postgres.
Where does MySQL fit in? Nowhere!
What are DDLs and what does it mean to have Transactional DDLs?
Why does SQLite rule, and why would I consider it for production?
What does “lite” really mean?
Read on…
DDL - Data Definition Language
DDLs are the parts of SQL responsible for defining the data structure. Things like CREATE TABLE and ALTER TABLE and DROP TABLE.
Transactional DDLs
It’s a Big Deal™ to be able to wrap DDL statements in transactions. It means that you can have atomicity around the statements that actually change the schema of your database. One could argue, that transactional DDLs is actually more important than having DML (Data Manipulation Language) statements transactional.
When you make schema changes, you might need to update a bunch of foreign-keys, set a heap of default values, or create indexes that are crucial to your app’s success.
If a glitch in your SQL causes a schema change to fail part-way through then not just your database, but the schema itself is left in an unknown state. Your app could be crippled.
Worst of all, if you’re talking about adding some new constraint or something like that that’s data-dependent, and you’re talking about doing this on a live database, you can’t actually be sure the change will succeed (unless you take your DB offline) until you actually do it.
SQLite and Transactional DDLs
SQLite has had Transactional DDLs since version 2.0.0 (think September 2001) and Rails has just been patched (version 2.3.2) to handle/recognize this.
What this means is that for Rails users, a migration is now an atomic operation.
The whole thing either succeeds (including the update to schema_migrations or the whole thing fails. No more manual fixup required when a migration fails! Who can say “Sweet development!”
SQLite > MySQL
MySQL still has no support for transactional DDLs. Never has, and maybe never will. It doesn’t seem to be on their radar at all (like Oracle the MySQL devs seem happy with only DMLs in transactions).
SQLite will happily run 99% of websites (99.999% probably). The rule of thumb from the author of SQLite is that if your site is getting fewer than 100k page hits a day - then SQLite will be fine.
Why MySQL then?
So, why ever use MySQL? I really can’t answer.
It has failed numerous ACID tests over the years (caveat: I have no information about versions 5 or 6). It is slower than SQLite on all but the largest databases/websites. And it doesn’t support transactional DDLs.
As far as I can tell there are two choices of DB engine. SQLite for anything under 100k hits per day, and Postgres for anything above that.
Comments
There are 6 comments on this post. Post yours →
ok I’m convinced … almost. Some questions. Are there any issues with UTF8 encodings? Any suggestions for database browser tools for sqlite? cheers
I can’t speak to using SQLite, but I’ve been using PostgreSQL on my current project. PostgreSQL also has transactional DDL and it is awesome. No more half bungled migrations that need to be manually fixed! They either work or they don’t; and if they don’t, you can fix and run again without any problems. I love it.
@Jeem - native UTF-8 support has been in all SQLite releases and since 2.0.6 in October 2001 you’ve been able to use UTF-8 for table and column names as well. So it’s very mature. UTF-8 is the default encoding.
UTF-16 has been supported in all 3.x versions (circa mid-2004). You can now specify BE and LE UTF-16 encoding or nothing to use your system’s default.
@Luke - yeah, it’s a dream. So Rails 2.3 was released a couple of days ago and has this goodness baked in for SQLite. It’s a dream during development, and it’s even better with production when you’re adding a unique index or some constraint and you don’t have to obsess about the migration and all the possible failure scenarios.
@Jeem - sorry, I didn’t answer the second question you had. The SQLite site itself has some management tools listed which is as much as I know about GUIs for SQLite (I’m a command line junkie).
So what if you have more than one webserver that needs to hit your database?
@Ryan - SQLite is one of the only serverless DBs that enables so much concurrency.
Any number of processes can have the DB file open at once - and all can read the DB at once.
Write concurrency is done with the fcntl locking, and only one process can write at a time, but the locks are only held for the life of the actual write - so typically this is milliseconds per write.
Obviously if you have remote process(es) that can’t fcntl lock the file then you need a server-based DB - the answer still isn’t MySQL, at that point the answer is Postgres :)
Post a comment
Required fields look like this.