How to Use SQLite: The Beginner's Guide (First Database, Tables, CRUD, Joins, and When Not to Use It)

CodexFuse Team · 2026-10-31T03:57:27.257Z · 15 min read

How do I use SQLite?

You use SQLite by installing the sqlite3 shell, opening a database file, creating tables with CREATE TABLE, and running SQL against them — install, open, create, query, back up. Here is how to use SQLite from absolute zero, in one beginner-friendly arc: check your install, build a real two-table books database, run the full CRUD cycle, join the tables, add an index, and back everything up safely. Every SQL statement in this SQLite tutorial was executed in the sqlite3 shell as written, so you can type along line by line. If your first data lives in a spreadsheet instead of your head, our CSV to SQL converter turns it into INSERT statements you can paste straight into the shell.

1. Check the install. Run sqlite3 --version in your terminal. macOS and most Linux systems already ship the shell; Windows users download the precompiled tools from the official SQLite download page.
2. Open a database file. sqlite3 books.db creates books.db in the current folder (or opens it if it already exists) and drops you at the sqlite> prompt.
3. Create tables. Write CREATE TABLE statements for authors and books, then confirm them with the .tables dot command.
4. Run the CRUD cycle. INSERT rows, SELECT them back with WHERE, UPDATE a price, and DELETE a row — the sqlite3 commands below ran exactly as written in testing.
5. Back it up. .backup 'books-copy.db' writes a safe copy while the database is still open. That is the whole beginner workflow: five steps, one file, zero servers.

What is SQLite, and why is it different from MySQL and Postgres?

SQLite is a serverless, self-contained SQL database engine that stores an entire database — tables, indexes, and all — in a single ordinary file on disk. There is no server process to install, no username or password to configure, and no network port to open: your application reads and writes the file directly through the SQLite library.

That is the opposite of how MySQL and Postgres work. Those are client-server databases: a separate server process runs in the background, and your app connects to it over a network socket, even when both live on the same machine. With SQLite, the database engine is embedded inside your application, which is why it shows up in mobile apps, browsers, and small web projects where running a database server would be overkill.

Beginners sometimes hear "serverless" and assume SQLite is a toy. It is not — its transactions are fully ACID-compliant, meaning your writes are atomic, consistent, isolated, and durable, the same guarantees the big databases make. The current release line is SQLite 3.54.x (3.54.1 shipped October 16, 2026), and the engine is under continuous development by its original authors.

How do I install SQLite?

You install SQLite by getting the sqlite3 command-line shell onto your machine, which takes about a minute on every major platform. Start by checking whether it is already there:

sqlite3 --version

On macOS, the shell is preinstalled — the command above just works in Terminal. On Debian or Ubuntu Linux, install it from the package manager with sudo apt install sqlite3. On Windows, there is no installer: visit the official SQLite download page, grab the precompiled "sqlite-tools" zip for Windows, extract it to a folder, and add that folder to your PATH.

If you prefer a throwaway sandbox over touching your system, you can also run the shell inside a container — our Docker beginner guide covers the container basics first, then come back here. And if the command line is not your style at all, a free visual client like DBeaver opens the same .db files with point-and-click tables — our DBeaver beginner guide walks through the first connection.

How do I create my first SQLite database and table?

You create your first SQLite database by running sqlite3 with a filename — SQLite creates the file the moment it needs to write to it. This command opens (or creates) a database called books.db and gives you the interactive prompt:

sqlite3 books.db

Nothing is on disk yet except an empty file; the database only takes shape when you create a table. Every SQL statement ends with a semicolon, and the prompt keeps accepting lines until it sees one. Create an authors table first:

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT
);

Two things to notice. INTEGER PRIMARY KEY is special in SQLite: it makes the column an alias for the internal rowid, so values auto-increment as you insert — you never have to supply the id yourself. NOT NULL means the name column refuses empty values, a small guardrail you will thank yourself for later.

Now a books table that points back at authors:

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  year INTEGER,
  price REAL
);

The REFERENCES authors(id) clause declares a foreign key: each book's author_id is supposed to match an author's id. Note the honest caveat the official docs stress — SQLite parses foreign keys but does not enforce them unless you run PRAGMA foreign_keys = ON; for each connection, because they are disabled by default for backwards compatibility. More on that in the FAQ.

Two dot commands — shell-only helpers that start with a period, not SQL — let you inspect what you built. .tables lists your tables, and .schema prints the CREATE TABLE statements back:

sqlite> .tables
authors  books
sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  year INTEGER,
  price REAL
);

How do I add, read, update, and delete data in SQLite?

You change data in SQLite with the four CRUD statements — INSERT, SELECT, UPDATE, DELETE — and the sqlite create table work from the last section is what they operate on. Add three authors, one INSERT per row:

INSERT INTO authors (name, country) VALUES ('Octavia Butler', 'United States');
INSERT INTO authors (name, country) VALUES ('N. K. Jemisin', 'United States');
INSERT INTO authors (name, country) VALUES ('Kazuo Ishiguro', 'United Kingdom');

