DB setup

Create a new Postgres database with Docker:

docker run --name sharaf-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

Then connect to it via psql (or your favorite SQL tool):

docker exec -it sharaf-postgres psql -U postgres postgres

and create a table:

CREATE TABLE customers(
  id SERIAL PRIMARY KEY,
  name VARCHAR
);

Squery setup

Sharaf recommends the Squery library for accessing databases with a JDBC driver.

Create a file sql_db.sc and paste this code into it:

    //> using scala "3.4.0"
//> using dep org.postgresql:postgresql:42.7.1
//> using dep com.zaxxer:HikariCP:5.1.0
//> using dep ba.sake::sharaf:0.4.0
//> using dep ba.sake::squery:0.3.0

import io.undertow.Undertow
import ba.sake.tupson.JsonRW
import ba.sake.squery.{*, given}
import ba.sake.sharaf.*, routing.*

val ds = com.zaxxer.hikari.HikariDataSource()
ds.setJdbcUrl("jdbc:postgresql://localhost:5432/postgres")
ds.setUsername("postgres")
ds.setPassword("mysecretpassword")

val ctx = new SqueryContext(ds)

Here we set up the SqueryContext which we can use for accessing the database.

Querying

Now we can do some querying on the db:

case class Customer(name: String) derives JsonRW

val routes = Routes:
  case GET() -> Path("customers") =>
    val customerNames = ctx.run {
      sql"SELECT name FROM customers".readValues[String]()
    }
    Response.withBody(customerNames)

  case POST() -> Path("customers") =>
    val customer = Request.current.bodyJson[Customer]
    ctx.run {
      sql"""
      INSERT INTO customers(name) 
      VALUES (${customer.name})
      """.insert()
    }
    Response.withBody(customer)

Running the server

Finally, we need to start up the server:

    Undertow.builder
  .addHttpListener(8181, "localhost")
  .setHandler(SharafHandler(routes))
  .build
  .start()

println(s"Server started at http://localhost:8181")

and run it like this:

scala-cli sql_db.sc 

Then you can try the following requests:

# get all customers
curl http://localhost:8181/customers

# add a customer
curl --request POST \
  --url http://localhost:8181/customers \
  --data '{
    "name": "Bob"
  }'