SQL CRUD API

Source: tutorial/07_sql_crud_api See in Playground

A tiny “notes” API backed by PostgreSQL: POST /notes stores the request body as plain text, GET /notes lists every stored note. It combines a SqlPool model shared across requests with an HttpServer model in the same program.

Note

Requires a reachable PostgreSQL database. Point db_url at any Postgres instance to try it.

Running


cd tutorial/07_sql_crud_api
melodium run Compo.toml --db_url postgresql://user@localhost/notes_db


curl -X POST http://127.0.0.1:8080/notes -d "buy milk"
curl http://127.0.0.1:8080/notes


1) buy milk

Optional: add --api-report and an API token (MELODIUM_API_TOKEN) to see this run’s full trace on Cadence.CI .

How it works

main instantiates two models, db (a SqlPool) and server (an HttpServer), and passes them down to the route treatments through model configuration parameters:


model AppDb(const db_url: string) : SqlPool {
    url             = db_url
    min_connections = 1
    max_connections = 5
}
 
treatment main(
    const db_url: string = "postgresql://postgres@localhost/notes_db",
    const port:   u16    = 8080
)
  model db:     AppDb(db_url=db_url)
  model server: HttpServer(host=|from_ipv4(|localhost_ipv4()), port=port)
{
    startup()
    connect[sql_pool=db]()
    startup.trigger -> connect.trigger
 
    connected[sql_pool=db]()
    createTable[db=db]()
    connected.trigger -> createTable.trigger
 
    start[http_server=server]()
    logReady: logInfoMessage(label="server", message="notes API ready")
    createTable.done -> start.trigger
    createTable.done -> logReady.trigger
 
    createNote[db=db, http_server=server]()
    listNotes[db=db, http_server=server]()
}

The data flow ties the database setup to the server startup:

connect is fired once at startup, and the connected source treatment starts a track once the pool is actually ready: createTable, and everything downstream including starting the HTTP server, only runs after that, so no request can race the table’s creation.

Writing: `execute` with a single bind

POST /notes reduces the body to a single Block<string> with trigger.last (the same “collapse a one-item stream to a block” idiom used to compute totals from a stream of numbers), wraps it in a Map, and passes it as the bind parameter to execute:


treatment insertNote[db: SqlPool]()
  input  data: Stream<byte>
  output data: Stream<byte>
{
    decoded: decode()
    Self.data -> decoded.data
 
    textBlock: trigger<string>()
    decoded.text -> textBlock.stream
 
    bindMap: blockMapEntry<string>(key="text")
    textBlock.last -> bindMap.value
 
    doInsert: execute[sql_pool=db](sql="INSERT INTO notes (text) VALUES (?)", bindings=["text"])
    bindMap.map -> doInsert.bind
 
    insertFailed: logErrorMessage(label="sql", message="insert failed")
    insertError:  logError(label="sql")
    doInsert.failed -> insertFailed.trigger
    doInsert.error  -> insertError.message
 
    confirm: emit<string>(value="created\n")
    asBytes: stream<string>()
    encoded: encode()
    doInsert.completed -> confirm.trigger,emit -> asBytes.block,stream -> encoded.text,data -> Self.data
}

execute’s SQL uses the default ? placeholder; for a PostgreSQL connection it is automatically rewritten to $1, $2, and so on before reaching the driver, so INSERT INTO notes (text) VALUES (?) never has to be written by hand as $1.

Reading: `fetch` streamed row by row

GET /notes streams its response row by row: fetch’s data output emits each row as soon as it arrives from the database, and each row is turned into one "id) text\n" line written straight into connection.data. The HTTP response grows as rows arrive, nothing is buffered client-side:


treatment listRows[db: SqlPool]()
  input  trigger: Block<void>
  output lines:   Stream<byte>
{
    emitBind: emit<Map>(value=|mmap([]))
    rows: fetch[sql_pool=db](sql="SELECT id::text AS id, text FROM notes ORDER BY id", bindings=[])
    Self.trigger -> emitBind.trigger,emit -> rows.bind
 
    rowErrors: logErrors(label="sql")
    rows.errors -> rowErrors.messages
 
    id:   mapGet<string>(key="id")
    text: mapGet<string>(key="text")
    rows.data -> id.map
    rows.data -> text.map
 
    idOr:   unwrapOr<string>(default="?")
    textOr: unwrapOr<string>(default="")
    id.value   -> idOr.option
    text.value -> textOr.option
 
    line: format(format="{id}) {text}\n")
    idOr.value   -> asEntry.value
    asEntry: entry(key="id")
    asEntry.map -> withText.base
    textOr.value -> withText.value
    withText: insert(key="text")
    withText.map -> line.entries
 
    line.formatted -> encoded.text
    encoded: encode()
    encoded.data -> Self.lines
}

SELECT id::text AS id, text FROM notes casts id to text in SQL, rather than guessing which native integer type (i32? i64?) the Postgres driver maps SERIAL to: get<string> then always matches. When a value’s exact Mélodium type coming back from a driver is uncertain, it is often simpler to coerce it to string in the query itself than to guess and get it wrong silently, since get<T> returns none on a type mismatch rather than an error.

Both routes drive their response from connection.started rather than a body-derived trigger, since GET /notes has no request body at all either.

Dependencies


[dependencies]
std      = "0.10.3"  # core flows, logging, data structures
http     = "0.10.3"  # HTTP client and server
net      = "0.10.3"  # IP address helpers
sql      = "0.10.3"  # connection pools, fetch, execute
encoding = "0.10.3"  # UTF-8 encode / decode

SQL CRUD API

Running

How it works

Writing: execute with a single bind

Reading: fetch streamed row by row

Dependencies

Writing: `execute` with a single bind

Reading: `fetch` streamed row by row