What is SQLite?

If you have ever built an app that stores data, you have probably heard names like PostgreSQL, MySQL, or MongoDB. These are all database systems, and they all have one thing in common. They run as a separate program on your machine, or on someone else’s machine. Your app connects to that program over the network, sends it questions, and gets answers back. There is a whole server involved.

SQLite is different. It is not a server at all. It is a library that gets embedded directly into your application. When your code talks to SQLite, it reads and writes a single file on disk. No network. No separate process running in the background. No configuration files to manage. Just your app and a file.

That simple idea makes SQLite the default choice for mobile apps, desktop apps, CLI tools, embedded systems, and local-first software. It ships with every iPhone, every Android phone, every Mac, most Linux distributions, and most programming language standard libraries. There is a very good chance it is already on your computer right now.

What is a database?

If you are completely new to databases, let’s start at the very beginning.

A database is a structured way to store and retrieve data. Think of it like a spreadsheet, but designed for programs instead of humans. You define tables (like individual sheets), each with columns (like headers). Then you add rows of data, and each row is one record. A row in a users table might represent one user. A row in a books table might represent one book.

To talk to a database, you use a language called SQL. SQL stands for Structured Query Language. It is how you say things like “give me all users who signed up this month,” or “add a new book,” or “delete this review.” SQLite speaks SQL, with a few quirks that we will cover along the way.

What is a schema?

You will see the word “schema” throughout this course, so let’s define it now.

A schema is the structure of your database. It describes which tables exist, what columns each table has, what types those columns use, and what rules (called constraints) are in place. The schema describes the shape of your data, not the data itself.

When someone says “change the schema,” they mean changing the structure. Adding a table, adding a column, removing a column, renaming something. The actual rows of data inside the tables are not the schema. The schema is the blueprint.

When to use SQLite

SQLite handles more than most people expect. It comfortably serves websites with moderate traffic, powers local data storage, and works well as an application file format. Think of it this way. A .sqlite file is like a save file that your program can also query.

Where it does not fit is in situations where multiple processes need to write to the same database at the same time with very high throughput, or where you need your data replicated across multiple machines. For those scenarios, you want a client-server database like PostgreSQL.

For a surprisingly large number of real-world applications, though, SQLite is more than enough. And because there is no server to set up, getting started takes seconds.

What we will build in this course

Rather than jump between unrelated toy examples, this course keeps one running application in mind: a small book review platform. Users sign up, read books written by authors, tag books with genres, and leave ratings and reviews. Every new concept we introduce (tables, relationships, indexes, full-text search, and so on) earns its place by answering a question this platform actually needs to answer.

By the end you will have built the whole schema through numbered migration files, and you will know how to extend it when the product grows. The capstone at the very end then applies the same toolkit to a different domain (a course platform), just to show the skills transfer.

Check your version

Open a terminal and run:

sqlite3 --version

If that prints a version number (something like 3.x.x), you already have it installed. If not, install it from your package manager:

# macOS
brew install sqlite

# Ubuntu / Debian
sudo apt install sqlite3

# Windows (via scoop)
scoop install sqlite

[!NOTE] This course uses some features introduced in recent SQLite versions. ON CONFLICT requires 3.24+, RENAME COLUMN requires 3.25+, and DROP COLUMN and RETURNING require 3.35+. If your version is 3.35 or higher, everything in this course will work. Most systems ship with a recent enough version, but if you run into a “syntax error” on a feature, check your version first.

Create your first database

sqlite3 myapp.db

That is it. If myapp.db does not exist, SQLite creates it. You are now inside the SQLite interactive shell, connected to that file. You will see a prompt that looks like this:

SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite>

Everything you type at this prompt is either a SQL statement or a dot-command. We will cover the difference in the next lesson. For now, type .quit to exit.

Notice there is no CREATE DATABASE command anywhere. Opening a file creates it. If you delete the file, the database is gone. That simplicity is one of the things that makes SQLite so pleasant to work with.

Exercises

Exercise 1: Run sqlite3 --version in your terminal. Write down the version number. Is it 3.35 or higher?

Exercise 2: Create a new database by running sqlite3 test.db. Type .quit to exit. Then check your file system. You should see a file called test.db. Delete it when you are done.

Exercise 3: Think of an application you use daily (a notes app, a todo list, a messaging app). What tables do you think it might have? What columns would each table need? You do not need to write any SQL yet. Just sketch it out on paper.

You now have SQLite installed and a database file created. That is a good starting point, but staring at a blank sqlite> prompt is not very useful on its own. In the next lesson, we will get comfortable with the sqlite3 shell itself so you can navigate it with confidence before writing any real SQL.