Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 33 ยท Meet SQLite

What Is SQLite?

Learn what SQLite is, how a serverless, single-file database works, where it runs, and when to pick it over a client-server database like PostgreSQL or MySQL.

SQLite is a relational database that lives inside your program. There is no database server to install, no port to open and no password to configure. Your application links the SQLite library, and the whole database is stored in one ordinary file on disk. You still talk to it with SQL: CREATE TABLE, INSERT, SELECT and everything else you know from the SQL tutorial.

That design sounds modest, but it makes SQLite the most widely deployed database engine in the world. It runs in every phone, every web browser, most desktop applications, many cars, TVs and airplanes. If you have used a smartphone today, you have used SQLite several times without noticing.

This tutorial teaches SQLite itself: its quirks, its strengths and the habits that keep a SQLite database fast and correct.

Serverless does not mean "no database engine"

Most databases follow the client-server model. A separate process (the server) owns the data files. Your application connects to it over a network socket, sends SQL text, and receives rows back.

SQLite is embedded. The database engine is a library, compiled into your application. When your code runs a query, the library reads and writes the database file directly, in the same process. Nothing travels over a network.

Two ways to reach a database

Client-server Your app talks to a separate database process over a socket.
Embedded The database engine is a library inside your app.

PostgreSQL and MySQL are client-server. SQLite is embedded.

1 / 2

Here is the difference as a picture of one request.

flowchart LR
  subgraph CS["Client-server"]
    A1["App"] -->|"SQL over network"| S1["Database server"]
    S1 --> F1[("Data files")]
  end
  subgraph EM["SQLite"]
    A2["App + SQLite library"] --> F2[("app.db")]
  end

With SQLite there is no middle process: the library inside your app opens the file itself.

One file holds everything

A SQLite database is a single cross-platform file. Tables, indexes, views, triggers and all the data live inside it. You can email it, put it in version control (for small ones), copy it to another operating system or another CPU architecture, and it opens just the same. The file format is stable and documented, and the SQLite project has promised to keep it readable for decades. That is why the US Library of Congress lists it as a recommended format for long-term data storage.

While your program runs, SQLite may create small helper files next to the database (a journal or a write-ahead log, covered in Concurrency, Locking and WAL). They are part of the database while it is open, so never copy only the main file while a program is writing to it.

You can also ask for a database that exists only in memory. It disappears when the connection closes. This is what the live editors on this site use: every time you press Run, you get a brand-new empty database.

Where SQLite is used

  • Phones and tablets. Android and iOS both ship SQLite, and apps use it for contacts, messages, settings and offline data.
  • Browsers. Chrome, Firefox and Safari store history, bookmarks and cookies in SQLite files. You can also run it inside a web page with WebAssembly, which is how this site's editor works.
  • Desktop software. Photo libraries, chat clients, accounting tools and IDEs keep their data in SQLite.
  • File formats. Some applications use a SQLite file as their "document format", so a saved project is just a database.
  • Small and medium websites. A site with one application server and moderate write traffic can run on SQLite with excellent speed.
  • Tools and testing. Data analysis scripts, command-line tools, and automated tests use it because there is nothing to set up.

SQLite versus a client-server database

Question SQLite PostgreSQL / MySQL
Where does the engine run? Inside your application In a separate server process
Setup None Install, configure, create users
Storage One file A directory of files managed by the server
Many writers at once One writer at a time Many concurrent writers
Network access Not built in Built in, with users and permissions
Data types Flexible (dynamic typing) Strict by default
Typical size Megabytes to tens of gigabytes (the limit is about 281 TB) Gigabytes to terabytes and more

The "one writer at a time" row deserves a closer look. SQLite allows many readers in parallel, but writes take turns. Because each write is fast (often under a millisecond), this is fine for a surprising number of applications. It becomes a problem when many different processes or machines all want to write constantly.

When SQLite is a great fit

  • Your app is the only (or main) user of the data, on a single machine.
  • You want zero administration: local apps, command-line tools, embedded devices.
  • You need a reliable file format, or an easy way to ship data along with a program.
  • A website with mostly reads and a modest number of writes per second.
  • Prototyping, teaching, tests and analysis of datasets that fit on one disk.

When to pick something else

  • Many machines must write to the same database. SQLite has no network protocol. Using it over a shared network drive is unsafe because file locking on network file systems is unreliable.
  • Very high write concurrency. Hundreds of simultaneous writers are better served by PostgreSQL or MySQL.
  • You need fine-grained user permissions. SQLite has no users or roles. Whoever can open the file can do anything to it.
  • You need built-in replication or clustering. Tools exist that add this, but they are extras, not part of SQLite.

None of these are flaws. They are the price of being small, fast and simple, and you should choose based on your application, not on habit.

Meeting SQLite in a query

You do not need a table to talk to SQLite. The function sqlite_version() returns the version of the engine behind the editor, and any expression can be selected without a FROM clause.

SQL Asking the engine about itself

Press Run (or Ctrl + Enter) to see the result here.

The version number will be 3.something. The typeof() calls give a first taste of what comes in Dynamic Typing and Type Affinity: in SQLite, the type belongs to the value you store, not only to the column.

SQL No FROM needed

Press Run (or Ctrl + Enter) to see the result here.

You should see 14, SQLITE, 8 and 2024-03-01. Because 2024 is a leap year, two days after 28 February is 1 March. SQLite's date functions get their own chapter later in Dates, Times and Booleans.

A few myths

  • "SQLite is a toy." It is tested more heavily than almost any other software, with millions of test cases and branch coverage of its core. It also handles real production traffic at many companies.
  • "SQLite is only for small data." Databases of many gigabytes work well, provided queries use indexes.
  • "SQLite is not safe." Writes are transactional (ACID). A power failure in the middle of a write leaves either the old data or the new data, never a mix. This works because SQLite is careful to flush data to disk, which also means that file copies of a live database can go wrong.
  • "No server means no SQL." It is real SQL, with joins, subqueries, window functions, triggers, views and more.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does it mean that SQLite is "serverless"?

    Serverless here means embedded. The SQLite library is linked into your program and reads and writes the database file directly.
  2. 2. How is a SQLite database normally stored?

    One cross-platform file holds the tables, indexes and data. An in-memory database also exists, and vanishes when the connection closes.
  3. 3. Which workload is the WORST fit for SQLite?

    SQLite has no network protocol and allows one writer at a time. A client-server database suits many machines writing together.
  4. 4. What does SELECT 1 + 1; need in order to run in SQLite?

    SQLite evaluates expressions without FROM and returns a single row.

Summary

  • SQLite is an embedded SQL database: a library inside your program, with the whole database in one file (or in memory).
  • It needs no installation, server or accounts, and database files are portable across operating systems.
  • It runs in phones, browsers, desktop apps, small websites, tools and tests.
  • Many readers can work in parallel, but only one writer at a time. It has no built-in network access or user permissions.
  • Choose a client-server database when many machines need to write concurrently or you need roles and replication.

Next: Running SQLite: Browser and the sqlite3 Shell shows the two ways you will run queries in this tutorial.