Quick, what’s the most widely deployed database in the world? MySQL? Oracle? Nope, it’s that puny squirt SQLite – the one you see listed as an option every time you set up a new Rails or Django site. SQLite is not a database server, but a SQL-compatible interface to a single file — perfect for every application that needs a full relational database without the overhead and complexity of a client/server connection. In other words, it’s in your smartphone. It’s on your Mac (driving the indexes in Mail.app, plus the databases behind Aperture and Safari). It’s in Adobe AIR. It’s in FireFox. It’s in Android. It’s in a bunch of giant products and services that will never admit to it.
SQLite is fast. Hella fast. Not great with massive concurrency or transactions, but runs circles around its more popular big brothers in simple SELECT/INSERT statements in moderate-traffic environments. Apparently it can be used to run production web applications if you don’t transactions or permissions (“If your site is small enough to run on a single server, it’ll rock on SQLite.”)
Excellent interview with SQLite’s creator Richard Hipp in this week’s FLOSS. Incredibly humble guy. Talks like his accomplishments ain’t no big thing. But his software changed the world. And instead of making a million bucks off it, he took his name out of the source code and released it into the public domain. Now he makes his living doing customizations for giant corps. Altruism can be good business.
It appears that as of today, when you posted this article, the episode of FLOSS is now officially “last week’s episode”. :)
So much for taxonomy. But my first link above points to the permalink.
Huh. I use SQLite all the time in boostrapping, early dev situations, but I never realized it scaled up to production so well. Thanks for the tip!
Great for reads, but the whole file gets locked during a write. So *database* level locking, from what I hear.
I already have mysql running everywhere. No reason to start using sqlite now. I’m happy not learning something new.
So yup, great for the majority of web sites/apps, and especially for the small devices/footprint folks.
If you’re a great developer the real money has always been in consulting. So he might not be as altruistic as wise. ;)
Yes, but for microseconds (literally). Yes, other requests will get queued, but unless you’re doing huge traffic, no practical downside. I don’t think anyone’s suggesting that you actually should run SQLite in production, only that it might not be as crazy as it sounds unless you’re doing really big traffic or need transactions or a few other fairly obscure features.
From the sound of it, I think he put it into public domain well before there was any prospect of making money from consulting. But that’s my take just based on his manner and tone.
Depends on the query. I’m sure there’s a benchmark out there somewhere on how sqlite performs.
Here are some:
Older (“The numbers here are old enough to be nearly meaningless”)
Newer (but still somewhat old)
If I remember from the interview, the performance synopsis is something like “In low concurrency environments, SQLite runs circles around everything else. In high concurrency environments, other DBs eat its lunch.”
The public domain part is interesting and has actually caused some companies to think twice before adopting it because lawyers are used to dealing with open source licenses but since public domain projects are rare the legal issues are untested. This seems funny since you would think there are no legal issues with public domain.
One very convenient aspect of SQLite is that it is zero configuration. It doesn’t require a password, and you don’t need to be a database admin to use it. It is also good for cross platform projects. We adopted SQLite for our Run BASIC web development system (http://www.runbasic.com).
Shacker wrote:
“I don’t think anyone’s suggesting that you actually should run SQLite in production…”. The SQLite web site itself is powered by a standalone web server called cvstrac which is made up of SQLite , CVS, and a web server, bug tracker, etc. Point is…SQLite is used in production and that site runs fine, and I’m sure they get lots of hits every day.