stado-database

A product asks Stado for its database's placement and credential coordinate instead of hard-coding a provider. connect uses SeaORM for PostgreSQL, MySQL and a fleet SQLite file; credentials gives products using other engines the URL and certificate authority for their own driver. The declaration and consumer grant use the same commands for every engine.

[dependencies]
stado-database = { git = "https://github.com/wisent-ai/stado", package = "stado-database" }
sea-orm = { version = "1.1", default-features = false, features = ["sqlx-postgres", "sqlx-mysql", "sqlx-sqlite", "runtime-tokio-rustls", "macros"] }

let database = stado_database::FleetDatabase::for_product("handtohuman", "HANDTOHUMAN_FLEET_HOME")?;
let connection: sea_orm::DatabaseConnection = stado_database::connect(&database).await?;

Tables are SeaORM entities in the product; the schema changes through a sea-orm-migration migrator the product runs at startup.

Synchronous code

A command-line tool, or a service whose row operations are plain functions, uses stado_database::sync::Client instead of writing a client of its own:

use stado_database::{params, sync::{Client, OptionalExtension}};

let client = Client::connect(&stado_database::FleetDatabase::for_product("grant-cli", "GRANT_FLEET_HOME")?)?;
client.execute("UPDATE sources SET last_synced_at = $1 WHERE id = $2", params![now, id])?;
let name: Option<String> = client
    .query_row("SELECT name FROM sources WHERE id = $1", [id], |row| row.get("name"))
    .optional()?;
let transaction = client.transaction()?;
transaction.execute("DELETE FROM watches WHERE id = $1", [id])?;
transaction.commit()?; // dropped without commit, it rolls back

The client runs the SeaORM connection on a runtime of its own and waits for each statement; on a multi-threaded Tokio worker it leaves the worker for the wait. Error::is_unique_violation says an insert hit a unique constraint, and Row::json gives a whole row as a JSON object.

A product whose tables are SeaORM entities but whose callers are synchronous hands its entity work to the same client with run, and runs its migrator the same way; it keeps no runtime or connection thread of its own:

let client = Client::connect(&FleetDatabase::for_product("jeden", "JEDEN_STADO_HOME")?)?;
client.run(|db| async move { Migrator::up(&db, None).await })??;
let row = client.run(move |db| async move { value::Entity::find_by_id(key).one(&db).await })??;

run answers the closure's own result inside sync::Result; its only error of its own is a task that stopped before answering.

What connect does

1. stado database resolve <product> --consumer <product> --json names the Skarbiec item holding the database (<product>-database).
2. stado service directory connect skarbiec --consumer <product> --json names the Skarbiec route on this host.
3. stado credentials get <item> --field pooler_url and --field ca_certificate, run as consumer <product>-database-client with the bearer in ~/.stado/<product>-database-client-skarbiec-token, give the database URL and the certificate authority it is verified against. A sqlite:// URL names a file, which has no server to verify, so no certificate is read. For Postgres it also reads --field session_url.
4. The pool connects with sslmode=verify-full against that certificate (mysql:// URLs with VerifyIdentity). Postgres is opened on session_url, not pooler_url.

Why Postgres opens on session_url

A Supabase database's pooler_url is its pooler in transaction mode: each transaction may run on another server connection. SeaORM sends every statement through sqlx as a named prepared statement (sqlx_s_1, sqlx_s_2, …), and on a transaction-mode pooler the next client finds that name already taken: prepared statement "sqlx_s_1" already exists. session_url is the same pooler in session mode (Supabase's session port), where one client connection keeps one server connection, so a statement's name stays its own. A fleet Postgres database and an external one are reached directly, so their session_url is the server's own URL. stado database create writes it for every provider; stado database adopt writes it into an existing Supabase item, and create again writes it into an existing fleet or external one. pooler_url stays the transaction-mode route for a service that delivers it as DATABASE_URL.

connect opens the three engines SeaORM speaks: postgres:// or postgresql://, mysql://, and sqlite://<file> for a fleet SQLite database. A SQLite file is opened as it is, never created, so a consumer on a host that does not hold it is refused at step connect with the file named. A database of any other engine (MongoDB, Redis, SQL Server, ...) is refused by connect at step read pooler_url with its scheme named; the product calls stado_database::credentials, which does steps 1 to 3 and answers item, pooler_url and ca_certificate, and opens the server with that engine's own driver, verified against the certificate authority.

