Database Access

Cornucopia is a code generator that takes small snippets of SQL and turns them into Rust functions.

We'll turn our crates/db folder into a crate so we can keep all our database logic in one place.

Run the following

cargo init --lib crates/db


Install cornucopia into your project cd into your crates/db folder.

cd crates/db
cargo add [email protected]

Creating a SQL definition

In a folder called db/queries create a file called users.sql and add the following content.

--: User()

--! get_users : User
FROM users;

Cornucopia will use the above definition to generate a Rust function called get_users to access the database. Note cornucopia checks the query at code generation time against Postgres.


Create a crates/db/ file and add the following content. This file we compile our .sql files into rust code whenever they change.

use std::env;
use std::path::Path;

fn main() {
    // Compile our SQL

fn cornucopia() {
    // For the sake of simplicity, this example uses the defaults.
    let queries_path = "queries";

    let out_dir = env::var_os("OUT_DIR").unwrap();
    let file_path = Path::new(&out_dir).join("");

    let db_url = env::var_os("DATABASE_URL").unwrap();

    // Rerun this build script if the queries or migrations change.

    // Call cornucopia. Use whatever CLI command you need.
    let output = std::process::Command::new("cornucopia")

    // If Cornucopia couldn't run properly, try to display the error.
    if !output.status.success() {
        panic!("{}", &std::str::from_utf8(&output.stderr).unwrap());

Add a function to do connection pooling

First add some more crates, make sure you are in the crates/db folder.

cargo add [email protected]
cargo add [email protected]
cargo add [email protected]
cargo add tokio@1 --features macros,rt-multi-thread
cargo add [email protected]
cargo add serde@1 --features derive

Add the following code to crates/db/src/ will we use this to convert our DATABASE_URL env var into something cornucopia can use for connection pooling.

use std::str::FromStr;

pub use cornucopia_async::Params;
pub use deadpool_postgres::{Pool, PoolError, Transaction};
pub use tokio_postgres::Error as TokioPostgresError;
pub use queries::users::User;

pub fn create_pool(database_url: &str) -> deadpool_postgres::Pool {
    let config = tokio_postgres::Config::from_str(database_url).unwrap();
    let manager = deadpool_postgres::Manager::new(config, tokio_postgres::NoTls);

include!(concat!(env!("OUT_DIR"), "/"));

Folder Structure

You should now have a folder structure something like this.

├── .devcontainer/
   └── ...
└── crates/
         │  └──
         └── Cargo.toml
         ├── migrations
         │   └── 20220330110026_user_tables.sql
         ├── queries
         │   └── users.sql
         ├── src
         │   └──
├── Cargo.toml
└── Cargo.lock

Testing our database crate

Make sure everything builds.

cargo build

Add the following code to the bottom of your crates/db/src/

mod tests {
    use super::*;
    async fn load_users() {
        let db_url = std::env::var("DATABASE_URL").unwrap();
        let pool = create_pool(&db_url);

        let client = pool.get().await.unwrap();
        //let transaction = client.transaction().await.unwrap();

        let users = crate::queries::users::get_users()


Run the test

Run the below to trigger the unit test

cargo test -- --nocapture

The Results

Running unittests src/ (/workspace/target/debug/deps/db-1a59f4c51c8578ce)

running 1 test
[crates/db/src/] users = [
    User {
        id: 1,
        email: "[email protected]",
    User {
        id: 2,
        email: "[email protected]",
    User {
        id: 3,
        email: "[email protected]",

test tests::load_users ... ok

Great, from now on adding code to access the database is as simple as creating new sql files. The code generator will take care of the details.