From SQLite to PostgreSQL

What you will be able to do

Move from the SQLite file of the SQL course to a PostgreSQL server on your computer. Load Larkfield’s help-desk data into it, and see what the server checks that the file did not.

Before you startWhat to know and to have readyAbout 45 minutes

What you need to know

• SQL with SELECT, WHERE and GROUP BY, from SQL and Data Preparation for AI, and its SQLite file larkfield.db, from Set up a practice database.
• The ticket API, a virtual environment and pip install -r, from Build and Deploy Your First API, for example Environment and first endpoint.
• A terminal, and how to change folders with cd.

What you need on your computer

• Python 3.12 or later.
• Docker Desktop, or permission to install PostgreSQL. Install and verify in the Docker course shows the Docker install. You do not need the rest of that course.
• About 3 GB of free disk space: the PostgreSQL image is 686 MB, and the large data of the next lesson needs about 1 GB. No account, no key and no money.

You start a new project folder in this lesson, ~/projects/ticket-api, from the snapshot start. If you finished the CI/CD course, keep your old folder: this course starts from a copy with new data and new files.

A notebook or a clerk

Think of two ways to keep the records of a small shop.

The first is a notebook on your desk. You open it, you write in it, and you close it. Nobody checks what you write. If you write “abc” in the price column, the notebook accepts it. And while the notebook is in your bag, nobody else can read it.

The second is a records office with a clerk. You do not touch the book yourself. You go to the counter, say who you are, and ask. The clerk checks every entry: a price must be a number, and a date must exist. Many people can ask at the same time, and the clerk keeps them in order. But the office must be open. If the clerk is not at work, nobody gets an answer.

SQLite is the notebook. PostgreSQL is the clerk.

The problem: a file that accepts anything

In the SQL course, your data was one file, larkfield.db. Your program opened it directly, through the sqlite3 library inside the program. That was the right tool: one person, one file, nothing to install.

You know Larkfield’s team from the SQL and API courses. Mei, the data engineer, leads the storage design, and Amira builds the ticket API. Priya compares AI models from their records, and Grace leads the support agents. Omar is the product manager, Tomás is an AI engineer who likes shortcuts, and Sam asks the questions that you may have too.

Now the help desk wants its ticket API to keep everything: customers, tickets, messages, files, AI runs and vectors. The API, the tests, Priya’s reports and the agents’ screens use the same data at the same time. And the data must stay correct.

See what SQLite does with a wrong value. Open the Python SQLite shell on a new practice file. On Windows, type python instead of python3:

python3 -m sqlite3 try.db

Type these three statements at the sqlite> prompt:

CREATE TABLE tickets (ticket_id TEXT PRIMARY KEY, priority INTEGER, created_at TIMESTAMP);
INSERT INTO tickets VALUES ('T-30002', 'abc', '2026-02-30 09:00');
SELECT ticket_id, priority, typeof(priority), created_at FROM tickets;

('T-30002', 'abc', 'text', '2026-02-30 09:00')

The column says INTEGER, but SQLite stored the text abc. The date is 30 February, a day that does not exist, and SQLite stored that too. There was no error and no warning. The next program that sums the priorities or sorts by date gets a wrong answer, and nobody knows why. Type .quit to leave, and delete try.db.

SQLite is not broken. Its makers designed it to be flexible, as the SQL course’s comparison showed. But a shared system of records needs a clerk who says no.

The idea: a database server

A database server is a program that runs all the time and owns the data. Other programs do not open the data files. They connect to the server, say who they are, and send SQL. The server checks each statement, runs it, and sends back the result.

A program that connects is a client. The ticket API is a client. So are the tests, and psql, PostgreSQL’s own terminal client.

PostgreSQL is a free, open-source database server. Its name comes from an older project, Ingres: “post-Ingres”, plus SQL.

SQLite is a library inside your program that writes one file. PostgreSQL is a separate program: every client connects to it, and only the server touches the data.

Text description of the diagram

With SQLite, the database is a file, and the code that reads it runs inside your own program. With PostgreSQL, the database is a server process. The ticket API, psql and the tests are three separate clients. Each one opens a connection to the server, and the server does all the reading and writing in its data directory.

A client needs five facts to reach the server. They fit in one line, the connection string. This is the one in the project’s .env.example:

postgresql://tickets:change-me-local-only@127.0.0.1:5432/tickets

Part	Value here	What it means
scheme	postgresql://	This is a PostgreSQL server
user	tickets	Who you are. The server checks what this user may do
password	change-me-local-only	Proves that you are that user. You change it
host	127.0.0.1	Which computer runs the server: this one
port	5432	Which door on that computer. 5432 is PostgreSQL’s usual port
database	tickets	One server can keep several databases. This one is tickets