On a machine without Stado or Skarbiec, set <PRODUCT>_DATABASE_URL (the product name upper case, dashes as underscores) and <PRODUCT>_DATABASE_CA_FILE, the PEM bundle the server is verified against; a sqlite:// URL needs no certificate file. connect then uses them and runs none of the steps above. A server URL without its certificate file, or a file that cannot be read, is refused with the variable named.

Creating a database

stado database create places PostgreSQL or SQLite on a fleet host, can provision PostgreSQL through Supabase, or adopts an existing server of any engine through --provider external. The default is fleet PostgreSQL, so the operator need not choose a vendor:

stado database create <product> --consumer <product>

flag	default	what it chooses
--engine	postgres (fleet, supabase); the URL's engine (external)	postgres or sqlite (fleet), postgres (supabase); external: any engine, named by the connection URL, which --engine only confirms
--provider	fleet	fleet: Stado runs the database itself on a fleet host, no vendor and no bill; supabase: a hosted Postgres project; external: a server of any engine the user already runs anywhere (Postgres, MySQL, MongoDB, Redis, SQL Server, ...; RDS, Cloud SQL, Neon, PlanetScale, Atlas, Azure, their own server)
--ca-certificate	none	external only: PEM bundle of the server's certificate authority; refused without it
--host	the vault owner	fleet host the database is placed on
--port	none; required for a fleet Postgres	port a fleet Postgres listens on; Stado assumes none
--anchor	the one organization and region every project the Supabase token sees shares	supabase only: declared database whose organization and region the project joins; a token that sees projects in several is refused with each named
--accept-monthly-usd	none	supabase only: the monthly compute cost accepted; refused without it

The Supabase commands (create --provider supabase, adopt, destroy) call the management API with field value of the vault item that plays role supabase-management. A fleet where no item plays it is refused with the two commands that give it: stado credentials item retag --host <owner> <ITEM> --tags stado:role:supabase-management and stado credentials grant add --host <owner> --role supabase-management --field value --token-file <bearer file> <consumer>.

create --provider supabase reuses a project of the same name, so it reads the token's projects first; an answer to GET /projects that is not a project list is refused (whether <name> exists is unknown; nothing was created) rather than read as no projects, which would create a second, billed one. adopt refuses the same answer, a key listing that is not a list, a key that comes back without its value, and a project listed without its ref, so no item is written with an empty key or from an empty project path. When Supabase answers a project's pooler without its user or host, the item gets no pooler_url or session_url and carries pooler_unreported with the reason instead; the password create generated is kept, and stado database adopt reads the pooler again.

A fleet database is placed by stado database place <name> [--engine] --port <port> (the port only for Postgres), which create runs on the chosen host through the host channel. place initialises the data under ~/.stado/databases/<name>/ on that host and writes the credential item <name>-database into the owner vault. For Postgres it runs initdb with a generated password, issues a certificate authority of the database's own and a server certificate it signs for the host's routable address and loopback, accepts only TLS connections with a password, stores pooler_url and session_url (both the server's URL), ca_certificate, db_user and db_password on the item, and installs the managed unit <name>-database that runs postgres on that data. For SQLite it creates the file and stores host, path and pooler_url (sqlite://<file>); connect opens it for a consumer on that host. A database already placed is reported as reused and never initialised twice. Every provider declares the database for its consumers the same way.

An existing server of any engine is brought in with --provider external. The connection URL is read from standard input, so it never appears in the process table or shell history:

stado database create <product> --consumer <product> --provider external \
  --ca-certificate <server-ca.pem> < <file holding the connection URL>

The URL's scheme names the engine: postgres:// or postgresql:// is Postgres, mysql:// MySQL, mongodb:// or mongodb+srv:// MongoDB, redis:// or rediss:// Redis, mssql:// SQL Server, and so on for any engine. --engine, when given, must agree with it. The item <name>-database then holds pooler_url (that URL; a Postgres one also as session_url), ca_certificate (the bundle), host, engine and provider: external, the same fields a consumer reads from any other database. Running it again re-points the item at the URL given. The stado-database crate opens it like any other: connect opens Postgres and MySQL (a mysql:// URL as a MySQL pool verified with VerifyIdentity, and the synchronous Client binds parameters in the connection's own dialect), and credentials hands any other engine's URL and certificate authority to that engine's own driver.

