Skip to content

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.

  1. Create it and add an entity.

    puerto nuevo --nombre tienda --db
    cd tienda
    puerto 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
  2. Install the tools, once.

    make setup # cargo-nextest, sqlx-cli, cargo-audit
  3. Migrate and prepare. Both start Postgres in Docker first (docker-compose.yml, postgres:16).

    make sqlx/migrate # apply the migrations
    make sqlx/prepare # write .sqlx/, the offline query cache
  4. Run it.

    make run

    On 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`
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:

migraciones/…_crear_tabla_producto.sql
-- 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:

migraciones/…_crear_tabla_pedido.sql (excerpt)
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.

puerto generar migracion anadir_stock_a_productos
output
✓ Migration 'anadir_stock_a_productos' created in migraciones/
Edit the file, then run: make sqlx/migrate && make sqlx/prepare

Puerto 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.

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 database
make sqlx/check # verify .sqlx/ matches the queries (for CI)

make sqlx/online and make sqlx/offline switch the mode for local work.

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.

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.

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