What is SQLite?

SQLite is a relational database engine that works with the SQL language, but with one huge difference compared with systems like MySQL or PostgreSQL: it is not a server. SQLite is a library written in the C language that is embedded directly inside your application and stores the entire database in a single file. There is no service to install, no port to configure and no administrator to watch over: the program that uses SQLite opens that file and works with it directly, just as it would read a document or a configuration file. That simplicity explains its popularity. SQLite was first released in the year 2000 and has since become arguably the most widely deployed database engine on the planet: billions of copies are estimated to be in use, many of them invisible to the user. Whenever a phone stores a message, a browser remembers your history or a desktop program keeps your data, chances are that a SQLite file is quietly doing the work behind the scenes.

An embedded library, not a database server

To understand what SQLite is, it helps to compare it with the classic client-server architecture. With MySQL or PostgreSQL there is a server process that listens for requests, and applications connect to it over the network or through a local socket. That server has to be installed, configured, secured and kept up to date. SQLite removes that whole layer: the engine is a library linked into your program that executes SQL directly against the database file. The application is both client and server at the same time. That is why SQLite is described as self-contained, serverless and zero-configuration. It requires no administrator, consumes no memory or CPU in the background when idle, and behaves identically on Windows, Linux, macOS, Android or iOS. What is more, the database file is portable across platforms: you can copy it, email it or back it up with a simple file-copy command.

Main features of SQLite

Despite its small footprint, SQLite is a full-featured engine. Its most notable characteristics are:

ACID transactions: operations are atomic, consistent, isolated and durable. If a transaction is interrupted halfway, the database is never left in a partial state.
Standard SQL: it implements most of the SQL language, including complex queries, subqueries, joins (JOIN), indexes, views, triggers, window functions and common table expressions (CTEs).
A single file: all the information, including tables, indexes, views and records, lives in one .sqlite or .db file.
No configuration: there is no server to start, no database users to create and no configuration files to edit.
Dynamic types: every column accepts the INTEGER, REAL, TEXT, BLOB and NULL storage classes, with per-column type affinity, which offers a great deal of flexibility.
Proven reliability: the project ships a test suite with millions of cases and supports databases of up to 281 terabytes.
Public domain: SQLite's code is released into the public domain: you can use, copy, modify and distribute it without paying for licenses.
Cross-platform: it compiles and runs on virtually any system, from phones to servers.

When should you use SQLite?

SQLite shines when the database lives on the same machine as the application and the number of concurrent writes is low or moderate. It is an excellent choice in these scenarios:

Mobile applications: Android and iOS ship with SQLite built in; it is the recommended native store for offline applications.
Small and medium websites: if a site receives a few thousand visits a day, SQLite can handle it without effort and without managing a database server. In fact, it is the default engine of many small projects written in PHP and Python.
Desktop applications: invoicing, inventory, accounting and local management programs keep their data in a SQLite file with nothing else to install.
Prototypes and development: starting with SQLite is instantaneous, and you can later migrate to a server engine if the product grows.
Embedded devices and the Internet of Things: routers, TVs, game consoles, drones and industrial equipment use SQLite because of its minimal size and consumption.
Data analysis and conversion: since it is a single portable file, SQLite is widely used to query and transform data (CSV, JSON) without standing up any infrastructure.

When should you NOT use SQLite?

To be honest: SQLite is not the right tool for everything. Choose PostgreSQL, MySQL or SQL Server when:

There are many concurrent writes: SQLite allows several simultaneous readers, but only one writer at a time. With a high volume of parallel inserts and updates, the write lock becomes a bottleneck.
Many simultaneous users on the network: client-server engines are designed to serve hundreds or thousands of connections from different machines, with per-user access control.
The database grows too large or demands high availability: database servers offer replication, load balancing and hot backups that SQLite does not provide out of the box.
Client and server live on different machines: SQLite does not communicate over the network; each computer would hold its own copy of the file and the data would drift out of sync.

As a rule of thumb: if the application is local and write traffic is moderate, SQLite is usually enough and keeps everything simple. If you expect a multi-user web application with heavy concurrency, consider a client-server engine from the start.

SQLite vs MySQL: key differences

One of the most frequent questions is how SQLite compares with MySQL. Both are SQL engines, but with opposite philosophies:

Aspect	SQLite	MySQL
Architecture	Embedded library, serverless	Client-server with its own process
Storage	A single portable file	Data directory managed by the server
Configuration	Zero: works instantly	Installation, configuration and administration
Concurrent writes	One writer at a time	Several simultaneous writers
Network access	No: local use	Yes: many remote clients
Users and permissions	Does not manage users	Full user and privilege management
Best for	Mobile, desktop, small and medium sites	Web with many users and high concurrency
License	Public domain	GPL and commercial license
Backups	Copy the file	Server tools (mysqldump, replicas)

In short: this is not a contest between better and worse, but between environments. For a database embedded inside an application, SQLite is hard to beat; for a portal with thousands of users connected at once, a database server is more appropriate.

How to open a .sqlite or .db file

Opening a SQLite database is easy because, underneath, it is just a regular file. These are the most common ways:

From the terminal (sqlite3): the official client ships in the command-line tools package on the SQLite website. You use it like this: sqlite3 inventory.db. Once inside, the command .tables lists the tables, .schema shows their structure, and .headers on together with .mode column make the results easier to read.
With Python: the sqlite3 module is bundled with Python, so you only need to write import sqlite3 and connect to the database to run queries.
With a GUI tool: free cross-platform programs such as DB Browser for SQLite let you explore tables, run queries and export data without writing a single line of code.
With other languages: PHP (PDO SQLite), Java, C#, Go, Node.js and Rust all have official or very stable libraries for SQLite.

A typical query from the terminal looks like this: SELECT * FROM products; and it returns every row in the table. If the file does not exist and a program tries to open it for writing, SQLite creates an empty one: that is why it is also so handy for building databases from scratch in tests.

Real-world uses of SQLite

SQLite is much closer than you might think. Here are some real examples:

Web browsers: Chrome, Firefox and Safari store history, bookmarks, cookies and extension data in SQLite files.
Android phones: the operating system ships with SQLite as its native data store: contacts, messages and almost every application use it.
iOS: on the iPhone and iPad, SQLite is likewise the default embedded database for applications.
Local inventory and invoicing systems: management software for small businesses often keeps products, stock and sales in SQLite, which saves the customer from installing a database server.
Embedded devices: routers, televisions, game consoles and medical devices include it to store configuration and records.

If you run a warehouse or a shop and are looking for an inventory system that is simple, fast and independent of an external server, keep in mind that tools such as Kardex Tauro take advantage of exactly this technology to store your products and movements in a local file, with all of SQLite's reliability and none of the installation hassle.

Labels:

#sqlite #database #sql