Get started with PostgreSQL

The same five-minute path as SQLite, with a server in place of a file. PostgreSQL adds RETURNING on every write, $n placeholders, and identity columns instead of AUTOINCREMENT.

#Add the crates

[dependencies]
besu = "0.1"
besu-postgres = "0.1"
tokio = { version = "1", features = ["rt-multi-thread", "macros"] }

besu enables derive and migrate by default. besu-postgres enables sqlx and runtime-tokio; the DDL and the dialect work without either.

The database has to exist before you connect. Besu creates tables, indexes and constraints, but it never runs CREATE DATABASE or CREATE SCHEMA.

#Define a table

use besu::prelude::*;

#[derive(Table, Debug, Clone, PartialEq)]
#[besu(table = "users")]
pub struct User {
    /// `GENERATED BY DEFAULT AS IDENTITY` on PostgreSQL.
    #[besu(primary_key, auto_increment)]
    pub id: i64,
    #[besu(unique)]
    pub email: String,
    pub name: String,
    pub age: i32,
    pub bio: Option<String>,
}

The derive emits the row type, a users handle, a UserInsert struct and a &'static TableDef. See schema declaration for the rest.

#Connect

use besu_postgres::Sqlx;

let url = std::env::var("DATABASE_URL")
    .unwrap_or_else(|_| "postgres://besu:besu@localhost:5432/besu".to_string());
let db = besu::Database::new(Sqlx::connect(&url).await?);

Sqlx::new is an alias for Sqlx::connect, for symmetry with the other drivers. Both accept a libpq-style URL, so TLS and application-name settings travel in the query string. See connecting to PostgreSQL for pool configuration.

#Create the table

push introspects, diffs against the struct, and applies only what is missing.

db.push([users.def()]).await?;

It sends one statement (reformatted here):

CREATE TABLE "users" (
    "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "email" TEXT NOT NULL UNIQUE,
    "name" TEXT NOT NULL,
    "age" INTEGER NOT NULL,
    "bio" TEXT
)

push refuses anything that would lose data. In a development loop where columns come and go, use push_destructive, which is the explicit opt-in.

#Insert

let oscar = db
    .insert(users)
    .value(users.insert("oscar@example.com", "Oscar", 21))
    .returning(users)
    .await?
    .remove(0);

assert_eq!(oscar.name, "Oscar");

users.insert(..) takes exactly the columns the database cannot fill in: not the generated id, not the nullable bio, and not a column with a default.

INSERT INTO "users" ("email", "name", "age") VALUES ($1, $2, $3)

.returning(users) appends RETURNING with the whole column list, which is how the generated id comes back without a second query.

#Select

let adults: Vec<User> = db
    .select(users)
    .filter(users.age.gte(18))
    .order_by(users.name.asc())
    .limit(50)
    .await?;

SELECT "users"."id", "users"."email", "users"."name", "users"."age", "users"."bio"
FROM "users"
WHERE ("users"."age" >= $1)
ORDER BY "users"."name" ASC
LIMIT $2
/* besu:ii */

The trailing comment is part of the statement the driver sends. It records the shape of the bound parameters, because PostgreSQL fixes a parameter's type at prepare time and sqlx caches prepared statements by SQL text alone. Two statements with the same text and different parameter types would otherwise share a cache entry with the wrong types.

#Check the SQL without a database

let db = besu::dry::<besu_postgres::Postgres>();

let sql = db
    .select(users)
    .filter(users.age.gte(18))
    .to_sql()?;

assert!(sql.sql().contains(r#"WHERE ("users"."age" >= $1)"#));

dry gives you the full typed builder and the exact SQL with no connection, which is also how the builder compiles for wasm32. Awaiting a query built this way returns an error.

#Next steps

• Schema declaration — every #[besu(...)] attribute.
• PostgreSQL column types — NUMERIC, JSONB, UUID.
• Indexes and constraints — partial indexes and checks.
• Connecting to PostgreSQL — pools and transactions.
• Get started with SQLite or MySQL.

#Gotchas

• Every identifier is quoted. PostgreSQL folds an unquoted name to lower case, so a table declared #[besu(table = "Users")] becomes "Users" and no longer matches a table you created as users.
• push is not transactional: statements run one at a time, and a failure part-way leaves the earlier ones applied. Use besu generate plus besu migrate for anything that matters.
• A schema named with #[besu(schema = "app")] has to exist already. Table identity is (schema, name), so app.users and users are two different tables.
• An unsigned column is not an unsigned type. u32 is a BIGINT and u64 is a NUMERIC(20,0); the range check happens in Rust, and the database will happily accept a negative number you wrote by hand.
• Renaming a column is a drop plus an add unless you declare #[besu(renamed_from = "old_name")], in which case push emits one ALTER TABLE ... RENAME COLUMN and the data survives.
• The /* besu:... */ suffix is part of the SQL. An assertion that compares the whole string has to include it.