The project reads the string from the variable DATABASE_URL. The password is in it, so the string is a secret. It lives in a file called .env, which Git ignores.

Set up the project

Now you build Larkfield’s setup on your computer. The project, ticket-api, is the ticket API of the earlier courses. The snapshot start adds the help desk’s data, its tables and a loader. The route has one fork: with Docker or without it.

The setup route of this course. Only the step that starts PostgreSQL depends on Docker.

Text description of the diagram

The steps in order: get the project, write .env, and start PostgreSQL. With Docker, that is one command. Without Docker, it is an installer, the pgvector extension, a user and a database. Then make the virtual environment, load the data, and run the tests. The load prints 4 migrations and 200 tickets. The tests print 34 passed.

1. Get the files of the course. If you have Git, clone the labs repository once, in ~/projects:

   cd ~/projects
   git clone https://github.com/Nextia-Intelligence/nextia-learning-labs.git

   Without Git, download the ZIP file of the repository and unzip it in ~/projects. Its folder is then nextia-learning-labs-main. Use that name in the next step.

2. Copy the snapshot start into a new folder ticket-api, and go into it. If a folder ticket-api exists already, rename it first, for example to ticket-api-cicd.

   macOS and Linux

   cp -R nextia-learning-labs/C20/snapshots/start ticket-api
   cd ticket-api
   cp .env.example .env

   Windows

   Copy-Item -Recurse nextia-learning-labs\C20\snapshots\start ticket-api
   cd ticket-api
   Copy-Item .env.example .env

3. Open the folder in VS Code. Open .env, and replace change-me-local-only with a password of your own, in all three places. The first line, POSTGRES_PASSWORD, sets the password when the server starts for the first time. The other two lines use it to connect.

4. Start PostgreSQL. Choose your route.

   With Docker

   Start Docker Desktop. Then start only the database service of the project’s compose.yaml, and check it:

   docker compose up -d db
   docker compose ps

   The first time, Docker downloads the image pgvector/pgvector:0.8.7-pg18-trixie. It is PostgreSQL 18.6 with the pgvector extension, which stores vectors. Run docker compose ps again until the STATUS column says Up … (healthy). The PORTS column says 127.0.0.1:5432->5432/tcp: only programs on your computer can reach the server.

   Without Docker (not tested)

   Install PostgreSQL 18: the installer from postgresql.org on Windows and macOS, or the PGDG packages on Linux. Then install pgvector. On Linux, it is the package postgresql-18-pgvector. On Windows and macOS, follow the pgvector installation notes on GitHub. Open psql as the postgres user, and make the user and the database:

   CREATE ROLE tickets LOGIN SUPERUSER PASSWORD 'your-password';
   CREATE DATABASE tickets OWNER tickets;

   Use the password of your .env. SUPERUSER is only for practice on your own computer: the migrations create the vector extension, and the tests create and delete their own database. The course did not test this route.

5. Make the virtual environment and install the packages:

   macOS and Linux

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1
   python -m pip install -r requirements.txt

   The packages are the CI/CD course’s: FastAPI 0.142.2 and psycopg 3.3.6, the PostgreSQL client library for Python.

6. Load the data:

   python -m scripts.load

   applied  001_create_classifications
   applied  002_add_score
   applied  003_backfill_score
   applied  004_help_desk_tables
   Database is up to date (4 migrations).
   Loaded the small data in 0.1 s:
     customers                 40
     tickets                  200
     messages                 334
     attachments               43
     documents                 36
     ai_runs                  294
     ticket_embeddings        200

7. Run the tests:

   python -m pytest

   The last line is 34 passed. The tests make their own database, tickets_test, and delete everything in it. They never touch tickets.

What the load did

The loader first applied four migrations: numbered SQL files in migrations/, each applied once, in order. The first three come from the earlier courses: they made the classification history. 004_help_desk_tables.sql is new. It makes one table per kind of record, each with a primary key and nothing else yet. Module 2 adds the rules. Then the loader copied the CSV files of data/small/ into the tables and printed the counts.

All the data is made up for this course. No real customer wrote these tickets, and the photos and receipts are drawn by a program. The customers C-0001 to C-0040 have the IDs of the SQL course’s first customers, with new names. The small data has 200 tickets from 25 September to 8 October 2026. The course’s “today” is Friday 9 October 2026.

The loader protects the data, as get_data.py did in the SQL course. Run it again:

python -m scripts.load

The help-desk tables already have data. Run again with --reset to delete it and load again.

python -m scripts.load --reset deletes the help-desk data and loads it again. Use it whenever your practice data is in a bad state.

