Here are some reasons why you should be using SQLite with your apps:
It is fast
SQLite is an embedded database so the database engine gets built right into your app. It is also designed for incredible speed. It can handle millions of rows with ease.
It is typeless
Unlike other databases, SQLite does not care what type of data you put into a column. In fact, it really doesn’t use types at all! Columns are assigned an “affinity” of INTEGER, REAL, TEXT and BLOB which helps determine how they are used for queries and functions. But you can stick text data into an integer column without a problem. You can read more about SQLite’s unique handling of Data Types on their documentation page.
It is cross-platform
SQLite works on every platform imaginable, including: Windows, macOS, Linux, Raspberry Pi, web, iOS and Android. Additionally, the database files themselves are cross-platform. So if you create a database and populate with data on iOS, you can move the file over to Windows and be able to read, write and otherwise use it without trouble. This makes SQLite a great fit for cross-platform development tools like Xojo.
It is public domain
Because it is public domain, SQLite is complete free to use with no restrictions of any kind. And the full source code is readily available, of course.
It works with most programming languages
Most programming languages have built-in support for SQLite, including: Delphi, C, C#, C++, Go, Java, JavaScript, LiveCode, Lue, Objective-C, Perl, PHP, Python, Ruby, Visual Basic and Xojo. With Xojo you can use SQLite with desktop, web, console and iOS apps.
It is updated often
SQLite is a mature database with few bugs, but it is updated often to ensure it remains at its best quality. If any bugs sneak in, they are quickly squashed.
It is fast
SQLite is so fast that it is worth saying again. Seriously, it is fast. Don’t use a text file or JSON or some other thing that will likely be much slower, especially when the files get large. Use SQLite and you’ll be happier.
It is used everywhere
It is a good bet that apps you rely on are using SQLite in some way. It is a great storage mechanism for preferences, general app data and can even be used as the app file format itself. For example, Apple Mail uses SQLite to save email messages and Firefox uses SQLite databases to store much of its profile information.
Free Videos
For more information, check out these videos:
Original version of this article appeared here: SQLite Rocks , Let Us Tell You Why
Top comments (8)
"But you can stick text data into an integer column without a problem."
OK, I give up - why would that be an advantageous thing?
I'm pretty sure my app would crash horribly if I started getting strings in my ints.
If you want to use SQLite as more of a datastore, perhaps to back a dictionary then the data type flexibility would be very useful.
If you are using it as a simple traditional database, I agree it is far less useful.
Don't forget: Apple's Core Data is also backed by SQLite.
Thanks a lot for sharing this post. I didn't know that SQLite was used in production. I used it for testing locally in dev environments.
I actually encountered an issue with this. I updated a migration once to have a char(8) and accidentally updated a field to a string with 9 characters. Locally testing it with SQLite threw no errors, but on the CI my tests were failing and I couldn't figure out why.
Personal lesson learned: use the same rdbms as on production while testing.
It works great for this use case but it also works well in production.
It's also used pretty often for mobile apps.
And, in case you never read it, you might enjoy How SQLite is tested :)
wow. i think i must use sqlite
I read it got much of its funding by aerospace companies. That's why SQLite has a humongous amount of tests.