Introduction

Clorinde is a tool powered by rust-postgres designed to generate type-checked Rust interfaces from your PostgreSQL queries. It works by preparing your queries against an actual database and then running an extensive validation suite on them. Once the queries are prepared and validated, Rust code is generated into a module, which can be imported and used in your project. The basic premise is thus to:

1. Write your PostgreSQL queries.
2. Use Clorinde to generate Rust code.
3. Use the generated code in your project.

Compared to other Rust database interfaces, Clorinde's approach has the benefits of being simple to understand while also generating code that is both ergonomic and free of heavy macros or complex generics. Since Clorinde generates plain Rust structs, you can also easily build upon the generated items.

Here are some defining features:

• SQL-first. Your SQL is the only source of truth. No intricate ORM.
• Powerful query validation. Catch errors before runtime, with powerful (and pretty) diagnostics.
• Supports custom user types (composites, domains, and enums) and one-dimensional arrays.
• Sync and async driver support, with optional pooling.
• Ergonomic non-allocating row mapping.
• Granular type nullity control.
• Available as a library and a CLI.
• As close to native rust-postgres performance as we can make it.

Installation

Clorinde

You can use Clorinde as a CLI or a library API, depending on your needs. Make sure to check out these sections later for more info.

CLI (Recommended)

To install the latest released version of the CLI, use cargo install:

cargo install clorinde

API

Import clorinde in your project's Cargo.toml:

clorinde = "..." # choose the desired version

Container manager

When running in managed mode, Cornucopia spawns a container running a PostgreSQL instance that acts as an ephemeral database. Therefore, you need a working docker or podman command available on your system.

Docker

To use Clorinde with docker on Linux, non-sudo users need to be in the docker group. For a step-by-step guide, please read the official Docker installation and post-installation docs.

Migration from Cornucopia

Clorinde is a fork of Cornucopia which includes a few breaking changes if you want to migrate over.

Crate-based code generation

Clorinde generates a crate instead of a single file which allows Clorinde to automatically generate a Cargo.toml file customised to support all the necessary dependencies and features required by your queries, without polluting your manifest. For example, Cornucopia's "Full dependencies" example:

[dependencies]
# Required
postgres-types = { version = "*", features = ["derive"] }

# Async
cornucopia_async = { version = "*", features = ["with-serde_json-1"] }
tokio = { version = "*", features = ["full"] }
tokio-postgres = { version = "*", features = [
    "with-serde_json-1",
    "with-time-0_3",
    "with-uuid-1",
    "with-eui48-1",
] }
futures = "*"
# Async connection pooling
deadpool-postgres = { version = "*" }

# Row serialization
serde = { version = "*", features = ["derive"] }

# Extra types
serde_json = "*"
time = "*"
uuid = "*"
eui48 = "*"
rust_decimal = { version = "*", features = ["db-postgres"] }

Could be replaced with:

[dependencies]
clorinde = { path = "clorinde" }

Clorinde also re-exports the dependencies: postgres, tokio-postgres, and deadpool-postgres.

A drawback to crate-based codegen is that cargo won't publish crates with path dependencies meaning you either can't publish a crate that depends on Clorinde or you will need to publish the Clorinde crate separately.

If doing the latter, you can use a clorinde.toml to specify the [package] section of the Cargo.toml in the generated crate. For example, a clorinde.toml that includes:

[package]
name = "my-clorinde-queries"
version = "0.1.0"
license = "MIT"
homepage = "https://github.com/furina/my-repo"
repository = "https://github.com/furina/my-repo"
publish = true

Will generate clorinde/Cargo.toml with the specified [package] where you can then publish the crate as my-clorinde-queries.

chrono instead of time

Clorinde uses the chrono crate instead of time. If you want to keep using time you can add the time feature flag to the generated Clorinde crate.

[dependencies]
# If using `deadpool-postgres`
clorinde = { path = "clorinde", default-features = false, features = ["deadpool", "time"] }

