Build a workshop booking API with FastAPI, SQLAlchemy, and Postgres

There is one place left in a workshop. Two people hit Book at the same time. Both saw an available seat, but only one should receive a confirmation.

That is the problem we'll solve in this example: a small Python API for browsing workshops, booking a place, checking a booking, and cancelling it. It uses FastAPI, SQLAlchemy 2, psycopg 3, and Alembic with ClickHouse Managed Postgres. FastAPI's generated API interface gives us somewhere to try the workflow without building a separate frontend.

The complete application includes the API, SQL migrations, sample data, and tests. Its README contains the full setup, costs, and cleanup steps. Here we'll follow the requests that make the database design matter.

1. Start with the booking workflow #

The application has four operations:

After following the README, open /docs on the running application. Pick a workshop and authorize with one of your configured attendee tokens. Create a booking with a UUID you generate once for this attempt:

1{
2  "id": "11111111-1111-4111-8111-111111111111",
3  "workshop_id": "22222222-2222-4222-8222-222222222222"
4}

Replace workshop_id with an ID from the workshop list. A new booking returns HTTP 201 with status: "confirmed". If the workshop is full, the API returns HTTP 409. Availability is a snapshot; the booking transaction makes the final decision.

The server maps bearer tokens to attendees. The request body cannot choose another attendee's identity. Reading or cancelling someone else's booking returns HTTP 404. This small example uses configured tokens; a public application can replace that mapping with a verified identity provider.

2. Connect Python to ClickHouse Managed Postgres #

ClickHouse Cloud is the overall managed data platform. Within it, ClickHouse is the analytical database service, and ClickHouse Managed Postgres is the PostgreSQL service for transactional applications.

ClickHouse Managed Postgres runs on local NVMe storage and integrates with the ClickHouse analytical database through ClickPipes CDC and the pg_clickhouse extension. Together, the two database services provide a unified transactional and analytical data stack within ClickHouse Cloud.

This application uses only the ClickHouse Managed Postgres service. Its booking rules fit relational transactions. Our Shortwave example shows how to use both database services when an application also needs analytics.

Follow the README's explicit service-creation commands, download the service's CA certificate, and configure the migration and runtime connections. The runtime uses a restricted database role. SQLAlchemy connects through its psycopg 3 dialect, using postgresql+psycopg and a synchronous engine.

Both connections use sslmode=verify-full with the downloaded CA. This verifies the certificate chain and the requested hostname, as described in the PostgreSQL TLS documentation. Keep the hostname supplied by the service in the connection settings.

3. Make the data model explain the rules #

There are three application tables in the workshop_booking schema: workshops, attendees, and bookings. A booking links an attendee to a workshop. Its cancelled_at timestamp distinguishes cancelled records from active ones, so cancellation preserves the original booking.

The initial SQL migration includes this constraint:

1CREATE UNIQUE INDEX bookings_one_active_per_attendee_workshop
2ON workshop_booking.bookings (workshop_id, attendee_id)
3WHERE cancelled_at IS NULL;

That partial unique index allows an attendee to keep past cancellations while holding at most one active booking for a workshop. A booking's primary key separately prevents duplicate IDs.

Alembic tracks migration history and applies the checked-in SQL. You can read the schema changes directly, alongside the separate bootstrap, grants, seed, and cleanup files.

4. Protect the last seat inside one transaction #

The key operation lives in services.py. A new booking follows this sequence within one transaction:

1. Lock the selected workshop row with SELECT ... FOR UPDATE.
2. Check for an existing booking with the supplied ID and validate its owner and workshop.
3. Check that booking is still open and the attendee has no active booking there.
4. Count active bookings, compare the count with capacity, and insert only when a seat remains.
5. Commit before returning confirmation.

Another request for the same workshop waits for that row lock. The lock lasts until the transaction ends, following PostgreSQL's row-locking rules. The count runs as a separate statement after the lock is acquired. At READ COMMITTED, it sees the earlier request's committed booking.

This makes the order explicit: the first successful transaction takes the last seat; the next sees a full workshop. Requests for different workshops can proceed independently. Every code path that changes availability must follow the same locking rule, including future administrative tools.

We use ordinary synchronous FastAPI route functions for the synchronous database calls. FastAPI runs these functions in its thread pool, and each operation has its own database session. The database coordinates requests across application processes too.

5. Make retries and cancellation predictable #

Suppose the insert commits but the response never reaches the caller. Retrying with the same booking UUID, attendee, and workshop returns HTTP 200 with the existing booking. It does not consume another seat.

That response reports the booking's current state. If it was subsequently cancelled, the retry returns cancelled. Booking again requires a new UUID. Reusing an ID for another existing workshop or attendee returns a conflict.

Cancellation is allowed before the workshop starts and takes the same workshop lock before setting cancelled_at. Repeating an existing cancellation returns the cancelled record without changing capacity again. Availability comes from counting active bookings, so there is no separate seat counter to increment twice.

6. Exercise the race, then adapt the example #

Use two attendee tokens to compete for a workshop with one remaining seat. Expect one confirmation and one conflict, then cancel the winner and book again. Also retry a confirmed booking, retry a cancelled booking, and attempt to read it using the other attendee's token.

On 17 September 2026, we tested the application against ClickHouse Managed Postgres 18.6 from an isolated Linux environment. The 27 integration tests passed, covering competing bookings, cancellation, retries, ownership, rollback, role permissions, and TLS checks. A separate HTTP run used two API processes: only one could take the last seat, cancellation made it available again, and a retry still returned the saved booking after a process restart. The Alembic upgrade, downgrade, and reapply test also passed.

The useful tradeoff is visible: requests for one popular workshop queue behind one row lock. Keep that transaction short. This example has no payment or calendar integration; any future external calls should happen outside the seat-allocation transaction.

Start with the application README and create a ClickHouse Cloud account with $300 in trial credits. Follow the cleanup instructions when finished. You'll have a small booking API to adapt, with ownership, retries, and the last-seat decision tied to durable Postgres state.