Introduction

Clorinde is a tool powered by rust-postgres designed to generate type-checked Rust interfaces from PostgreSQL queries, with an emphasis on compile-time safety and high performance. It works by preparing your queries against an actual database and then running an extensive validation suite on them. Rust code is then generated into a separate crate, 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 a crate with type-safe interfaces to those queries.
3. Import and 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.

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 it 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 code generation 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 [manifest.package] section of the Cargo.toml in the generated crate. For example, a clorinde.toml that includes:

[manifest.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, use "Custom Type Mappings" to map the Postgres types to the time crate.

[types.mapping]
"pg_catalog.timestamp" = "time::PrimitiveDateTime"
"pg_catalog.timestamptz" = "time::OffsetDateTime"
"pg_catalog.time" = "time::Time"
"pg_catalog.date" = "time::Date"

[manifest.dependencies]
time = { version = "0.3", features = ["serde"] }
# enable the time feature of postgres and tokio-postgres
postgres = { version = "0.19", features = [
    "with-time-0_3",
    "with-serde_json-1",
] }
tokio-postgres = { version = "0.7", features = [
    "with-time-0_3",
    "with-serde_json-1",
] }

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 three main commands: schema, live, and fresh.

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. This requires docker or podman to be installed.

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.

fresh: Temporary database on existing server

The clorinde fresh command provides a middle-ground approach between schema and live. It connects to an existing PostgreSQL server, creates a temporary database, loads your schema files, generates your queries, and then drops the temporary database. This is useful when you have an existing PostgreSQL server but want the convenience of automatic schema loading without managing containers.

Example Usage

Here are some examples of using the different commands:

# Using schema command with container management
clorinde schema schema.sql

# Using live command with existing database
clorinde live postgresql://user:pass@localhost/mydb

# Using fresh command with existing server
clorinde fresh schema.sql --url postgresql://user:pass@localhost

# Using fresh command with custom database name and search path
clorinde fresh schema.sql --url postgresql://user:pass@localhost \
  --db-name my_temp_db \
  --search-path public,custom_schema

Useful flags

sync

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

serialize (DEPRECATED)

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.

[types]
derive-traits = ["serde::Serialize"]

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 behaviour.

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.

Manifest configuration

The [manifest] section allows you to configure the entire Cargo.toml for the generated crate:

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

[manifest.dependencies]
serde = { version = "1.0", features = ["derive"] }
my_custom_types = { path = "../types" }

This gives you complete control over the generated Cargo.toml. Clorinde will automatically merge your configuration with the required PostgreSQL dependencies based on the types found in your SQL queries.

Dependency merging

Clorinde automatically adds dependencies based on your PostgreSQL schema:

• Core dependencies: postgres-types, postgres-protocol, postgres
• Type-specific dependencies: chrono, uuid, serde_json, etc. (based on column types)
• Async dependencies: tokio-postgres, futures, deadpool-postgres (when async enabled)

Your custom dependencies in [manifest.dependencies] will be preserved and merged with these auto-generated ones.

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 using the types section:

[manifest.dependencies]
# 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>>"

Dependencies needed for your custom type mappings should be specified in [manifest.dependencies].

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 = ["serde::Serialize", "serde::Deserialize", "Hash"]

This will add the 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 Default 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 file copying

# Simple copy of files to the root of the generated directory
static = ["LICENSE.txt", "build.rs"]

Advanced configuration

static = [
    # Simple copy (copies to root with original filename)
    "README.md",
    
    # Rename file during copy
    { path = "config.template.toml", destination = "config.toml" },
    
    # Place file in subdirectory
    { path = "assets/logo.png", destination = "static/images/logo.png" },
    
    # Hard link instead of copy (saves disk space for large files)
    { path = "large_asset.bin", hard-link = true },
    
    # Combine renaming with hard linking
    { path = "data.json", destination = "resources/app_data.json", hard-link = true }
]

Configuration options

• path: Source file path (required)
• destination: Target path within the generated directory (optional)
  • If not specified, uses the original filename in the root directory
  • Can include subdirectories which will be created automatically
• hard-link: Create a hard link instead of copying (optional, default: false)
  • Useful for large files to save disk space
  • Both source and destination must be on the same filesystem

Examples

static = [
    # Copy LICENSE to root as-is
    "LICENSE",
    
    # Rename during copy
    { path = "template.env", destination = ".env.example" },
    
    # Organize into subdirectories
    { path = "docs/api.md", destination = "documentation/api.md" },
    { path = "scripts/build.sh", destination = "tools/build.sh" }
]

When using the detailed configuration format, Clorinde will automatically create any necessary parent directories for the destination path.

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 {
    // ...
}
}

Custom attributes

You can add custom attributes to generated query functions using the --# syntax. This allows you to add deprecation warnings, conditional compilation, or any other Rust attributes.

--! authors_from_country
--# deprecated = "Use authors_from_country_v2 instead"
--# allow(dead_code)
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;

This will generate:

#![allow(unused)]
fn main() {
#[deprecated = "Use authors_from_country_v2 instead"]
#[allow(dead_code)]
pub fn authors_from_country() -> AuthorsFromCountryStmt {
    // ...
}
}

Type annotations

Type annotations allow you to customise 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;

Custom attributes

You can add custom attributes to generated structs using the --# syntax. This allows you to add documentation, conditional compilation directives, or any other Rust attributes.

--: Author(age?) : Default, serde::Deserialize
--# doc = "Represents an author in the system"
--# cfg_attr(feature = "graphql", derive(async_graphql::SimpleObject))

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

This will generate:

#![allow(unused)]
fn main() {
#[derive(Default, serde::Deserialize, Debug, Clone, PartialEq)]
#[doc = "Represents an author in the system"]
#[cfg_attr(feature = "graphql", derive(async_graphql::SimpleObject))]
pub struct Author {
    pub name: String,
    pub age: Option<i32>,
}
}

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.

Query preparation

Clorinde provides two ways to execute queries, optimised for different use cases:

Default behaviour with bind()

The standard way to execute a query is to call bind() directly:

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

This approach intelligently handles statement preparation:

• If your client supports statement caching (like deadpool-postgres), it will automatically cache-prepare the statement
• If not, it executes without preparation to avoid unnecessary latency for one-off queries

This is the recommended approach for most applications, especially when using connection pools such as deadpool-postgres.

Explicit preparation with prepare()

For queries that will be executed multiple times with different parameters, you can explicitly prepare the statement:

#![allow(unused)]
fn main() {
let prepared = authors().prepare(&client).await?;

// Reuse the prepared statement multiple times
for country in ["Greece", "Italy", "Spain"] {
    let results = prepared.bind(&client, Some(country)).all().await?;
    // Process results...
}
}

This provides the best performance when:

• You're executing the same query repeatedly in a loop
• You're not using a connection pool with built-in statement caching
• You want to ensure the statement is prepared once and reused

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.