Databases

Encore.rs supports PostgreSQL databases. You declare a database as a static, write its schema as SQL migration files, and query it with methods modelled on tokio-postgres. Encore provisions the database, applies the migrations, and grants each service the access its code uses.

Creating a database

Declare a Database as a module-level static in a service, and point it at a directory of migrations:

todo/src/db.rs
use encore::storage::postgres::Database;

pub static DB: Database = Database::new("todo").migrations("./migrations");

todo/src/migrations/1_create_table.up.sql
CREATE TABLE todo_item (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false
);

encore run creates the database on a local Postgres server and applies the migrations before the service starts. Nothing else needs setting up.

The declaration has a fixed shape, because Encore reads it from the source:

• It is a static at module level, initialized by Database::new(…).
• The name is a string literal: lowercase letters, digits and underscores, starting with a letter and ending with a letter or digit, at most 63 characters, and unique in the app. Names starting with encore_ are reserved.
• The only option is .migrations("…"), chained onto new and set at most once. Its path is a string literal, relative to the directory of the file that declares the database. The directory must exist.

A database declared without .migrations has no schema of its own.

Migrations

A migration is a SQL file named <version>_<description>.up.sql:

• <version> is a number. Migrations are applied in order of it, lowest first. Leading zeros are allowed (0001_create_table.up.sql).
• <description> is anything that says what it does.
• Files with any other name are ignored. A <version>_<description>.down.sql file is the matching down migration, run only when you migrate down by hand.

/todo
├── encore.service.json
├── Cargo.toml
└── src
    ├── main.rs
    ├── db.rs
    └── migrations
        ├── 1_create_table.up.sql
        └── 2_add_created.up.sql

Encore records the version a database is at in its schema_migrations table, and applies only the migrations above it. Each migration file runs as one batch of statements.

Changing the schema

Change the schema by adding a migration with a higher version than any before it. Don't edit a migration that has been applied: Encore won't run it again.

todo/src/migrations/2_add_created.up.sql
ALTER TABLE todo_item ADD created TIMESTAMPTZ NOT NULL DEFAULT now();

A migration added while encore run is running shows as pending. Apply it with:

encore db migrate --database todo --up

When you deploy, migrations are applied before the new version of the service starts. Make sure the code that is running keeps working with the new schema while the deploy rolls out.

Handling migration errors

If a migration fails, its statements are rolled back (unless the file commits on its own) and the database is marked dirty at that version. Encore won't migrate a dirty database until you say where it stands. Fix the migration, set the recorded version back to the last migration that applied, and migrate again:

encore db version --database todo
encore db migrate --database todo --set 1
encore db migrate --database todo --up

encore db reset --database todo drops a local database and creates it again from its migrations, discarding its data.

Querying

The query methods have the names, parameters and meaning of tokio-postgres's Client methods. They return Result<_, encore::Error>. Parameters are written $1, $2, … in the SQL and passed as a slice of references:

use encore::storage::postgres::Database;

static DB: Database = Database::new("todo").migrations("./migrations");

async fn add(title: &str) -> Result<i64, encore::Error> {
    let row = DB
        .query_one(
            "INSERT INTO todo_item (title) VALUES ($1) RETURNING id",
            &[&title],
        )
        .await?;
    Ok(row.get("id"))
}

async fn open_titles() -> Result<Vec<String>, encore::Error> {
    let rows = DB
        .query("SELECT title FROM todo_item WHERE NOT done ORDER BY id", &[])
        .await?;
    Ok(rows.iter().map(|row| row.get("title")).collect())
}

async fn complete(id: i64) -> Result<bool, encore::Error> {
    let updated = DB
        .execute("UPDATE todo_item SET done = true WHERE id = $1", &[&id])
        .await?;
    Ok(updated == 1)
}

simple_query and batch_execute take no parameters: never build their SQL from untrusted input.

A query is a &str or String, or a prepared Statement (see Connections). Each query is traced.

Reading rows

Row::get reads a column by name or zero-based position, converting it to the type you ask for. It panics if the column doesn't exist or can't be converted; Row::try_get returns an error instead.

let row = DB.query_one("SELECT id, title, done FROM todo_item WHERE id = $1", &[&id]).await?;
let title: String = row.get("title");
let done: bool = row.get(2);
let note: Option<String> = row.try_get("note")?;

A NULL reads as None into an Option. Row also has columns(), len() and is_empty().

Streaming rows

query_raw returns the rows as a RowStream rather than collecting them. Read it with next(), or use it as a futures Stream:

let mut rows = DB
    .query_raw("SELECT title FROM todo_item WHERE id = ANY($1)", [ids])
    .await?;
while let Some(row) = rows.next().await {
    let title: String = row?.get("title");
    println!("{title}");
}

After the last row, rows_affected() returns the number of rows the statement affected.

Types

Parameters implement ToSql and columns are read through FromSql, both in encore::storage::postgres::types. They cover every type tokio-postgres converts: integers, floats, bool, &str and String, &[u8] and Vec<u8>, Option<T> for NULL, and slices and Vecs of them as arrays. The SDK also enables tokio-postgres's support for serde_json::Value, uuid::Uuid (uuid 1), the chrono 0.4 date and time types, cidr 0.3 and geo-types 0.7. Add the crate to your Cargo.toml to use its types.

To convert a type of your own, implement the traits from encore::storage::postgres::types:

use encore::storage::postgres::types::{to_sql_checked, BytesMut, FromSql, IsNull, ToSql, Type};

#[derive(Debug)]
struct Celsius(f64);

impl ToSql for Celsius {
    fn to_sql(
        &self,
        ty: &Type,
        out: &mut BytesMut,
    ) -> Result<IsNull, Box<dyn std::error::Error + Sync + Send>> {
        self.0.to_sql(ty, out)
    }

