A small server that reads a Postgres database and turns each table into REST endpoints, so students see how a database sits behind a web API without writing one. The lab runs it for every student beside their own Postgres.

Is the data kept between sessions?

Yes. The database keeps its files in the named volume pgdata, which the platform saves, and so is the student’s home folder. Anything outside a volume is gone when the lab stops, which is how containers behave in production.

How do students connect to the database and the API in this lab?

In the terminal, psql -h db -U shop opens the database, with the password shop, and curl http://api:3000/notes reads the notes table once it exists. Each is reached by its service name, the way services find each other in production.

PostgreSQL and PostgREST lab: a database and a REST API in the browser

This lab gives each student their own PostgreSQL database and their own PostgREST, which turns every table in the database into a REST API. A terminal beside the two has psql and curl, so a student creates a table in SQL and then reads and writes it over HTTP.

It is one compose file, and it runs on your own machine too.

What students learn

Creating a table and talking to a database by its service name, the way services find each other in production. Reading and writing rows through a REST API with curl, including filtering and ordering. What survives a restart and what does not: the database keeps its files in a named volume, so the notes are still there next time, while a file written outside a volume is gone when the lab stops. Backing up with pg_dump and restoring from the file.

Run it in your browser

Try it yourself first. The password for the database user shop is shop.

Open the Container page. Open Build on the bar and choose Run a container. Anybody may open it, and the Git lab and the SQL lab start with no account. Every other lab needs a free account: create one, sign in, then press Start.
Find the lab. The Sample labs card lists every sample. Find the database with a web API.
Press Start. Start runs it as it is, in one press. Edit the file first opens the compose file so you can change it before you start.
Open it. On the lab’s page, under In your browser, press A web API over every table and add /notes to the address once you have made the table. In the terminal, psql -h db -U shop opens the database and curl http://api:3000/notes calls the API.

The code on GitHub

The whole lab is one compose file in a public repository, which also holds the lesson it was written for, step by step. It runs on your own machine too: docker compose up. Fork it to make it your own.

A basic lesson on this lab

API Basics and API Basics 2 are small lessons worked in this lab, each step explained and with a Run button that types its command into the student’s own terminal. In API Basics they make a table, put a note in it, read it over the web with curl and add one with a POST request. In API Basics 2 they change a note, delete one and read the status codes the API answers with.

A free personal account is given them on the Basics module, which is on Home, every college can copy them from the Library’s Basics folder, and a new college opens with them in a module called Lab Basics (Sample). They are deliberately small: they show a teacher what a lesson worked in a lab is, and the lab does the rest. Copy one into a module of your own to change it.

Set it as work

To give it to a class, make a lab of your own from the repository.

Make the lab. In your module add a task and choose the Container lab card, then In a GitHub repository. Give https://github.com/mitsurutech/lab-sample with the branch main. The compose file is the default, compose.yaml. Fork the repository first if you want to change it.
Publish and try it. Publish pins the commit you were shown, so a later change in the repository does not alter a lab you have already set. Start your own copy first.
Hand it out. Assign it like any task, with a due date if you want one, and say in the task what to do and what to hand in.
Mark what is handed in. Open a student’s copy and the work they handed in, mark it, and send it back with a reason if it needs more.

Questions people ask

What is PostgREST?: A small server that reads a Postgres database and turns each table into REST endpoints, so students see how a database sits behind a web API without writing one. The lab runs it for every student beside their own Postgres.
Is the data kept between sessions?: Yes. The database keeps its files in the named volume pgdata, which the platform saves, and so is the student’s home folder. Anything outside a volume is gone when the lab stops, which is how containers behave in production.
How do students connect to the database and the API in this lab?: In the terminal, psql -h db -U shop opens the database, with the password shop, and curl http://api:3000/notes reads the notes table once it exists. Each is reached by its service name, the way services find each other in production.

Create a free account to run every lab.