The times in this module come from one computer, with macOS 26.6 on an Apple M4 with 16 GB. Docker Desktop (version 4.81.0) had 8 GB and 10 CPUs. It ran PostgreSQL 18.6 and pgvector 0.8.7 in a container. Your times will differ; the counts will not. The course tested every step on macOS (Apple silicon) only. The Windows and Linux commands are written, not run.

Look inside with psql

psql is PostgreSQL’s terminal client. With Docker, it is already inside the database container, so you need nothing new:

docker compose exec db psql -U tickets -d tickets

Without Docker, give psql your connection string: psql "postgresql://tickets:your-password@127.0.0.1:5432/tickets". The prompt is tickets=#. A command that starts with a backslash belongs to psql, not to SQL. \dt lists the tables:

                List of tables
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+---------
 public | ai_runs           | table | tickets
 public | attachments       | table | tickets
 public | classifications   | table | tickets
 public | customers         | table | tickets
 public | documents         | table | tickets
 public | messages          | table | tickets
 public | schema_migrations | table | tickets
 public | ticket_embeddings | table | tickets
 public | tickets           | table | tickets
(9 rows)

schema_migrations records which migrations ran. \d tickets shows one table, with a type for each column:

                         Table "public.tickets"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 ticket_id   | text                     |           | not null |
 customer_id | text                     |           |          |
 subject     | text                     |           |          |
 body        | text                     |           |          |
 channel     | text                     |           |          |
 team        | text                     |           |          |
 priority    | smallint                 |           |          |
 status      | text                     |           |          |
 created_at  | timestamp with time zone |           |          |
 updated_at  | timestamp with time zone |           |          |
 closed_at   | timestamp with time zone |           |          |
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (ticket_id)

SQL works as in the SQL course. End each statement with ;:

SELECT status, count(*) FROM tickets GROUP BY status;

  status  | count
----------+-------
 closed   |    42
 open     |    39
 pending  |    27
 resolved |    92
(4 rows)

One table is new to you: ticket_embeddings. It keeps a vector for each ticket: 384 numbers that an embedding model made from the ticket’s text. Tickets with similar text get vectors that are near each other, and the <=> operator of pgvector measures that distance. This query sorts the tickets by their distance from T-30002, “Charged twice for order LK-182074”, and keeps the first four:

SELECT ticket_id, subject FROM tickets ORDER BY (SELECT embedding FROM ticket_embeddings WHERE ticket_id = 'T-30002') <=> (SELECT embedding FROM ticket_embeddings e WHERE e.ticket_id = tickets.ticket_id) LIMIT 4;

 ticket_id |              subject
-----------+-----------------------------------
 T-30002   | Charged twice for order LK-182074
 T-30050   | Charged twice for order LK-710804
 T-30168   | Charged twice for order LK-919420
 T-30064   | Charged twice for order LK-167846
(4 rows)

T-30002 comes first, because its distance from itself is 0. The next three are the same problem on other orders. You do not need to understand the query yet. Storage categories explains vectors, and Module 5 makes this search fast. Stay in psql for the next section.

What is stricter than SQLite

Now give the clerk the same wrong values that SQLite accepted. In psql:

UPDATE tickets SET priority = 'abc' WHERE ticket_id = 'T-30002';
UPDATE tickets SET created_at = '2026-02-30 09:00' WHERE ticket_id = 'T-30002';
SELECT ticket_id, priority, created_at FROM tickets WHERE ticket_id = 'T-30002';

ERROR:  invalid input syntax for type smallint: "abc"
LINE 1: UPDATE tickets SET priority = 'abc' WHERE ticket_id = 'T-300...
                                      ^
ERROR:  date/time field value out of range: "2026-02-30 09:00"
LINE 1: UPDATE tickets SET created_at = '2026-02-30 09:00' WHERE tic...
                                        ^
 ticket_id | priority |       created_at
-----------+----------+------------------------
 T-30002   |        1 | 2026-09-25 08:20:19+00
(1 row)

Both changes failed, and the row did not change. A column’s type is a rule that PostgreSQL checks on every write. Type \q to leave psql.

Three more differences matter in this course:

• Foreign keys are always checked. In SQLite, a foreign key does nothing until you run PRAGMA foreign_keys = ON. In PostgreSQL, a declared foreign key is checked on every write. The help-desk tables have no foreign keys yet. When Module 2 adds them, PostgreSQL finds three messages whose ticket does not exist.
• Many clients at once. The server takes connections from the API, the tests and psql at the same time. By default, it allows 100 connections. Module 3 shares them through a connection pool.
• The server must run. A file is there whenever your program is. A server is a program, and a program can stop. The common mistake below shows the error.

When to use which

SQLite stays a good tool. Use it for one program and one user: a notebook, a script, a phone app, a test. It needs no setup, and a copy of the database is a copy of one file.