You can also insert several rows in one statement by stacking the value lists — handy when seeding a practice database:

INSERT INTO books (title, author_id, year, price) VALUES
  ('Kindred', 1, 1979, 12.99),
  ('The Fifth Season', 2, 2015, 14.99),
  ('Never Let Me Go', 3, 2005, 11.99);

Reading data starts with SELECT. SELECT * FROM books; dumps every column, but real queries name their columns and filter with WHERE:

SELECT title, year FROM books WHERE year > 2000 ORDER BY year DESC LIMIT 2;

That returns the two newest books after 2000, newest first. Two readability settings make shell output far more pleasant — turn them on at the start of a session:

.headers on
.mode column

Now the two statements beginners must treat with respect: UPDATE and DELETE. Both take a WHERE clause, and without one they rewrite or wipe the entire table:

UPDATE books SET price = 13.99 WHERE title = 'Kindred';
DELETE FROM books WHERE title = 'Never Let Me Go';

The rule is simple and absolute: write the WHERE first, read it twice, then run the statement. There is no undo button in the shell — which is exactly why the backup section below exists.

How do SQLite joins work?

SQLite joins work like joins in any SQL database: they combine rows from two tables wherever a condition matches, usually a foreign key meeting its primary key. The books table stores author_id numbers, not names — a join is how you get the names back:

SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE books.year > 2000;

This returns "The Fifth Season — N. K. Jemisin" and "Never Let Me Go — Kazuo Ishiguro": for each book, SQLite found the author row whose id matches the book's author_id and glued the two rows together. INNER JOIN keeps only books that have a matching author — which is every book here, since author_id is NOT NULL.

The sibling you will meet next is LEFT JOIN, which keeps every row from the left table even when no match exists, filling the missing side with NULLs. A practical beginner pattern: SELECT authors.name, COUNT(books.id) FROM authors LEFT JOIN books ON books.author_id = authors.id GROUP BY authors.name; lists every author alongside how many books they have, including authors with zero books — the LEFT JOIN is what keeps the zero-book authors in the results.

What are indexes, and when should I add one?

An index is a lookup structure SQLite maintains alongside a table so it can find rows without scanning every one of them. You add one with CREATE INDEX, naming the table and the column you filter or join on most:

CREATE INDEX idx_books_author ON books(author_id);

This index exists because the join above matches books.author_id against authors.id — without it, SQLite scans the whole books table for every join. The official foreign-key documentation makes the same point from the other direction: foreign key checks run far better when the child key is indexed, which is why indexing foreign key columns is the first index most beginners should create.

Indexes are not free. Every INSERT, UPDATE, and DELETE now also updates the index, so a table with ten indexes writes slower than the same table with one. The beginner rule of thumb: index columns that appear in WHERE clauses and JOIN conditions, skip the rest, and never index a tiny practice table to "optimize" it — on a few hundred rows, the scan is already instant and the index only adds write cost.

Why does SQLite let me store text in an INTEGER column?

SQLite lets you store text in an INTEGER column because it uses dynamic typing with type affinity: the declared type of a column is a recommendation, not a requirement. This is deliberate — the official documentation calls flexible typing a feature, not a bug — and it is the single biggest surprise for developers arriving from MySQL or Postgres.

Here is the footgun, verified in the shell. Create a table with an INTEGER column and insert the string 'hello' into it:

CREATE TABLE demo(id INTEGER PRIMARY KEY, age INTEGER);
INSERT INTO demo(age) VALUES ('hello');
INSERT INTO demo(age) VALUES (42);
SELECT age, typeof(age) FROM demo;

SQLite accepts both rows without complaint. The typeof() function reveals the truth: 'hello' is stored with storage class TEXT, 42 with INTEGER, in the same INTEGER-affinity column. Every value in SQLite carries one of five storage classes — NULL, INTEGER, REAL, TEXT, or BLOB — and the value's own class wins over the column's declared type.

The practical consequences are real: sorting and comparisons follow the value's storage class, so mixed-type columns produce ordering that surprises people who assumed the declared type was enforced. The honest beginner practice is to declare proper types anyway and treat the flexibility as a safety net, not a license. And if you want the rigid behavior of other databases, SQLite has offered STRICT tables since version 3.37.0, which reject values that do not match the declared type.

How do I back up a SQLite database safely?

You back up a SQLite database safely with the .backup dot command, which copies the database while it is open without risking a half-written file. Run it from inside the shell:

sqlite> .backup 'books-copy.db'

This writes a complete, consistent copy to books-copy.db even if other connections are reading the database. It is the beginner-safe path: one command, no downtime, no extra tools. Verify the copy opens with sqlite3 books-copy.db "SELECT COUNT(*) FROM books;" before you trust it.

The unsafe path is copying the .db file with your operating system's copy command while the database is open and being written to — you can capture a torn page and end up with a corrupt backup. A plain file copy is only safe when no connection has the database open. One more wrinkle: if you enabled WAL mode (PRAGMA journal_mode=WAL;), SQLite keeps two sidecar files, books.db-wal and books.db-shm. A file-level backup must copy all three files together, or checkpoint first — the .backup command sidesteps this entirely, which is another reason to prefer it.

