Using the SQL ORM

This template uses ZIO Quill (opens in a new tab), an ORM that uses a Quoted Domain Specific Language (QDSL) to generate SQL queries from your functional code.

The necessary steps are already done if you have set add_sql_orm to true, but we still will go over them, so you can understand.

Setup

Create db

You need to start a postgres database with the correct schema (the one you want) if you want to use our example you need to create a example_entity table with the following schema:

CREATE TABLE example_entity (
    id VARCHAR(255) NOT NULL,
    value INT NOT NULL,
    PRIMARY KEY (id)
);

Environment variables

You will also need to configure the connection to your database, you can do this by setting the following environment variables:

export POSTGRES_USER=postgres;
export POSTGRES_PASSWORD=postgres;
export POSTGRES_HOST=localhost;
export POSTGRES_PORT=5432;
export POSTGRES_NAME=postgres;

Note : The default values are the ones used in the example above. You can run the server without setting any environment variables.

Code

First define your model:

final case class ExampleEntity(id: String, value: Int)

You can now inject quill into a service that will need it ...

//                    👇 Inject quill with the Postgres dialect and the snake case naming strategy
class ExampleService(quill: Quill.Postgres[SnakeCase]) {
  import quill._ // 👈 Import the quill context, it allows your controller to execute the run function
 
  def getById(id: String): ZIO[Any, SQLException, Option[ExampleEntity]] = {
    run(quote {
      query[ExampleEntity].filter(_.id == lift(id))
    }).map(_.headOption)
  }
 //...
}

We now add a ZLayer to our ExampleService so the provided quill context will be available to our controller:

object ExampleService {
  val live = ZLayer.fromFunction(new ExampleService(_))
}

You can finally provide the configuration to our service, here we do it in main:

object Main extends ZIOAppDefault {
 
  override def run =
    for {
 
      _ <- ZIO.serviceWithZIO[ExampleService](
        _.insertOne(randomUUID.toString, 1)
      ).provide(
        ExampleService.live, // 👈 Provide the service so it can be constructed with the quill context
        Quill.Postgres.fromNamingStrategy(SnakeCase),
        Quill.DataSource.fromPrefix("db.default") // 👈 Provide the configuration
      ).debug(
        "Result: "
      )
 
    } yield 0
}

And now you are good to go! 🚀

Message Queue Deployment