# Otherwise use `time` by itself
clorinde = { path = "clorinde", default-features = false, features = ["time"] }

Supported types

Base types

Custom PostgreSQL types

Custom types like enum, composite and domain will be generated automatically by inspecting your database. The only requirement for your custom types is that they should be based on other supported types (base or custom).

Clorinde is aware of your types' namespaces (what PostgreSQL calls schemas), so it will correctly handle custom types like my_schema.my_custom_type.

Custom Rust types

You can define custom Rust types through a clorinde.toml configuration file. See "Custom Type Mappings" for more information.

Array types

Clorinde supports one-dimensional arrays when the element type is also a type supported. That is, Clorinde supports example_elem_type[] if example_elem_type is itself a type supported by Clorinde (base or custom).

Using Clorinde

You can use Clorinde as a CLI or a library API depending on your needs. The CLI is simpler and allows you to use the same binary for all your projects, but the library can be used to integrate or automate Clorinde as part of your own project.

Workflows

Clorinde is flexible with how it can be used. Here are some useful workflows when developing with Clorinde:

Basic

This is the simplest workflow. Create a queries/ directory containing your PostgreSQL queries at the root of your crate. Then, run the CLI to generate a clorinde crate in your directory. Note that the CLI will require the path to one or more PostgreSQL schemas to build against.

You're done! Now you can add the clorinde crate to your Cargo.toml.

[dependencies]
clorinde = { path = "clorinde" }

And import your generated query items from it. When you modify your queries or schemas, you can re-run the CLI to update the generated code.

Automatic query rebuild

The setup is the same as the basic workflow, but instead of using the CLI to generate your queries, create a build.rs build script that invokes Clorinde's API. Build scripts have built-in functionalities that allow them to be re-executed every time your queries or schema(s) change. See this example for details.

Self-managed database

With this workflow, you don't need a container manager at all. Set up your database in the state you want, then use Clorinde's live functionality to build directly against this database using a connection URL. Both the CLI and API support this.

CLI

The CLI exposes two main commands: schema and live.

Generating code

The code generation can be made either against a database that you manage or by letting Clorinde manage an ephemeral database container for you.

schema: Automatic container management

The clorinde schema command creates a new container, loads your schema(s), generates your queries and cleanups the container. You will need to provide the path to one or more schema files to build your queries against.

live: Manual database management

If you want to manage the database yourself, use the clorinde live command to connect to an arbitrary live database. You will need to provide the connection URL.

Useful flags

sync

By default, Clorinde will generate asynchronous code, but it can also generate synchronous code using the --sync flag.

serialize

If you need to serialize the rows returned by your queries, you can use the --serialize flag, which will derive Serialize on your row types.

podman

You can use podman as a container manager by passing the -p or --podman flag.

API

Clorinde's API offers functionalities similar to the CLI. The main benefit of the API over the CLI is to facilitate programmatic use cases and expose useful abstractions (e.g. an error type).

For more information, you can read the library API docs.

Error reporting

One of Clorinde's core goals is to provide best-in-class error reporting. For example, let's say you tried to declare a nullable field, but the query doesn't have a field with this name. You'll receive an error message such as this, before runtime:

× unknown field
   ╭─[queries/test.sql:1:1]
 1 │ --! author: (age?)
   ·              ─┬─
   ·               ╰── no field with this name was found
 2 │ SELECT * FROM author;
   ╰────
  help: use one of those names: id, name

This helps you catch any malformed query annotation, and will offer helpful hints to get you there. If your development environment supports links, you should be able to click the path (here queries/test.sql:1:1) to bring you directly to the error site in your SQL code.

Clorinde's error reporting is quite extensive and covers a lot more than the simple case above. You can take a look at our internal tests/integration crate to see our whole error reporting suite.

Error type

Clorinde's library API provides a fully fleshed-out error type that you can use if you need more complex error-handling behavior.

Configuration