When should I NOT use SQLite?

You should not use SQLite when many writers need to write at the same time, when the database must live on a network filesystem, or when you need the operational tooling of a database server. These are the three honest boundaries, and each one comes straight from how the engine works.

First, write concurrency. Even in WAL mode, SQLite allows only one writer at a time — readers never block writers and writers never block readers, but two writers serialize. A personal project, a small team tool, or a read-heavy site will never notice; a busy multi-user app with constant concurrent writes will. Second, the network rule: WAL mode requires all processes to share memory on the same host computer, so a SQLite file on a network share is not a supported setup. Third, scale of operations: no replication, no user management, no server-side scheduling — when you need those, you have outgrown the single file.

That is the graduation path, and it is a healthy one: prototype on SQLite, and when write concurrency or multi-server deployment arrives, move to Postgres. Our Supabase beginner guide covers the managed Postgres route for exactly that moment — it is the "what comes after SQLite" branch of this tutorial.

SQLite vs MySQL vs Postgres: which should a beginner pick?

A beginner should pick SQLite to learn SQL itself, MySQL for classic shared web hosting, and Postgres when the project needs serious concurrency or advanced features. The table below compresses the decision:

The key insight for learners: SQL skills transfer. The CREATE TABLE, CRUD, and JOIN statements in this guide run nearly unchanged on MySQL and Postgres, so time spent learning on SQLite is never wasted — you are learning SQL, and SQLite is simply the cheapest classroom.

Sources

Facts in this guide were verified on October 30, 2026 against the following sources. Every SQL statement shown was executed in the sqlite3 shell (version 3.45.1) and ran as written:

• SQLite official download page — precompiled sqlite-tools binaries for Windows, version-number filename encoding — https://www.sqlite.org/download.html
• SQLite release history — version 3.54.1 released 2026-10-16; 3.54.0 CLI enhancements — https://www.sqlite.org/changes.html
• SQLite datatypes documentation — dynamic typing, the five storage classes, type affinity as a recommendation, STRICT tables since version 3.37.0 — https://www.sqlite.org/datatype3.html
• SQLite foreign key support — constraints disabled by default for backwards compatibility, enabled per connection with PRAGMA foreign_keys = ON — https://www.sqlite.org/foreignkeys.html
• SQLite write-ahead logging — one writer at a time, readers do not block writers, WAL requires all processes on the same host — https://www.sqlite.org/wal.html
• W3Resource, "How to use SQLite: Installation, Commands, and Examples" (updated December 11, 2024) — install routes, CREATE/INSERT/SELECT/UPDATE reference shape — https://w3resource.com/sqlite/snippets/how-to-use-sqlite.php
• Ignacio Maranillo, "First Steps" (Essential Guide to SQLite) — sqlite3 mydatabase.db workflow, .tables and other dot commands — https://imarranz.github.io/essential-guide-to-sqlite/02_installing/022_first_steps.html
• Stephen C., "A Beginner's Guide to SQLite" (dev.to) — serverless and embedded definition, ACID transactions, dynamic typing — https://dev.to/stephenc222/a-beginners-guide-to-sqlite-the-lightweight-database-solution-c37
• Buddy Lindsey, "Getting Started with SQLite" — database creation, .schema, .exit — https://buddylindsey.com/getting-started-with-sqlite.html

Frequently asked questions

Is SQLite a real database or just a toy for practice?

SQLite is a real, ACID-compliant relational database — atomic, consistent, isolated, durable transactions, the same guarantees the big engines make. It is designed for embedded and single-file use: mobile apps, browsers, and small web projects. "Toy" is the wrong word; "right-sized" is better.

Can I use SQLite in production?

Yes, when the workload fits: read-heavy traffic, one server, and modest write concurrency. Many production sites run SQLite behind a cache exactly this way. Move to Postgres or MySQL when you need many simultaneous writers, replication, or the database on a separate machine.

What is the difference between sqlite3 dot commands and SQL?

Dot commands like .tables, .schema, and .backup are instructions to the sqlite3 shell itself — they start with a period and only work in the shell. SQL statements like SELECT and CREATE TABLE are the database language, and they work identically in every SQLite client, including Python, Node.js, and GUI tools.

Does SQLite enforce foreign keys?

SQLite parses foreign key declarations but does not enforce them unless you enable enforcement with PRAGMA foreign_keys = ON, separately for each database connection — they are disabled by default for backwards compatibility. If your app opens a new connection per request, that pragma must run on every one of them.

Should I learn SQLite or MySQL first?

Learn SQLite first. There is no server to install or configure, so your first hour is spent writing SQL instead of fighting setup — and the CREATE TABLE, CRUD, and JOIN skills transfer directly to MySQL and Postgres later. This sqlite for beginners path is the fastest route to real SQL fluency.