🦜 Parrot / type-safe SQL in gleam (https://gleam.run/)

Package VersionHex Docserlang

🚨 Exciting News<br /> Parrot got listed a community project on the sqlc website! 🦜🎉<br /> Check it out here: https://docs.sqlc.dev/en/latest/reference/language-support.html

Table of Contents

Table of contents generated with markdown-toc

Features

Most of the heavy lifting features are provided by / built into sqlc, I do not aim to take credit for them.

☑️ Supports SQlite, PostgreSQL and MySQL.<br /> ☑️ Multiple queries per file.<br /> ☑️ Database client agnostic.<br /> ☑️ Utility wrappers for popular gleam database libraries (lpil/sqlight, lpil/pog).<br /> ☑️ Automatically pulls the schema of your database.<br /> ☑️ Automatically downloads sqlc binary.<br /> ☑️ Named parameters.<sup>*1</sup> <br />

*1
## Code Showcase Given this SQL: ```sql -- name: CreateUserWithRole :exec insert into users (username, role) values ($1, $2) returning id; -- name: GetUserByUsername :one select id, username, created_at, date_of_birth, profile, extra_info, favorite_numbers, role, document from users where username = $1 limit 1; ``` Parrot will generate gleam code with matching types, decoders and argument names for you: ```gleam pub type CreateUserWithRole { CreateUserWithRole(id: Int) } pub fn create_user_with_role( username username: String, role role: Option(UserRole), ) { let sql = "insert into users (username, role) values ($1, $2) returning id" #(sql, [ dev.ParamString(username), dev.ParamNullable( option.map(role, fn(v) { dev.ParamString(user_role_to_string(v)) }), ), ]) } pub fn create_user_with_role_decoder() -> decode.Decoder(CreateUserWithRole) { use id <- decode.field(0, decode.int) decode.success(CreateUserWithRole(id:)) } pub type GetUserByUsername { GetUserByUsername( id: Int, username: String, created_at: Option(Timestamp), date_of_birth: Option(Date), profile: Option(String), extra_info: Option(String), favorite_numbers: Option(List(Int)), role: Option(UserRole), document: Option(BitArray), ) } pub fn get_user_by_username(username username: String) { let sql = "select id, username, created_at, date_of_birth, profile, extra_info, favorite_numbers, role, document from users where username = $1 limit 1" #(sql, [dev.ParamString(username)], get_user_by_username_decoder()) } pub fn get_user_by_username_decoder() -> decode.Decoder(GetUserByUsername) { use id <- decode.field(0, decode.int) use username <- decode.field(1, decode.string) use created_at <- decode.field(2, decode.optional(dev.datetime_decoder())) use date_of_birth <- decode.field( 3, decode.optional(dev.calendar_date_decoder()), ) use profile <- decode.field(4, decode.optional(decode.string)) use extra_info <- decode.field(5, decode.optional(decode.string)) use favorite_numbers <- decode.field( 6, decode.optional(decode.list(of: decode.int)), ) use role <- decode.field(7, decode.optional(user_role_decoder())) use document <- decode.field(8, decode.optional(decode.bit_array)) decode.success(GetUserByUsername( id:, username:, created_at:, date_of_birth:, profile:, extra_info:, favorite_numbers:, role:, document:, )) } ``` If you want to see more code how this lirbary works in action, take a look at the integration tests: - PostgreSQL: [./integration/psql](./integration/psql) - MySQL: [./integration/mysql](./integration/mysql) - SQlite: [./integration/sqlite](./integration/sqlite)

Usage / Getting Started

Installation

$ gleam add parrot

Define your Queries

Here are some links to help you start out, if you are unfamiliar with the sqlc annotation syntax:

Here is an example of the file structure:

├── gleam.toml
├── README.md
├── src
│   ├── app.gleam
│   └── sql
│       ├── auth.sql
│       └── posts.sql
└── test
   └── app_test.gleam

Code Generation

# automatically detects database & engine from env (DATABASE_URL by default)
$ gleam run -m parrot

# provide connection string from different environment variable
$ gleam run -m parrot -- -e PG_DATABASE_URL

# specify sqlite file
$ gleam run -m parrot -- --sqlite <file_path>

# see all options
$ gleam run -m parrot help

If you use SQLite, you also need to have installed sqlite3.

If you use MySQL, you also need to have installed mysqldump (comes by default if you have a mysql client installed).

If you use PostgreSQL, you also need to have installed pg_dump (comes by default if you have a postgresql client installed).

Run it!

You now have type safe access to your sql queries.

You might want to write wrapper functions for the database client library of your choice. If you are using lpil/pog or lpil/sqlight, you are in luck! You can find functions to copy & paste into your codebase here: wrappers

An example with lpil/sqlight:

import app/sql
import parrot/dev

fn parrot_to_sqlight(param: dev.Param) -> sqlight.Value {
  // ...
}

pub fn main() {
  // ...

  let #(sql, with, expecting) = sql.get_user_by_username("alice")
  let with = parrot_to_sqlight(with)
  let row = sqlight.query(sql, on:, with:, expecting:)

  // ...
}

Examples

If you want to see how this library works in action, take a look at the integration tests:

Development

just is used to run project commands.

Database

There are scripts to spawn a MySQL or PostgreSQL Podman container:

For example:

$ ./bin/mysql.sh
# or
$ ./bin/psql.sh

Integration Test Suite

$ just test-sqlite
$ just test-mysql
$ just test-psql

Quirks

As with everything in software, there are some quirks with this library, due to the nature of your database of choice and sqlc.

Functions

Wether it be custom or built-in functions in postgres, sqlc struggles to infer types for those types of queries. Luckily sqlc provides a way to annotate columns directly. Parrot will soon provide support for direct database connections. Until then, you will have to annotate the type of those columns.

Overview over all types can be found here:

You can annotate column types like so:

-- name: GetTournamentChampionBets :many
SELECT id::uuid,
       created_by::uuid,
       updated_by::uuid,
       updated_at::timestamp,
       tournament_name::text,
       team_name::text
FROM get_tournament_champion_bets_safe();

Multidimensional Arrays

If you have an INTEGER[][] column in Postgres, pg_dump does not correctly identify the column as a two-dimensional array and therefore only gives you a List(Int) instead of a List(List(Int)). If this is a problem for you, you can raise an issue and we might come up with a solution or workaround.

Dynamic Data Types

There are a couple of complex data types that are explictly made dynamic since they are too complex to handle with the current implementation. There is a plan for a better and more flexible implementation. Until then, it will be wrapped in a dynamic type.

Targetting JavaScript

So here is the catch: you can only execute parrot in an erlang gleam application. However the generated code will also run in a javascript environment. So if you need parrot for a javascript project, you can create a separate package and copy over the generated module and that will work.

FAQ

What flavour of SQL does parrot support?

This library 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?

Future Work

Ideas and actionable tasks are collected and organised here: https://github.com/daniellionel01/parrot/issues

Contributions are welcomed!

Acknowledgements