#17 SQL setup

January 03, 2023
See the code for this post on the sql-setup branch.

We will use PostgreSQL to persist the data on drivers, riders, and trips. PostgreSQL (aka "Postgres") is open-source, can handle large amounts of reads and writes, and supports transactions. It has a long track record and a strong community behind it. In the past, Uber has also used Postgres as its primary data store.

Thanks to Docker, setting up Postgres is a breeze. We don't need a build step via a Dockerfile, as everything is taken care of via the base image. To start a Docker container, run the following:

docker run -d \
--name db-postgres \
-p 5432:5432 \
--mount type=volume,src=app-db,target=/var/lib/postgresql/data \
-e POSTGRES_PASSWORD=mysecretpassword \

By default, Postgres listens on port 5432. We are exposing this port and mapping it to the same port on our machine. We are also passing a password, which is required.

You might have also noticed we're mounting the /var/lib/postgresql/data to the host machine. This is where Postgres stores data by default. By mounting this directory, we are persisting our data on the host machine, so that they will be saved across container restarts.

If you want to see where Docker stores mounted volumes on the host machine, use the docker volume inspect command:

docker volume inspect app-db
"Mountpoint": "/var/lib/docker/volumes/app-db/_data",

To start a Postgres container on our production server, all we have to do is copy the same command to the prod_deploy.sh script, prefixing the docker run command with sudo. It's really that simple.

sudo docker run -d \

Let's now try a quick interaction with the database to ensure everything is working correctly. First, start an interactive terminal session in the running container:

docker exec -it db-postgres bash

Connect to the PostgreSQL server as the postgres user:

psql -U postgres

You can list all databases with the \l command:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)

Let's add a drivers table.

phone VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
license_number VARCHAR(255) NOT NULL

Add some records to the table:

INSERT INTO drivers (name, phone, email, password, license_number) VALUES(
INSERT INTO drivers (name, phone, email, password, license_number) VALUES(

You can list the tables within the currently selected database with \dt:

postgres=# \dt
List of relations
Schema | Name | Type | Owner
public | drivers | table | postgres
(1 row)

Finally, query the table:

postgres=# SELECT * FROM drivers;
id | name | phone | email | password | license_number
1 | Alice | +31612345678 | alice@example.com | b7Fkd9Lm | XX-ZZ-23
2 | Michael | +31687654321 | michael@example.com | a5Sgf8Wx | BB-CC-45
(2 rows)
See the code for this post on the sql-setup branch.