Database and migrations
puerto nuevo --db gives a project a Postgres database through SQLx:
checked SQL, written out in each repository, with no ORM in between. A project without --db
keeps its data in memory, which is good enough to try an idea and is lost on restart.
Starting a database project
Section titled “Starting a database project”-
Create it and add an entity.
puerto nuevo --nombre tienda --dbcd tiendapuerto generar scaffold Producto -- nombre:String precio:i64 sku:String!output (end) ✓ Migration 'crear_tabla_producto' created in migraciones/Schema pre-filled — run: make sqlx/migrate && make sqlx/prepare -
Install the tools, once.
make setup # cargo-nextest, sqlx-cli, cargo-audit -
Migrate and prepare. Both start Postgres in Docker first (
docker-compose.yml,postgres:16).make sqlx/migrate # apply the migrationsmake sqlx/prepare # write .sqlx/, the offline query cache -
Run it.
make runOn startup the API connects, runs pending migrations and serves. If the database is down it says so and exits, instead of panicking:
… — ¿está arrancada la base de datos? `make docker-compose/up`
What --db changes
Section titled “What --db changes”| In memory | --db |
|
|---|---|---|
| Repository | RepositorioProductoEnMemoria |
RepositorioProductoPostgres + fila.rs (the row, FilaProducto) |
| Migration | none | migraciones/…_crear_tabla_producto.sql, schema pre-filled from the fields |
/health/ready |
always ready | runs SELECT 1; 503 when Postgres does not answer |
| Tests | unit | unit + #[sqlx::test] integration tests per repository |
| Files | docker-compose.yml, .cargo/config.toml, DATABASE_URL in .env, migraciones/, the migrador crate |
The scaffold writes the whole table, so there is nothing to fill in:
-- Migración de Puerto: crear_tabla_producto-- Ejecuta `make sqlx/prepare` después de editar este fichero.
CREATE TABLE productos ( id UUID PRIMARY KEY, nombre TEXT NOT NULL, precio BIGINT NOT NULL, sku TEXT NOT NULL, creado_en TIMESTAMPTZ NOT NULL DEFAULT NOW(), actualizado_en TIMESTAMPTZ NOT NULL DEFAULT NOW(), borrado BOOLEAN NOT NULL DEFAULT FALSE, borrado_en TIMESTAMPTZ);
CREATE UNIQUE INDEX productos_sku_key ON productos (sku) WHERE borrado = false;Deletes are soft, so the unique index is partial: a deleted product does not keep its SKU taken (ADR-0003).
A reference is a foreign key. puerto generar scaffold Pedido -- cliente_id:ref:Cliente writes
the constraint and an index for the list’s filter:
cliente_id UUID NOT NULL CONSTRAINT pedidos_cliente_id_fkey REFERENCES clientes (id),-- …CREATE INDEX pedidos_cliente_id_idx ON pedidos (cliente_id);The database checks that the customer exists, not the use case (ADR-0022).
Every context’s migrations live in one directory, migraciones/ at the root, because they all
build one database. The migrador crate embeds them at compile time as
migrador::MIGRADOR (sqlx::migrate!("../migraciones")), and its build.rs tells Cargo to
rebuild when that directory changes, so a new migration is picked up even when no .rs file
changed. The API runs MIGRADOR at startup, and every Postgres test runs on it.
Writing a migration
Section titled “Writing a migration”puerto generar migracion anadir_stock_a_productos✓ Migration 'anadir_stock_a_productos' created in migraciones/ Edit the file, then run: make sqlx/migrate && make sqlx/preparePuerto writes the file itself (no sqlx CLI needed for this step) with a version that is unique
even within the same second. The command is refused in a project without --db.
The offline cache
Section titled “The offline cache”query_as! checks every query against the database at compile time. So that the project
builds without a running Postgres (in CI, on a fresh clone), .cargo/config.toml sets
SQLX_OFFLINE=true and SQLx reads the answers from .sqlx/. Commit that directory.
After changing a query or a migration:
make sqlx/prepare # regenerate .sqlx/ against the live databasemake sqlx/check # verify .sqlx/ matches the queries (for CI)make sqlx/online and make sqlx/offline switch the mode for local work.
Errors from the driver
Section titled “Errors from the driver”Every Postgres repository maps driver errors through one function, mapear_error_sqlx, from
puerto-sqlx: a unique violation becomes ErrorRepositorio::Duplicado (409), and anything
else NoDisponible. A row the domain rejects (stored data gone bad) is logged and reported as
DatosInvalidos, a 500, never a 422 that would blame the caller
(ADR-0015).
An entity with references maps through mapear_error_sqlx_con, which knows its foreign keys by
constraint name. A write that names a customer who does not exist violates
pedidos_cliente_id_fkey and becomes ErrorRepositorio::ReferenciaInexistente("cliente_id"),
which the entity’s error turns into pedido.validacion.cliente_id_no_encontrado, a 422 on that
field.
Integration tests
Section titled “Integration tests”Each Postgres repository comes with #[sqlx::test] tests: SQLx creates a fresh database per test,
applies the migrations of migrador::MIGRADOR, and drops it afterwards.
#[sqlx::test(migrator = "migrador::MIGRADOR")]async fn deberia_guardar_y_recuperar_por_id(pool: PgPool) { // Preparar let entidad = sembrar(&pool).await;
// Actuar let encontrado = repositorio_de_prueba(pool) .buscar_por_id(entidad.id) .await .unwrap() .unwrap();
// Comprobar assert_eq!(encontrado.id, entidad.id); // …}sembrar comes from infra_producto::pruebas::siembra, public behind the pruebas feature, so
the tests of a context that references Producto can seed one too. The tests need DATABASE_URL
and a running Postgres: make test/infraestructura, or make test with make docker-compose/up
first.
Housekeeping
Section titled “Housekeeping”| Command | Does |
|---|---|
make db/arrancar / make db/parar |
Start or stop the containers |
make db/reiniciar |
Drop everything and re-run the migrations. Asks you to type DESTRUIR |
make generar/migracion NOMBRE=… |
The same as puerto generar migracion |