    fn accepts(ty: &Type) -> bool {
        <f64 as ToSql>::accepts(ty)
    }

    to_sql_checked!();
}

impl<'a> FromSql<'a> for Celsius {
    fn from_sql(ty: &Type, raw: &'a [u8]) -> Result<Self, Box<dyn std::error::Error + Sync + Send>> {
        f64::from_sql(ty, raw).map(Celsius)
    }

    fn accepts(ty: &Type) -> bool {
        <f64 as FromSql>::accepts(ty)
    }
}

Transactions

transaction() begins a transaction on a connection of its own from the pool. The Transaction has the same query methods, and rolls back when dropped unless you commit it, so an early return with ? can't leave one half done:

async fn move_items(from: i64, to: i64) -> Result<(), encore::Error> {
    let tx = DB.transaction().await?;
    tx.execute("UPDATE todo_item SET list_id = $2 WHERE list_id = $1", &[&from, &to])
        .await?;
    tx.execute("DELETE FROM todo_list WHERE id = $1", &[&from]).await?;
    tx.commit().await
}

rollback() rolls back explicitly. To set the transaction's isolation level, access mode or deferrability, use build_transaction():

use encore::storage::postgres::IsolationLevel;

let tx = DB
    .build_transaction()
    .isolation_level(IsolationLevel::Serializable)
    .read_only(true)
    .start()
    .await?;

Connections

The database is a pool of connections. What needs a single connection, prepared statements and COPY, is on a Connection from acquire(), or on a Transaction:

let conn = DB.acquire().await?;
let by_id = conn.prepare("SELECT title FROM todo_item WHERE id = $1").await?;
for id in [1i64, 2, 3] {
    let row = conn.query_one(&by_id, &[&id]).await?;
    println!("{}", row.get::<_, String>("title"));
}
conn.close().await;

• prepare(sql) and prepare_typed(sql, types) return a Statement, which can only be used on the connection that prepared it, or in a transaction on that connection.
• copy_in(sql) runs a COPY … FROM STDIN and returns a sink to write the data to; copy_out(sql) runs a COPY … TO STDOUT and returns a stream of it.
• transaction() and build_transaction() begin a transaction on the connection, which stays acquired after it ends.

The connection goes back to the pool when it is closed or dropped.

Passing the database to other code

A Database can't be passed around as a value: Encore reads which service uses it, and how, from the calls made on the static. To hand it to other code, such as a repository struct or a library, make a DatabaseRef with the privileges that code needs:

A DatabaseRef is Copy, and the methods are const, so a handle can also be a static. Library code takes the Queryable trait, which a DatabaseRef, a Transaction and a Connection implement, so the same code runs inside a transaction:

use encore::storage::postgres::{Database, DatabaseRef, Queryable};

static DB: Database = Database::new("todo").migrations("./migrations");
static REPORTS: DatabaseRef = DB.read();

async fn open_count(db: &impl Queryable) -> Result<i64, encore::Error> {
    let row = db.query_one("SELECT count(*) FROM todo_item WHERE NOT done", &[]).await?;
    Ok(row.get(0))
}

async fn report() -> Result<i64, encore::Error> {
    open_count(&REPORTS).await
}

async fn report_in_transaction() -> Result<i64, encore::Error> {
    let tx = DB.transaction().await?;
    let open = open_count(&tx).await?;
    tx.commit().await?;
    Ok(open)
}

Database doesn't implement Queryable, so open_count(&DB) doesn't compile.

Using another service's database

By default a database is used by the service that declares it, but any service can use it. Declare a second static with Database::named and the database's name:

report/src/main.rs
use encore::storage::postgres::Database;

// The todo service's database.
static TODO_DB: Database = Database::named("todo");

async fn completed() -> Result<i64, encore::Error> {
    let row = TODO_DB
        .query_one("SELECT count(*) FROM todo_item WHERE done", &[])
        .await?;
    Ok(row.get(0))
}

A named database takes no options: it is configured where it is declared. The name must belong to a database declared somewhere in the app, in any language. Encore grants the report service access from the calls it makes, as for the declaring service.

Using an ORM or another client

connection_string() returns a connection string for the database, for clients other than the SDK's, such as sqlx, Diesel or SeaORM:

use encore::storage::postgres::Database;
use sqlx::postgres::PgPool;

static DB: Database = Database::new("todo").migrations("./migrations");

async fn pool() -> Result<PgPool, Box<dyn std::error::Error>> {
    Ok(PgPool::connect(&DB.connection_string()?).await?)
}

Calling it records read and write access for the service. Under encore test it returns the connection string of the test's own database.

Keep the schema in Encore's migration files. An ORM or migration tool that generates plain SQL migration files works: name them as above and point .migrations at the directory.

Errors

The methods return encore::Error, which has a code and a message:

The Postgres error, such as a duplicate key, is in the message. The SDK doesn't yet expose its SQLSTATE code.

Seeding data

To have data in every environment, such as lookup tables, insert it in a migration.

To have data only while developing, insert it on startup when the app runs locally. Make the insert idempotent, since it runs on every start:

use encore::env::CloudProvider;

async fn seed() -> Result<(), encore::Error> {
    if matches!(encore::app_meta().environment.cloud, CloudProvider::Local) {
        DB.batch_execute(include_str!("../dev_data.sql")).await?;
    }
    Ok(())
}

encore exec runs a command with the Encore runtime attached to the app's local infrastructure. A one-off seed program can be a Cargo example in the service's package, such as examples/seed.rs, which keeps the package at the one binary a service builds:

encore exec cargo run --example seed

For tests, use fixtures: a saved state each test starts from.

Connecting to databases

The CLI connects to the app's local databases:

See the CLI reference for the options.