# Databases > Provisioning, migrating, querying 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](https://docs.rs/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: ```rust -- todo/src/db.rs -- use encore::storage::postgres::Database; pub static DB: Database = Database::new("todo").migrations("./migrations"); ``` ```sql -- 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 `_.up.sql`: - `` is a number. Migrations are applied in order of it, lowest first. Leading zeros are allowed (`0001_create_table.up.sql`). - `` is anything that says what it does. - Files with any other name are ignored. A `_.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. ```sql -- 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: ```shell 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: ```shell 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: ```rust use encore::storage::postgres::Database; static DB: Database = Database::new("todo").migrations("./migrations"); async fn add(title: &str) -> Result { 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, 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 { let updated = DB .execute("UPDATE todo_item SET done = true WHERE id = $1", &[&id]) .await?; Ok(updated == 1) } ``` | Method | Returns | |---|---| | `query(sql, params)` | Every row, as a `Vec`. | | `query_one(sql, params)` | Exactly one `Row`, or an error. | | `query_opt(sql, params)` | `Option`: an error if there is more than one row. | | `query_raw(sql, params)` | A `RowStream`, with the parameters as any iterator. | | `query_typed(sql, params)` | Like `query`, with each parameter's Postgres type given, so the query needs no round trip to prepare. | | `query_typed_raw(sql, params)` | The streaming form of `query_typed`. | | `execute(sql, params)` | The number of rows the statement changed. | | `execute_raw(sql, params)` | Like `execute`, with the parameters as any iterator. | | `simple_query(sql)` | Runs one or more statements with the simple query protocol, returning every value as text. | | `batch_execute(sql)` | Runs one or more statements with the simple query protocol, stopping at the first error. | `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](#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. ```rust 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 = 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`: ```rust 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`, `Option` for `NULL`, and slices and `Vec`s 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`: ```rust 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> { self.0.to_sql(ty, out) } fn accepts(ty: &Type) -> bool { ::accepts(ty) } to_sql_checked!(); } impl<'a> FromSql<'a> for Celsius { fn from_sql(ty: &Type, raw: &'a [u8]) -> Result> { f64::from_sql(ty, raw).map(Celsius) } fn accepts(ty: &Type) -> bool { ::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: ```rust 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()`: ```rust 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`: ```rust 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: | Method | Privileges recorded for the service | |---|---| | `DB.read()` | Read. May use a read replica. | | `DB.read_primary()` | Read, always on the primary. | | `DB.write()` | Read and write. | | `DB.admin()` | Read, write and admin. | | a query method on `DB` itself | Read and write. | 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: ```rust 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 { let row = db.query_one("SELECT count(*) FROM todo_item WHERE NOT done", &[]).await?; Ok(row.get(0)) } async fn report() -> Result { open_count(&REPORTS).await } async fn report_in_transaction() -> Result { 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. > **Note** > > The privileges are what Encore records for the service. At runtime every handle currently shares the database's one read-write connection pool: `read()` doesn't yet route to a read replica or open a read-only connection. ## 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: ```rust -- 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 { 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](https://github.com/launchbadge/sqlx), [Diesel](https://diesel.rs) or [SeaORM](https://www.sea-ql.org/SeaORM/): ```rust use encore::storage::postgres::Database; use sqlx::postgres::PgPool; static DB: Database = Database::new("todo").migrations("./migrations"); async fn pool() -> Result> { 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](#migrations) and point `.migrations` at the directory. ## Errors The methods return `encore::Error`, which has a `code` and a `message`: | Situation | `code` | |---|---| | An error from the database or the connection, including constraint violations. | `ErrCode::Unavailable` | | `query_one` found no rows. | `ErrCode::NotFound` | | `query_one` or `query_opt` found more than one row. | `ErrCode::FailedPrecondition` | | `Row::try_get` couldn't read the column. | `ErrCode::Internal` | 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: ```rust 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: ```shell encore exec cargo run --example seed ``` For tests, use [fixtures](/docs/rs/develop/testing#fixtures): a saved state each test starts from. ## Connecting to databases The CLI connects to the app's local databases: | Command | What it does | |---|---| | `encore db shell --database todo` | Opens a `psql` shell. | | `encore db conn-uri --database todo` | Prints a connection URI. | | `encore db proxy` | Runs a local proxy, so any Postgres client can reach the databases at `postgresql://localhost:/`. | | `encore db query --database todo "SELECT …"` | Runs one query and prints the result. | | `encore db list` | Lists the databases the app declares. | | `encore db version --database todo` | Prints the schema version. | | `encore db migrate --database todo` | Migrates with `--up`, `--down`, `--to N` or `--set N`. | | `encore db reset --database todo` | Drops the database and creates it again from its migrations. `--all` resets every one. | See the [CLI reference](/docs/cli/cli-reference) for the options.