Clorinde can be configured using a configuration file (clorinde.toml by default) in your project. This file allows you to customise generated code behaviour, specify static files, manage dependencies, and override type mappings.

Package configuration

The [package] section allows you to configure any standard Cargo.toml package field in the generated crate:

[package]
name = "furinapp-queries"
version = "0.1.0"
description = "Today I wanted to eat a *quaso*."
license = "MIT"
edition = "2021"

All fields specified in this section will be directly copied to the [package] section of the generated crate's Cargo.toml. This gives you complete control over the package metadata and configuration of the generated crate.

Workspace dependencies

The use-workspace-deps option allows you to integrate the generated crate with your workspace's dependency management:

# Use workspace dependencies from the current directory's Cargo.toml
use-workspace-deps = true

# Use workspace dependencies from a specific Cargo.toml
use-workspace-deps = "../../Cargo.toml"

When this option is set, Clorinde will:

1. Look for dependencies in the specified Cargo.toml file (or ./Cargo.toml if set to true)
2. Set workspace = true for any dependencies that exist in the workspace manifest
3. Fall back to regular dependency declarations for packages not found in the workspace

Custom type mappings

You can configure custom type mappings and their required dependencies using the types section:

[types.crates]
# Dependencies required for custom type mappings
ctypes = { path = "../ctypes" }
postgres_range = { version = "0.11.1", features = ["with-chrono-0_4"] }

[types.mapping]
# Map PostgreSQL types to custom Rust types
"pg_catalog.date" = "ctypes::date::Date"
"pg_catalog.tstzrange" = "postgres_range::Range<chrono::DateTime<chrono::FixedOffset>>"

The types.crates table specifies any dependencies needed for your custom type mappings. These will be added to the generated crate's Cargo.toml.

The types.mapping table allows you to map PostgreSQL types to Rust types. You can use this to either override Clorinde's default mappings or add support for PostgreSQL types that aren't supported by default, such as types from extensions.

#![allow(unused)]
fn main() {
use postgres_types::{ToSql, FromSql};

impl ToSql for CustomType {
    // ...
}

impl FromSql for CustomType {
    // ...
}
}

Derive traits

You can specify #[derive] traits for generated structs using this field.

[types]
derive-traits = ["Default", "serde::Deserialize"]

This will add the Default and serde::Deserialize traits to all structs. If you only want them added to specific structs, see this section in "Type annotations".

Custom PostgreSQL type derive traits

For more granular control in addition to traits in type annotations, you can specify traits that should only be derived for particular custom PostgreSQL types:

[types]
# Applied to all generated structs and postgres types
derive-traits = ["Default"]

[types.derive-traits-mapping]
# Applied to specfic custom postgres types (eg. enums, domains, composites)
fontaine_region = ["serde::Deserialize"]

This configuration will add the Clone trait to all generated types (and structs), but will only add serde::Deserialize to the fontaine_region enum.

You can combine global and type-specific derive traits - the traits will be merged for the specified custom PostgreSQL types.

Static files

The static field allows you to copy or link files into your generated crate directory. This is useful for including files like licenses, build configurations, or other assets that should persist across code generation.

# Simple copy of files
static = ["LICENSE.txt", "build.rs"]

# Advanced configuration with hard linking
static = [
    { path = "large_asset.bin", hard-link = true },
    "README.md"  # Mixed with simple paths
]

When hard-link = true is specified, Clorinde will create a hard link instead of copying the file. This is particularly useful for large files to save disk space.

Writing queries

Your queries consist of PostgreSQL statements using named parameters and decorated by special comment annotations.

Each query file can contain as many queries as you want and will be translated into a submodule inside your generated code file.

Named parameters

To make it easier to write robust queries, Clorinde uses named bind parameters for queries. Named bind parameters start with a colon and are followed by an identifier like :this. This is only for user convenience though, behind the scenes the query is rewritten using pure PostgreSQL syntax.

It may seem like a gratuitous deviation from PostgreSQL, but the improved expressivity is worth it in our opinion.