Use a database server when several programs share the data, when the data must follow strict rules, or when you need what a server adds. Here, that is pgvector for vectors, and later many connections at once.

A server has costs, and you just paid some of them:

• Something to run. The server uses memory and must be started. Docker runs it in a container. Without Docker, it runs as a service of your system.
• Something to protect. A server has users and passwords. The password is a secret in .env, and the port is open only to your own computer.
• Something to back up. The data is a directory that the server writes all the time. You cannot copy it like one file while the server runs. Module 5 makes a real backup and proves that it can be restored.

Common mistakes

Common mistake

docker compose stops: POSTGRES_PASSWORD is missing

Symptom: docker compose up -d db prints this, and nothing starts:

Error while interpolating services.api.environment.DATABASE_URL: required variable POSTGRES_PASSWORD is missing a value: Set POSTGRES_PASSWORD in .env

The service name in the message can differ, for example services.migrate.

Cause: there is no .env in the folder, or it has no POSTGRES_PASSWORD. compose.yaml refuses to start a database without a password.

Fix: run docker compose in the project folder. Copy .env.example to .env (step 2), set your password (step 3), and start the service again.

Common mistake

The server is not running

Symptom: python -m scripts.load prints this:

Cannot connect to the database: connection failed: connection to server at "127.0.0.1", port 5432 failed: could not receive data from server: Connection refused
Is PostgreSQL running? With Docker: docker compose up -d db

The words after failed: can differ between computers. python -m pytest gives 29 passed, 5 skipped: without a server, the database tests are skipped.

Cause: nothing answers at 127.0.0.1:5432. Docker Desktop is closed, the container stopped, or the computer restarted.

Diagnose: run docker compose ps. If db is missing, or its status is not healthy, the server is not running. Without Docker, check the PostgreSQL service of your system.

Fix: start Docker Desktop, then run docker compose up -d db. Wait for healthy, and run the command again. Your data is still there: it lives in a Docker volume, not in the container.

Reset. python -m scripts.load --reset loads the help-desk data again. docker compose down --volumes deletes the whole database; after it, run docker compose up -d db and python -m scripts.load. docker compose down without --volumes stops the server and keeps the data.

Knowledge check

Check your understanding

In your own words: what is the difference between SQLite and a PostgreSQL server? Use the words 'client', 'connection' and 'file'.

Show the answer

SQLite is a library inside your program, and the database is one file that your program reads and writes directly. PostgreSQL is a separate program, a server, that owns the data. Other programs are clients: they open a connection to the server, say who they are, and send SQL. Only the server reads and writes its data files, and it checks every statement.

Check your understanding

Your DATABASE_URL is postgresql://tickets:pw@127.0.0.1:5433/tickets, but the server listens on port 5432. Predict what python -m scripts.load prints, and name the part to fix.

Show the answer

It cannot connect: Cannot connect to the database: connection failed: connection to server at "127.0.0.1", port 5433 failed: …, then the hint Is PostgreSQL running?. The server runs, but behind another door: the port. Fix the port in .env to 5432. If TEST_DATABASE_URL has the same mistake, the database tests are skipped.

Check your understanding

Practical check: show that your setup works. Which two commands do you run, and what must they print?

Show the answer

python -m scripts.load --reset prints Database is up to date (4 migrations). and the counts: customers 40, tickets 200, messages 334, attachments 43, documents 36, ai_runs 294, ticket_embeddings 200. python -m pytest ends with 34 passed.

Case studies in this course

At the end of the course, you use what you learn on a realistic project, from the data to the recommendation. Complete at least one for the certificate; the others are optional.

• Design and tune storage for an open public datasetLoad a large open public dataset into PostgreSQL with a schema and constraints, list its access patterns, and measure index and pagination improvements on its real queries.
• Vector search over an open document collectionStore embeddings of an open document collection in pgvector, compare exact and approximate search on recall and time, filter by metadata, change the embedding version and delete a document everywhere.

Recap

• SQLite is a file that a library in your program reads. PostgreSQL is a database server: clients connect with a connection string (user, password, host, port, database), and the server checks every statement.
• The project ~/projects/ticket-api runs PostgreSQL 18.6 with pgvector 0.8.7, in Docker (docker compose up -d db) or from an installer.
• python -m scripts.load applies 4 migrations and loads the made-up help-desk data: 200 tickets, 334 messages, 43 files, 36 documents, 294 AI runs and 200 vectors. python -m pytest prints 34 passed.
• PostgreSQL refused the text abc as a priority and 30 February as a date. SQLite stored both.
• A server costs something: it must run, it has passwords, and it needs real backups.

Next, you load the large data, 300,000 tickets, and list how the help desk uses each kind of data.

Next: Access-pattern inventory