---
title: "Marina Berth Booking"
description: "Marina berth booking API: string primary keys, binary columns, berth fees and stay bands as computed columns"
url: "https://www.pixeltable.com/recipes/marina-berths"
source: "https://github.com/pierrebrunelle/pixeltable-marina-berths"
---

# Marina Berth Booking

Marina berth booking API: string primary keys, binary columns, berth fees and stay bands as computed columns

One `app.py` with 3 tables, 4 computed columns, 9 HTTP routes. Needs `pixeltable[serve]>=0.7.14`.

## Tables

### slips (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| slip_id | primary key | `` |
| dock | stored | String |
| length_m | stored | Float |
| power_amps | stored | Int |
| label | computed on insert | `slip_label(dock, slip_id, length_m)` |

### vessels (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| name | stored | String |
| loa_m | stored | Float |
| owner | stored | String |
| note_bin | stored | Binary (optional) |
| size | computed on insert | `size_class(loa_m)` |

### bookings (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| slip_id | stored | String |
| vessel_name | stored | String |
| loa_m | stored | Float |
| nights | stored | Int |
| fee | computed on insert | `berth_fee(loa_m, nights)` |
| band | computed on insert | `stay_band(nights)` |

## Routes (service `marina_api`)

- `POST /slips`: inserts into `slips` (takes slip_id, dock, length_m, power_amps; returns slip_id, label)
- `POST /vessels`: inserts into `vessels` (takes name, loa_m, owner; returns id, size)
- `POST /bookings`: inserts into `bookings` (takes slip_id, vessel_name, loa_m, nights; returns id, fee, band)
- `POST /bookings/extend`: updates `bookings` (takes nights; returns id, nights, fee, band)
- `POST /bookings/cancel`: deletes from `bookings`
- `POST /band`: computes `bookings` columns without storing (takes nights; returns band)
- `POST /quote`: computes `bookings` columns without storing (takes loa_m, nights; returns fee, band)
- `GET /slips/fit`: runs the query `slips_that_fit` (takes loa_m)
- `GET /slips/bookings`: runs the query `slip_bookings` (takes slip_id)

## Run it locally

```sh
git clone https://github.com/pierrebrunelle/pixeltable-marina-berths
cd pixeltable-marina-berths
uv sync
uv run pxt schema update app.py marina
uv run python seed.py marina
uv run pxt service run app.py marina --port 8000
```

## Run it on Pixeltable Cloud

```sh
uv run pxt new    # a free database, no account
# put the pxt:// address it prints in pixeltable.toml
uv run pxt db update pxt://<org>:main -f
uv run pxt schema update app.py pxt://<org>:main -f
uv run pxt service update app.py pxt://<org>:main -f
```

## app.py

At commit `0c9ace2a` (https://github.com/pierrebrunelle/pixeltable-marina-berths/blob/0c9ace2ae2921f685518988fad86e63a67ae299a/app.py):

```python
"""Marina Berth Booking API built with Pixeltable.

    pxt schema update app.py marina
    pxt service run app.py marina
"""
import pixeltable as pxt
import pixeltable.functions as pxtf
from pixeltable.serving import FastAPIRouter

from udfs import berth_fee, size_class, slip_label, stay_band

# ---- tables ----
TableModel = pxt.model_base()


class Slips(TableModel, name='slips'):
    slip_id = pxt.Column(type=pxt.String, primary_key=True)
    dock: pxt.String
    length_m: pxt.Float
    power_amps: pxt.Int

    label = slip_label(dock, slip_id, length_m)


class Vessels(TableModel, name='vessels'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    name: pxt.String
    loa_m: pxt.Float
    owner: pxt.String
    note_bin: pxt.Binary | None

    size = size_class(loa_m)


class Bookings(TableModel, name='bookings'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    slip_id: pxt.String
    vessel_name: pxt.String
    loa_m: pxt.Float
    nights: pxt.Int

    fee = berth_fee(loa_m, nights)
    band = stay_band(nights)


# ---- queries ----
@pxt.query
def slips_that_fit(loa_m: float):
    """Slips long enough for a vessel, tightest fit first."""
    return Slips.where(Slips.length_m >= loa_m).select(Slips.slip_id, Slips.label, Slips.power_amps).order_by(Slips.length_m)


@pxt.query
def slip_bookings(slip_id: str):
    return Bookings.where(Bookings.slip_id == slip_id).select(
        Bookings.id, Bookings.vessel_name, Bookings.nights, Bookings.fee, Bookings.band
    ).order_by(Bookings.vessel_name)


# ---- routes ----
marina_api = FastAPIRouter(name='marina_api')
marina_api.add_insert_route(Slips, path='/slips', inputs=[Slips.slip_id, Slips.dock, Slips.length_m, Slips.power_amps],
                            outputs=[Slips.slip_id, Slips.label])
marina_api.add_insert_route(Vessels, path='/vessels', inputs=[Vessels.name, Vessels.loa_m, Vessels.owner],
                            outputs=[Vessels.id, Vessels.size])
marina_api.add_insert_route(Bookings, path='/bookings',
                            inputs=[Bookings.slip_id, Bookings.vessel_name, Bookings.loa_m, Bookings.nights],
                            outputs=[Bookings.id, Bookings.fee, Bookings.band])
marina_api.add_update_route(Bookings, path='/bookings/extend', inputs=[Bookings.nights],
                            outputs=[Bookings.id, Bookings.nights, Bookings.fee, Bookings.band])
marina_api.add_delete_route(Bookings, path='/bookings/cancel')
marina_api.add_compute_route(Bookings, path='/band', inputs=[Bookings.nights], outputs=[Bookings.band])
marina_api.add_compute_route(Bookings, path='/quote', inputs=[Bookings.loa_m, Bookings.nights],
                             outputs=[Bookings.fee, Bookings.band])
marina_api.add_query_route(path='/slips/fit', query=slips_that_fit, method='get')
marina_api.add_query_route(path='/slips/bookings', query=slip_bookings, method='get')
```