Rust keywords

When generating your code, Clorinde will automatically escape identifiers that collide with non-strict Rust keywords. For example, if your SQL query has a column named async, it will be generated as r#async. This can be useful sometimes, but you should avoid such collisions if possible because it makes the generated code more cumbersome.

Strict keywords will result in a code generation error.

Annotations

Each SQL query that is to be used with Clorinde must be annotated using simple SQL comments. These special comments are parsed by Clorinde and allow you to customize the generated code.

In addition to query annotations, you can also use type annotations to reuse returned columns and parameters between multiple queries.

The next subsections cover query and type annotations in greater detail.

Query annotations

Query annotations decorate a SQL statement and describe the name, parameters and returned row columns of the query.

At their most basic, they look like this

--! authors_from_country
SELECT id, name, age
FROM Authors
WHERE Authors.nationality = :country;

The --! token indicates a Clorinde query annotation, and authors_from_country is the name of the query.

Clorinde will actually prepare your queries against your schema, automatically finding the parameters, row columns and their respective types. That is why in most simple queries, you don't have to specify the parameters or row columns: only the query name is required.

That said, you can also go further than this simple syntax in order to customise your queries, as you will learn in the next sections

Nullity

By default, parameters and returned row columns will all be inferred as non-null. If you want to control their nullity, you can use the question mark (?) syntax:

--! authors_from_country (country?) : (age?)
SELECT id, name, age
FROM Authors
WHERE Authors.nationality = :country;

The (country?) and (age?) annotations mean that the parameter country and returned column age will be inferred as nullable (Option in Rust).

You can also granularly modify the nullity of composites and arrays like so:

--! example_query : (compos?.some_field?, arr?[?])
SELECT compos, arr
FROM example

Which means that the compos column and its field some_field are both nullable and that the arr column and its elements are also nullable.

Query documentation comments

You can add documentation to your queries using --- comments after the query annotation. These comments will be added as doc strings to the generated Rust code.

--! authors_from_country
--- Finds all authors from a specific country.
--- Parameters:
---   country: The nationality to filter by
SELECT id, name, age
FROM Authors
WHERE Authors.nationality = :country;

This will generate:

#![allow(unused)]
fn main() {
/// Finds all authors from a specific country.
/// Parameters:
///   country: The nationality to filter by
pub fn authors_from_country() -> AuthorsFromCountryStmt {
    // ...
}
}

Type annotations

Type annotations allow you to customize the structs that Clorinde generates for your rows (and parameters, see the section below). Furthermore, this allows you to share these types between multiple queries.

To create type annotations, declare them using the --: syntax. Type annotations only need to declare the nullable columns. Here's how it looks:

--: Author(age?)

--! authors : Author
SELECT name, age FROM Authors;

--! authors_from_country (country?) : Author
SELECT name, age
FROM Authors
WHERE Authors.nationality = :country;

This will define a struct named Author containing typed fields for the name and age columns (with age being nullable). The same struct will be used for the authors and authors_from_country queries.

Derive traits

You can specify additional #[derive] traits for the generated struct by declaring them after the type annotation.

--: Author(age?) : Default, serde::Deserialize

--! authors : Author
SELECT name, age FROM Authors;

Inline types

You can also define type inline if you don't plan on reusing them across multiple queries:

--! authors_from_country (country?) : Author()
SELECT id, name, age
FROM Authors
WHERE Authors.nationality = :country;

Notice how inline types must have a set of parenthesis describing their nullable columns. This syntax is often more compact for simple cases. It doesn't have any other special meaning otherwise.

Parameter structs

Clorinde will automatically generate a parameter struct if it has more than one column. The name of the parameter struct is based on the name of the query. You can still manually generate a parameter struct using a type annotation or an inline type.

In any case, note that you don't need a parameter struct, you can always work directly with the query function (see the section query usage).

Using your generated queries

Once you have written your queries and generated your Rust code with Clorinde, it's time to use them. Hurray 🎉!