Refusals name what failed: --anchor or --accept-monthly-usd with --provider fleet (a fleet database has no vendor bill), an --engine other than postgres with --provider supabase (supabase runs Postgres only), --host or --port with --provider supabase, --host, --port, --anchor or --accept-monthly-usd with --provider external (the server is already placed), --provider external without --ca-certificate, a certificate file that holds no PEM certificate, empty standard input, a URL with no host, a URL whose engine differs from --engine, --ca-certificate with another provider, a sqlite engine with --provider external, any engine other than postgres or sqlite with --provider fleet (named with --provider external as the way in), an unknown provider, an engine name that is not a lowercase URL scheme, a fleet Postgres without --port (refused before anything is initialised), a host without initdb, postgres or openssl on its PATH (the PATH is printed), a host with no routable address, an initialised data directory whose unit is missing (the exact stado service deploy line that installs it is printed), and each failed initdb or openssl step with its own error output.

Fleet PostgreSQL certificate policy

A new PostgreSQL placement requires the placement host to declare database.postgres_tls, the certificate validity and key size its authority and server certificate are issued with: Fleet PostgreSQL certificate policy.

Stado Desktop

Databases → Create… runs the same stado database create. It offers the provider (fleet, supabase or external) and an optional engine; left empty, the engine is postgres on fleet and supabase and the connection URL's own engine on external. Fleet adds an optional host (empty: the vault owner); supabase adds the accepted monthly cost, and with it empty the button reads Read cost and the CLI's sentence naming what one more project adds to the bill appears above the form; external adds the connection URL, which goes to the CLI's standard input and never into its arguments, and the path of the server's CA certificate. Declare… takes any engine name. A row's menu holds Remove…, which withdraws the declaration, and Destroy…, the inverse of Create…: Destroy runs stado database destroy, and Destroy and delete its Supabase project adds --delete-project. Every refusal is the CLI's own sentence under The database plane refused.

Provisioning a product once

stado database create <product> --consumer <product>
stado credentials token mint <product>-database-client --host <vault owner> \
  --capabilities 'read:<product>-database#pooler_url,read:<product>-database#ca_certificate,read:<product>-database#session_url' \
  --audience <product>-database-client --token-file-name <product>-database-client-skarbiec-token
stado credentials token sync <product>-database-client --from-host <vault owner> --host <this host> \
  --source-token-file <owner home>/.stado/<product>-database-client-skarbiec-token \
  --token-file ~/.stado/<product>-database-client-skarbiec-token --shared-vault
stado service directory consumer add skarbiec <product> --target <this host>

A client minted before session_url existed may read only pooler_url and ca_certificate, and connect is refused at read credential field with Skarbiec's 403. stado database client <product> widens that client's grant on the vault owner to every field the crate reads for the database's engine (pooler_url; ca_certificate for a server; session_url for Postgres). It keeps the bearer, so the copies other hosts hold keep working, and running it again changes nothing. A client the vault owner holds no bearer for is refused with Skarbiec's answer. Stado Desktop's database row menu runs it as Grant library client reads.

Refusals

Every failure is the fleet database could not be reached at step <step>: <detail>, where the step is one of locate Stado, resolve database, resolve Skarbiec route, read credential field, read pooler_url, read session_url or connect, and the detail carries Stado's own answer. A Postgres item without session_url reads step read credential field: stado credentials get <product>-database --field session_url ... exited ...; a Postgres item carries session_url, the route that keeps one server session per connection: stado database adopt writes it for a Supabase database,stado database create for a fleet or external one. For example an undeclared database reads step resolve database: stado database resolve <product> ... exited 2: Error: unknown database "<product>"; declared: ..., a route the resolver has not bound yet reads step resolve Skarbiec route: ... did not answer at http://127.0.0.1:<port>, a SQLite database opened off its host reads step connect: opening <product> at <file> through <product>-database#pooler_url failed (a fleet SQLite file opens only on the host that holds it): ..., and connect on a MongoDB database reads step read pooler_url: <product>-database#pooler_url is a mongodb URL; connect opens postgres, mysql and sqlite through SeaORM, so open this engine with its own driver from stado_database::credentials.

Every database command

Every stado database command, what it does and how it refuses: Every database command.