SQLite: A Database in One File

Our bookshop API needs somewhere to keep its books. That place is a database.

Why this tutorial uses SQLite

Most databases run as a separate program called a database server. SQL Server, MySQL and Postgres all work this way. To use one, you install the server, set a password, pick a port, and make sure the server is running. On some machines, one of those steps goes wrong, and then you cannot follow the tutorial.

This tutorial is about building an API, not about running a database server. So we use SQLite. It has nothing to install and nothing to start. The whole database is one file, and our app reads and writes that file directly.

SQLite is still a real SQL database. The tables, rows and queries you learn here work the same way in the bigger databases. Near the end of the tutorial, we switch to a database server in one short lesson, so you can see how little changes.

What SQLite is

SQLite is a database that lives in a single file, such as bookshop.db.

No server. Nothing runs in the background. Your app opens the file, reads or writes it, and closes it.
Easy to move. To back it up, copy the file. To share it, send the file. To start again from nothing, delete the file.
Real SQL. You still use tables, rows and the SQL language to talk to it.

Where SQLite fits, and where it does not

SQLite is a good choice for:

Unit tests and integration tests. There is no server to start. A test can build a fresh database in memory (kept in the computer's memory, so it disappears when the test ends), use it, and throw it away. We do this later in the tutorial.
Prototypes and tutorials, like this one. You get a working database in a minute.
Small, low-traffic sites and apps. Many phone and desktop apps keep their data in SQLite. It is also fine in production for a small website. SQLite's own documentation says that most websites with fewer than about 100,000 visits a day work well with it, and calls that figure a cautious estimate.

SQLite is not the right choice when:

Many people save changes at the same moment. SQLite lets only one writer in at a time. Reading is not a problem, because many readers can read at once. Saving is the problem: the others wait in line.
The load is heavy. A busy shop with thousands of orders a minute needs a database server built for that.

For a small bookshop, this limit hardly matters. When the shop grows, you move to a database server. The idea is simple: start small, and move to a server when the app outgrows one file.

Look inside a SQLite file

💡 Already know databases? Skim the sections below. Working with SQLite is very similar to working with any other database.

A database file is hard to picture until you have opened one. So let's do that. We use a free tool called a database client. It is a small program that opens a database file and lets you look at it and type SQL.

The client we use is DB Browser for SQLite. It is free and works on Windows, Mac and Linux.

Download it from sqlitebrowser.org. On Windows, take the installer, or the portable version if you do not want to install anything.
Open it and choose New Database. Save the file as bookshop.db, anywhere you like.
A window asks you to create a table. Close it for now. We will create the table with SQL instead.

Create a table

A table is a grid. Each column has a name and a type of data. Each row is one item. Our table holds books.

Open the Execute SQL tab, paste this, and press the play button:

CREATE TABLE Books (
    Id     INTEGER PRIMARY KEY AUTOINCREMENT,
    Title  TEXT    NOT NULL,
    Author TEXT    NOT NULL,
    Price  REAL    NOT NULL,
    Stock  INTEGER NOT NULL
);

What it says:

CREATE TABLE Books makes a new table called Books.
Each line inside the brackets is one column: its name, then its type. TEXT is words, INTEGER is a whole number, and REAL is a number with decimals.
NOT NULL means the column can never be left empty.
Id INTEGER PRIMARY KEY AUTOINCREMENT is the book's number. A primary key is a value that is different for every row, so you can always point at exactly one book. AUTOINCREMENT means the database picks the next number for you.

Insert rows

Insert means add a row. Run this:

INSERT INTO Books (Title, Author, Price, Stock)
VALUES ('The Little Garden', 'Ana Reyes', 12.50, 8);

INSERT INTO Books (Title, Author, Price, Stock)
VALUES ('Sea and Salt', 'Mark Lee', 9.99, 3);

We did not give an Id. The database chose 1 and 2 for us.

Select rows

Select means read. This asks for every column of every row:

SELECT * FROM Books;

The * means "all columns". The result is:

Id  Title              Author     Price  Stock
--  -----------------  ---------  -----  -----
1   The Little Garden  Ana Reyes  12.5   8
2   Sea and Salt       Mark Lee   9.99   3

You can ask for less. This asks for the title and price of books under 10:

SELECT Title, Price FROM Books WHERE Price < 10;

Title         Price
------------  -----
Sea and Salt  9.99

In DB Browser, press Write Changes (or Ctrl+S) to save. Then look at the folder where you saved bookshop.db. The file is there, and it now holds your two books.

You will use this client again later. When our API creates its own database, you will open the file here and see the table that the code built.

How the app finds the database

Your app needs to know which database to open. It is told with a connection string: a short line of text that says where the database is.

For SQLite, the connection string is just the file name:

Data Source=bookshop.db

For a database server, the connection string holds the server's address, a user name and a password. That is the only thing that changes when you move to a bigger database. We keep the connection string in the app's settings, not in the code, so we can change it without rebuilding. A later lesson shows how.

Recap

A database is where an app keeps its data safely.
Most databases run as a database server, which takes setup. We use SQLite so you have nothing to install.
SQLite is a real SQL database that lives in one file.
It is great for tests, prototypes and small, low-traffic sites. It allows one writer at a time, so busy apps move to a database server.
A table has columns and rows. CREATE TABLE makes one, INSERT adds a row, and SELECT reads rows.
A connection string tells the app where the database is. For SQLite it is the file name.

Questions to think about

The answers are not given here on purpose, so check yourself against the lesson.

Why does this tutorial use SQLite and not SQL Server, MySQL or Postgres?
What does it mean that SQLite has "no server"? How do you back up a SQLite database?
Your friend has a small website with a few hundred visitors a day. Would you tell them SQLite is too weak for production? Why or why not?
A shop's website has thousands of customers placing orders at the same moment. Why would SQLite struggle, and what would you use instead?
Write the SQL to add a book called "Night Train" by "Iris Cole", priced 15.00, with 5 in stock. Then write the SQL that shows only the books with fewer than 5 in stock.