Getting Started With SQLite: A Practical Guide

Every programming language bundles a handful of things you are supposed to already know, and SQLite is one of them. It is the most widely deployed database engine on Earth in part because you never install it: it is a C library embedded directly inside the application that uses it. There is no server process, no configuration file, no admin account, and no setup step. The entire database is a single file on disk.

That simplicity is not a limitation, it is the design. SQLite is built for local data storage inside individual applications and devices, and its own documentation is refreshingly blunt about it: SQLite does not compete with client/server databases, it competes with fopen(). When the alternative to a database is writing JSON or CSV to a file by hand and then writing a parser to read it back, SQLite is the upgrade.

What you get

The feature list is more serious than its size suggests:

• Zero configuration. Nothing to install, nothing to administer. The database lives in one ordinary file.
• ACID transactions. Atomic, consistent, isolated, and durable, even after a crash or power failure.
• A real CLI. The sqlite3 command line program is a full SQL interface.
• Standards-adjacent SQL. Common table expressions, window functions, partial indexes, and more.
• Cross-platform. The database file works everywhere the library does.

The applications that lean on it tell you where it fits: phones, desktop apps, browsers, embedded devices, and small to medium web applications. If the operational overhead of a real database server is more than the project deserves, SQLite is the answer.

Create a database and do something with it

The CLI is how you get a feel for it fast. From a shell prompt, the single command sqlite3 with a database filename both opens and creates:

sqlite3 notes.db

That opens a SQLite command line where you can type SQL directly. Create a table, insert a row, and query it back:

CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT, created TEXT);

INSERT INTO notes (title, body, created) VALUES ('First note', 'Hello from SQLite', '2026-09-13');

SELECT id, title FROM notes;

Inside the CLI you can also run the built-in dot-commands. .tables lists your tables, .schema notes shows the SQL that created a table, and .help lists everything else. Type .quit to exit.

This is genuinely all there is to get started. Because sqlite3 notes.db creates the file if it does not exist, your first "production" interaction with a database is a single terminal command with no daemon to start and no connection string to wrestle with.

Backup is just a file copy

This is the part that surprises people coming from client/server databases. Because the database is one file, backing it up is a file copy:

cp notes.db notes-backup.db

That copies the database while it might be in use, and for a single-user tool it is usually fine. For a database that is being written while you copy, the safer approach is SQLite's online backup API or the .backup CLI command, which produces a consistent snapshot:

.backup notes-safe.db

Either way, the operational story is dramatically simpler than running a database server, which is the entire point. There is no pg_dump ritual, no replication topology, and no connection pool to babysit for a single-user workload.

Where SQLite will bite you

Two honest limits matter before you build on it. First, SQLite allows one writer at a time per database file, though it supports unlimited simultaneous readers. Writers queue up; writes are normally short, so this is rarely a problem, but it means SQLite is the wrong tool for many concurrent writers. Second, keep it local to your machine. SQLite works over a network filesystem in theory, but file locking on most network filesystems is unreliable and can corrupt the database. If multiple computers share the same database over a network, you want a client/server engine instead.

There is also a "suggestions, not constraints" quirk in the default type handling: column types are advisory, and a column declared INTEGER will happily store the string 'hello'. Writing an app that depends on strict types is where stricter databases win, but a local tool that is tolerant of its own data is rarely a problem in practice.

The takeaway

SQLite is the lowest-friction way to build real persistence into a tool that runs on one machine. It ships everywhere, needs no setup, gives you honest ACID transactions, and backs up with a file copy. Reach for it by default for local, single-writer data, and only graduate to a database server when you actually have multiple writers or data living across a network.

Key takeaways

• SQLite is a serverless, zero-configuration, single-file database library used by countless apps, phones, and small websites.
• sqlite3 notes.db creates and opens a database; you talk to it with plain SQL plus dot-commands like .tables and .schema.
• A single-user SQLite database backs up by file copy, or with the .backup command for a consistent snapshot.
• It supports one writer at a time and should stay local; for many concurrent writers or shared network data, use a client/server database.