Let's say you have generated your Rust crate into ./clorinde and added it to your Cargo.toml, then this is as simple as importing the items you need from it, like so:

#![allow(unused)]
fn main() {
use clorinde::queries::authors;
}

Building the query object

Building a query object starts with either the query function:

#![allow(unused)]
fn main() {
authors().bind(&client, Some("Greece"));
}

or the generated parameter struct:

#![allow(unused)]
fn main() {
use clorinde::{
    client::Params,
    queries::{authors, AuthorsParams}
};

authors().params(
    &client,
    AuthorsParams {
        country: Some("Greece")
    }
);
}

The query function is useful when you have a few obvious parameters, while the parameter struct is more explicit.

Note that in order to use the params method, you need to import the clorinde::client::Params trait.

Row mapping (optional)

Query objects have a map method that allows them to transform the query's returned rows without requiring intermediate allocation. The following example is pretty contrived but illustrates how you can use this feature.

#![allow(unused)]
fn main() {
enum Country {
    Greece,
    TheRest
}

impl<'a> From<&'a str> for Country {
    fn from(s: &'a str) -> Self {
        if s == "Greece" {
            Self::Greece
        } else {
            Self::TheRest
        }
    }
}

struct CustomAuthor {
    full_name: String,
    country: Country,
    age: usize,
}

authors()
    .bind(&client)
    .map(|author| {
        let full_name = format!(
            "{}, {}",
            author.last_name.to_uppercase(),
            author.first_name
        );
        let country = Country::from(author.country);
        CustomAuthor {
            full_name,
            country,
            age: author.age,
        }
    });
}

The result of a map is another query object.

Getting rows out of your queries

Once the query object has been built, use one of the following methods to select the expected number of rows:

• opt: one or zero rows (error otherwise).
• one: exactly one row (error otherwise).
• iter: iterator of zero or more rows.
• all: like iter, but collects the rows in a Vec.

Here are some example uses:

#![allow(unused)]
fn main() {
author_by_id().bind(&client, &0).opt().await?;
author_by_id().bind(&client, &0).one().await?; // Error if this author id doesn't exist
authors().bind(&client).all().await?;
authors().bind(&client).iter().await?.collect::<Vec<_>>(); // Acts the same as the previous line
}

Ergonomic parameters

To make working with bind parameters, Clorinde uses umbrella traits that allow you to pass different concrete types to the same query.

For example:

#![allow(unused)]
fn main() {
authors_by_first_name.bind(&client, &"John").all(); // This works
authors_by_first_name.bind(&client, &String::from("John")).all(); // This also works
}

Here's the list of umbrella traits and the concrete types they abstract over.

StringSql

• String
• &str
• Cow<'_, str>
• Box<str>

BytesSql

• Vec<u8>
• &[u8]

JsonSql

• serde_json::Value
• postgres_types::Json

ArraySql

• Vec<T>
• &[T]
• IterSql

Notes on IterSql

This is a wrapper type that allows you to treat an iterator as an ArraySql for the purpose of passing parameters.

Database connections

Depending on your choice of driver (sync or async) and pooling, your generated queries will accept different types of connections.

The following list details supported connections for each configuration.

Sync

• postgres::Client
• postgres::Transaction

Async

• tokio_postgres::Client
• tokio_postgres::Transaction

Async + Deadpool

• tokio_postgres::Client
• tokio_postgres::Transaction
• deadpool_postgres::Client
• deadpool_postgres::Transaction

Examples

The repository contains a few examples to get you going.

• The basic example showcases the basic workflow with simple queries. This example is available in both sync and async versions.
• The automatic query build example showcases how to integrate Clorinde's API inside a build script to automatically rebuild your Rust queries when your PostgreSQL queries change.
• The custom types example showcases how to specify custom Rust types and several other options in clorinde.toml.

How to contribute

If you have a feature request, head over to our Github repository and open an issue or a pull request.