SQL -> Gleam (sqlc-gen-gleam)

Project Status

this project is currently pre-alpha. it will be released under v1.0.0 once ready for usage.

How it works

This library makes use of a sqlc-gen-json plugin, which it then converts into gleam code.

So in a simplified manner, the pipeline looks like this:

graph LR
    SQL[SQL Queries] -- sqlc-gen-json --> JSON[JSON Schema]
    JSON -- sql-gen-gleam --> Gleam[Gleam Code]

An Example

Let’s take the following SQL queries as an example:

-- name: GetAuthor :one
SELECT
  *
FROM
  authors
WHERE
  id = ?
LIMIT
  1;

-- name: ListAuthors :many
SELECT
  *
FROM
  authors
ORDER BY
  name;

The --name: GetAuthor :one comment is part of sqlc and will be used to generate the name and return type of the wrapper.

Given the queries above, the following code will be generated:

//// file: src/gen/sqlc_mysql.gleam

import gleam/option.{type Option}

pub type GetAuthor {
  GetAuthor(
    id: Int,
    created_at: Int,
    name: String,
    bio: Option(String)
  )
}
pub fn get_author_sql(id: Int) {
  let sql = "
  SELECT
    *
  FROM
    authors
  WHERE
    id = ?
  LIMIT
    1;
  "

  #(sql, #(id))
}

pub type ListAuthorRow {
  ListAuthorRow(
    id: Int,
    created_at: Int,
    name: String,
    bio: Option(String)
  )
}
pub type ListAuthors = List(ListAuthorRow)
pub fn list_authors_sql(id: Int) {
  let sql = "
  SELECT
    *
  FROM
    authors
  ORDER BY
    name;
  "

  #(sql, Nil)
}

Every SQL query wrapper follows the schema of #(SQL, Params). So the first element is the raw SQL that can be executed by your database driver and the second element is a tuple of all of the parameters that you need for this query.

If you’re using one of the supported drivers, we can also generate the query execution parsing of the return type for you. If you are using a custom driver, you will have to do that yourself using the gleam/dynamic package.

Supported Drivers

• MySQL: https://github.com/VioletBuse/gmysql
• PostgreSQL: https://github.com/lpil/pog
• SQlite: https://github.com/lpil/sqlight

Usage

1. Install

$ gleam add parrot@1

2. Setup sqlc

Now setup your sqlc.yaml, schema.sql and query.sql like in any other sqlc project.

Here are some links to help you start out:

• Getting started with MySQL
• Getting started with PostgreSQL
• Getting started with SQlite

2. Generate Gleam code

# we first need the json from sqlc
$ sqlc generate

# then we use this library to turn it into gleam code
$ gleam run -m parrot

Further documentation can be found at https://hexdocs.pm/parrot.

Development

1. Database

There are scripts to spawn MySQL or PostgreSQL docker container:

• MySQL Script
• PostgreSQL Script

For example:

$ ./scripts/mysql/docker.sh
# or
$ ./scripts/psql/docker.sh

2. Generating the JSON schema

This library uses the JSON schema, generated by sqlc, to generate gleam code. To generate the JSON with the example schema & queries:

$ ./scripts/sqlc.sh # wrapper for "sqlc generate" with additional parameters

3. Running the project and tests

$ gleam run   # Run the project
$ gleam test  # Run the tests

FAQ

What flavour of SQL does sqlc-gen-gleam support?

This plugin supports everything that sqlc supports. As the time of this writing that would be MySQL, PostgreSQL and SQlite.

You can read more on language & SQL support here: https://docs.sqlc.dev/en/stable/reference/language-support.html

What sqlc features are not supported?

• embeddeding structs (https://docs.sqlc.dev/en/stable/howto/embedding.html)

Development

Tests

You can run a suite of integration tests via the module ./test/integration_test.gleam. You can run this module with this script:

$ ./scripts/integration-test.sh

Acknowledgements

• This project was heavily inspired by squirrel (Hex, GitHub). Thank you @giacomocavalieri!
• Thank you to sqlc (GitHub, Website)
• Thank you to dedent (GitHub, Hex)

License

Apache License, Version 2.0