Introduction

Cornucopia 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 Cornucopia to generate Rust code.
3. Use the generated code in your project.

Compared to other Rust database interfaces, Cornucopia'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 Cornucopia 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

Cornucopia

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

CLI

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

cargo install cornucopia

API

Import cornucopia in your project's Cargo.toml:

cornucopia = "..." # 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 Cornucopia 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.

Dependencies

The code generated by Cornucopia has a few dependencies that you need to import into your project's Cargo.toml.

This section describes the role of each dependency. Dependencies come in three broad categories:

• Common required ones.
• Choosing between sync and async.
• Optional dependencies that extend Cornucopia's capabilities.

Required

• Postgres type utils: postgres-types with the derive feature enabled.

Choose one (sync or async)

Sync

• Client: cornucopia_sync.
• Driver: postgres.

Async

• Client: cornucopia_async.
• Runtime: tokio.
• Driver: tokio_postgres.
• Async tools: futures.

(Optional) Async connection pooling

Requires cornucopia_async's deadpool feature (enabled by default)

• Connection pool deadpool-postgres.

(Optional) Extra types

You can enable additional support for additional PostgreSQL types by adding the corresponding crates and driver features.

(*) In addition to the driver feature, the with-serde_json-1 feature must also be enabled on the Cornucopia client.

(**) Doesn't require any driver feature, but it does require enabling rust_decimal's db-postgres feature.

(Optional) Row serialization

• serde with the derive feature enabled.

Full dependencies

The code block below shows what your dependencies might look like with every feature that async cornucopia supports enabled:

# Cargo.toml
[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"] }

If you're generating sync code, your dependencies will look a bit different but this should give you a rough idea.

Supported types

Base types

(*) Optional extra types.

Custom types

Custom types like enums, composites and domains 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).

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

Array types

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

Using Cornucopia

You can use Cornucopia 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 Cornucopia as part of your own project.

Workflows

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

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 cornucopia.rs file in your src/ folder. Note that the CLI will require the path to one or more PostgreSQL schemas to build against.

You're done! Now you can import your generated query items from the cornucopia module. 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 Cornucopia'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 Cornucopia'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 Cornucopia manage an ephemeral database container for you.

schema: Automatic container management

The cornucopia 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 cornucopia live command to connect to an arbitrary live database. You will need to provide the connection URL.

Useful flags

sync

By default, Cornucopia 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

Cornucopia'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 Cornucopia'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.

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

Error type

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

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, Cornucopia 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, Cornucopia 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 Cornucopia must be annotated using simple SQL comments. These special comments are parsed by Cornucopia 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 Cornucopia query annotation, and authors_from_country is the name of the query.

Cornucopia 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 customize 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.

Type annotations

Type annotations allow you to customize the structs that Cornucopia 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.

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

Cornucopia 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 Cornucopia, it's time to use them. Hurray 🎉!

Let's say you have generated your Rust module into src/cornucopia.rs, then this is as simple as importing the items you need from it, like so:

mod cornucopia;

use cornucopia::authors;

Building the query object

Building a query object starts with either the query function:

authors().bind(&client, Some("Greece"));

or the generated parameter struct:

use cornucopia_async::Params;

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 Params trait from your (sync or async) cornucopia client.

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.

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:

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, Cornucopia uses umbrella traits that allow you to pass different concrete types to the same query.

For example:

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

(This trait is only available if the client crate has the with-serde_json-1 enabled)

• serde_json::Value
• postgres_types::Json

ArraySql

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

Notes on IterSql

This is a wrapper type available in the client crates. It 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 Cornucopia's API inside a build script to automatically rebuild your Rust queries when your PostgreSQL queries change.

How to contribute

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

You want to become a member of the project? Thank you! You can join our discord server to get in touch with our contributors.