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

Installation

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
SELECT 
    id, 
    email
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.

Updating build.rs

Create a crates/db/build.rs 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
    cornucopia();
}

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("cornucopia.rs");

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

    // Rerun this build script if the queries or migrations change.
    println!("cargo:rerun-if-changed={queries_path}");

    // Call cornucopia. Use whatever CLI command you need.
    let output = std::process::Command::new("cornucopia")
        .arg("-q")
        .arg(queries_path)
        .arg("--serialize")
        .arg("-d")
        .arg(&file_path)
        .arg("live")
        .arg(db_url)
        .output()
        .unwrap();

    // 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/lib.rs 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);
    deadpool_postgres::Pool::builder(manager).build().unwrap()
}

include!(concat!(env!("OUT_DIR"), "/cornucopia.rs"));

Folder Structure

You should now have a folder structure something like this.

.
├── .devcontainer/
│   └── ...
└── crates/
│         web-server/
│         │  └── main.rs
│         └── Cargo.toml
│         db/
│         ├── migrations
│         │   └── 20220330110026_user_tables.sql
│         ├── queries
│         │   └── users.sql
│         ├── src
│         │   └── lib.rs
│         └── build.rs
├── 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/lib.rs.

#[cfg(test)]
mod tests {
    use super::*;
    #[tokio::test]
    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()
            .bind(&client)
            .all()
            .await
            .unwrap();

        dbg!(users);
    }
}

Run the test

Run the below to trigger the unit test

cargo test -- --nocapture

The Results

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

running 1 test
[crates/db/src/lib.rs:56] 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.