Most people think SQLite is just a toy database and does not have much use in the real world. But it can be very powerful when used correctly. In this article, I will try to explain what SQLite is, how it is different from other traditional databases, why you should use it, and a lot more.
What is SQLite?
SQLite is an embedded, open-source, lightweight SQL database engine. It is not a standalone process like other databases and is tightly integrated with the application software. By default, it stores data as a single file on disk. It can also be configured to store data in-memory if you want to trade durability for better performance. Also, the file format is cross-platform. A database (file) that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.
How is it different from other databases?
According to the SQLite's documentation:
SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
So, most traditional databases have separate client and server processes running on the same/different machines. They aim to provide high scalability and concurrency. However, in SQLite, everything runs in the same process as the application and it focuses on simplicity and efficiency.
Where can we use SQLite?
SQLite is great for applications that want to store data locally like browsers, mobile apps, etc. For example, browsers store information about your settings, history, etc. in an SQLite database.
It is ideal for embedded/IoT (Internet of Things) devices like cameras, drones, automobiles, etc.
It works great as the database for most low to medium-traffic (100K+ hits/day) websites. Additionally, it offers a more cost-effective and straightforward setup compared to client/server databases, enhancing its appeal as a favorable option.
The simplicity of SQLite makes it a good option for prototype applications.
Recently, companies have started to launch Distributed SQLite services like Cloudflare's D1, and Fly.io LiteFS. These services bring scale to the simplicity and efficiency of SQLite.
When not to use SQLite?
SQLite may not be the best option if your app has high concurrent write requirements. It supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.
SQLite is great when all the traffic can be served by a single backend process on a single machine. However, when scalability or high availability becomes essential, SQLite falls short, as it does not work well with multiple backends.
SQLite is not good for storing a large amount of data. It stores the entire database in a single disk file and many filesystems limit the maximum size of files.
Wrapping up
I hope this article was helpful and you learned something new. SQLite is an excellent choice if you want a simple and fast database. There is a lot more to know about SQLite and you can read more about it from its official documentation.
Thanks for reading!