Skip to main content

puerto_sqlx/
lib.rs

1//! The Postgres adapter every Puerto project with a database shares (ADR-0017): the connection
2//! pool, migrations at startup, the readiness ping and the one mapping from driver errors to
3//! [`ErrorRepositorio`].
4//!
5//! The migrations are the project's own: `sqlx::migrate!` embeds a directory at compile time, so
6//! the project declares its [`Migrator`] and hands it over. A generated project's `migrador`
7//! crate is
8//!
9//! ```ignore
10//! pub static MIGRADOR: sqlx::migrate::Migrator = sqlx::migrate!("../migraciones");
11//! ```
12//!
13//! and its repositories call `puerto_sqlx::mapear_error_sqlx`.
14//!
15//! A project with domain events (ADR-0018) also uses [`uow::TransaccionPostgres`] in its generated units
16//! of work, and its worker the outbox, the `db` transport and the dead-letter sink in [`eventos`].
17//! Their tables are [`MIGRACION_OUTBOX`], which `puerto new --eventos` writes into the project.
18//!
19//! A worker that sends mail (ADR-0020) keeps what it sent in [`correo::CorreosEnviadosPostgres`], over
20//! [`MIGRACION_CORREOS_ENVIADOS`].
21//!
22//! A project with local accounts (ADR-0021 § 2) keeps their passwords and sessions in
23//! [`auth::CredencialesPostgres`] and [`auth::SesionesPostgres`], over [`MIGRACION_USUARIOS`] and
24//! [`MIGRACION_AUTH`].
25
26pub mod auth;
27pub mod correo;
28pub mod eventos;
29pub mod uow;
30
31use puerto_nucleo::ErrorRepositorio;
32use sqlx::migrate::Migrator;
33use sqlx::postgres::{PgConnectOptions, PgConnection, PgPoolOptions};
34use sqlx::{Connection, PgPool};
35use std::time::Duration;
36
37/// The outbox, delivery and dead-letter tables (`migrations/outbox.sql`). Puerto's CLI ships a
38/// byte-for-byte copy of it as a project migration.
39pub const MIGRACION_OUTBOX: &str = include_str!("../migrations/outbox.sql");
40
41/// The sent-mail log (`migrations/sent_mails.sql`). Puerto's CLI ships a byte-for-byte copy of it
42/// as a project migration.
43pub const MIGRACION_CORREOS_ENVIADOS: &str = include_str!("../migrations/sent_mails.sql");
44
45/// The users every auth project has (`migrations/users.sql`): [`auth::SesionesPostgres`] reads it, so its
46/// schema is part of this crate's contract. Puerto's CLI ships a byte-for-byte copy of it as a
47/// project migration.
48pub const MIGRACION_USUARIOS: &str = include_str!("../migrations/users.sql");
49
50/// The credentials and sessions of local accounts (`migrations/auth.sql`), after
51/// [`MIGRACION_USUARIOS`]. Puerto's CLI ships a byte-for-byte copy of it as a project migration.
52pub const MIGRACION_AUTH: &str = include_str!("../migrations/auth.sql");
53
54/// Why the database could not be used at startup — read by the person starting the process, so
55/// it says where and why, never the password.
56#[derive(Debug, thiserror::Error)]
57#[non_exhaustive]
58pub enum ErrorBaseDatos {
59    /// `destino` is the URL's `host[:port]/database`: no user, no password.
60    #[error("no se puede conectar con la base de datos en {destino}: {causa}")]
61    ErrorConexion { destino: String, causa: String },
62    #[error("no se pueden aplicar las migraciones de la base de datos: {causa}")]
63    ErrorMigracion { causa: String },
64}
65
66pub async fn crear_pool_postgres(database_url: &str) -> Result<PgPool, ErrorBaseDatos> {
67    let failed = |e: sqlx::Error| ErrorBaseDatos::ErrorConexion {
68        destino: describe_url(database_url),
69        causa: e.to_string(),
70    };
71    let options: PgConnectOptions = database_url.parse().map_err(failed)?;
72    // One connection first, on its own: the pool retries a refused connection until its acquire
73    // timeout and then reports only that it timed out, not why.
74    let probe = PgConnection::connect_with(&options).await.map_err(failed)?;
75    let _ = probe.close().await;
76    Ok(PgPoolOptions::new()
77        .max_connections(5)
78        .acquire_timeout(Duration::from_secs(30))
79        .connect_lazy_with(options))
80}
81
82/// A connection URL as it can be shown: `host[:port]/database`, without the scheme, the
83/// credentials and the query. A string that is not a URL comes back as it is.
84fn describe_url(url: &str) -> String {
85    let rest = url.split_once("://").map_or(url, |(_, rest)| rest);
86    let rest = rest.rsplit_once('@').map_or(rest, |(_, rest)| rest);
87    rest.split('?').next().unwrap_or(rest).to_string()
88}
89
90/// Applies the project's pending migrations — `migrator` is its `sqlx::migrate!("./migrations")`.
91pub async fn ejecutar_migraciones(
92    pool: &PgPool,
93    migrator: &Migrator,
94) -> Result<(), ErrorBaseDatos> {
95    migrator
96        .run(pool)
97        .await
98        .map_err(|e| ErrorBaseDatos::ErrorMigracion {
99            causa: e.to_string(),
100        })
101}
102
103/// Whether the database answers a trivial query — what `/health/ready` reports.
104///
105/// Bounded by the pool's acquire timeout, so a database that is down makes the probe slow to
106/// answer rather than hang forever.
107pub async fn ping(pool: &PgPool) -> bool {
108    sqlx::query("SELECT 1").execute(pool).await.is_ok()
109}
110
111/// The one place that knows Postgres error codes: a driver error, as the repository port speaks it.
112/// The caller logs it first, with its own context. An entity with references converts with
113/// [`mapear_error_sqlx_con`] instead.
114pub fn mapear_error_sqlx(err: &sqlx::Error) -> ErrorRepositorio {
115    mapear_error_sqlx_con(err, &[])
116}
117
118/// A foreign key a repository writes through: the constraint's name in the migration and the field
119/// it checks (ADR-0022).
120#[derive(Debug, Clone, Copy, PartialEq, Eq)]
121pub struct ClaveForanea {
122    pub restriccion: &'static str,
123    pub campo: &'static str,
124}
125
126/// [`mapear_error_sqlx`], knowing the entity's foreign keys: a violation of one of them is
127/// [`ErrorRepositorio::ReferenciaInexistente`] with its field. One the list does not name is
128/// `Unavailable` — nothing can tell the caller which input was wrong.
129pub fn mapear_error_sqlx_con(err: &sqlx::Error, references: &[ClaveForanea]) -> ErrorRepositorio {
130    let Some(db_err) = err.as_database_error() else {
131        return ErrorRepositorio::NoDisponible;
132    };
133    match db_err.code().as_deref() {
134        // unique_violation: a `unique` column already taken.
135        Some("23505") => ErrorRepositorio::Duplicado,
136        // foreign_key_violation: a reference to a row that does not exist.
137        Some("23503") => db_err
138            .constraint()
139            .and_then(|name| references.iter().find(|fk| fk.restriccion == name))
140            .map_or(ErrorRepositorio::NoDisponible, |fk| {
141                ErrorRepositorio::ReferenciaInexistente(fk.campo)
142            }),
143        _ => ErrorRepositorio::NoDisponible,
144    }
145}
146
147#[cfg(test)]
148mod tests {
149    use super::*;
150
151    /// A pool that never reaches a server: nothing listens on port 1.
152    fn unreachable_pool() -> PgPool {
153        PgPoolOptions::new()
154            .acquire_timeout(Duration::from_millis(200))
155            .connect_lazy("postgres://puerto@127.0.0.1:1/puerto")
156            .unwrap()
157    }
158
159    /// A driver error carrying a Postgres SQLSTATE (and the constraint it broke), without a server.
160    #[derive(Debug)]
161    struct FakeDbError {
162        code: &'static str,
163        restriccion: Option<&'static str>,
164    }
165
166    impl std::fmt::Display for FakeDbError {
167        fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
168            write!(f, "SQLSTATE {}", self.code)
169        }
170    }
171
172    impl std::error::Error for FakeDbError {}
173
174    impl sqlx::error::DatabaseError for FakeDbError {
175        fn message(&self) -> &str {
176            self.code
177        }
178        fn code(&self) -> Option<std::borrow::Cow<'_, str>> {
179            Some(self.code.into())
180        }
181        fn constraint(&self) -> Option<&str> {
182            self.restriccion
183        }
184        fn as_error(&self) -> &(dyn std::error::Error + Send + Sync + 'static) {
185            self
186        }
187        fn as_error_mut(&mut self) -> &mut (dyn std::error::Error + Send + Sync + 'static) {
188            self
189        }
190        fn into_error(self: Box<Self>) -> Box<dyn std::error::Error + Send + Sync + 'static> {
191            self
192        }
193        fn kind(&self) -> sqlx::error::ErrorKind {
194            sqlx::error::ErrorKind::Other
195        }
196    }
197
198    fn database_error(code: &'static str) -> sqlx::Error {
199        sqlx::Error::Database(Box::new(FakeDbError {
200            code,
201            restriccion: None,
202        }))
203    }
204
205    fn constraint_error(code: &'static str, restriccion: &'static str) -> sqlx::Error {
206        sqlx::Error::Database(Box::new(FakeDbError {
207            code,
208            restriccion: Some(restriccion),
209        }))
210    }
211
212    const ORDER_REFERENCES: &[ClaveForanea] = &[
213        ClaveForanea {
214            restriccion: "orders_customer_id_fkey",
215            campo: "customer_id",
216        },
217        ClaveForanea {
218            restriccion: "orders_reviewer_id_fkey",
219            campo: "reviewer_id",
220        },
221    ];
222
223    #[test]
224    fn should_map_a_foreign_key_violation_to_the_missing_field() {
225        assert_eq!(
226            mapear_error_sqlx_con(
227                &constraint_error("23503", "orders_reviewer_id_fkey"),
228                ORDER_REFERENCES
229            ),
230            ErrorRepositorio::ReferenciaInexistente("reviewer_id")
231        );
232    }
233
234    #[test]
235    fn should_map_a_foreign_key_violation_on_an_unlisted_constraint_to_unavailable() {
236        assert_eq!(
237            mapear_error_sqlx_con(
238                &constraint_error("23503", "orders_warehouse_id_fkey"),
239                ORDER_REFERENCES
240            ),
241            ErrorRepositorio::NoDisponible
242        );
243    }
244
245    #[test]
246    fn should_map_a_unique_violation_to_duplicate_whatever_the_references() {
247        assert_eq!(
248            mapear_error_sqlx_con(
249                &constraint_error("23505", "orders_customer_id_fkey"),
250                ORDER_REFERENCES
251            ),
252            ErrorRepositorio::Duplicado
253        );
254    }
255
256    #[test]
257    fn should_map_a_non_database_error_to_unavailable_whatever_the_references() {
258        assert_eq!(
259            mapear_error_sqlx_con(&sqlx::Error::PoolTimedOut, ORDER_REFERENCES),
260            ErrorRepositorio::NoDisponible
261        );
262    }
263
264    #[test]
265    fn should_map_a_unique_violation_to_duplicate() {
266        assert_eq!(
267            mapear_error_sqlx(&database_error("23505")),
268            ErrorRepositorio::Duplicado
269        );
270    }
271
272    #[test]
273    fn should_map_another_database_error_to_unavailable() {
274        // 23503 = foreign_key_violation: a constraint, but not a taken unique value.
275        assert_eq!(
276            mapear_error_sqlx(&database_error("23503")),
277            ErrorRepositorio::NoDisponible
278        );
279    }
280
281    #[test]
282    fn should_map_a_non_database_error_to_unavailable() {
283        assert_eq!(
284            mapear_error_sqlx(&sqlx::Error::PoolTimedOut),
285            ErrorRepositorio::NoDisponible
286        );
287    }
288
289    #[tokio::test]
290    async fn should_report_not_ready_when_the_database_is_unreachable() {
291        assert!(!ping(&unreachable_pool()).await);
292    }
293
294    #[tokio::test]
295    async fn should_reject_a_database_url_that_is_not_one() {
296        let result = crear_pool_postgres("not a database url").await;
297
298        assert!(matches!(result, Err(ErrorBaseDatos::ErrorConexion { .. })));
299    }
300
301    #[tokio::test]
302    async fn should_say_where_and_why_the_connection_failed_without_the_password() {
303        // Arrange: nothing listens on port 1.
304        let url = "postgres://devuser:s3cret@127.0.0.1:1/shop";
305
306        // Act
307        let started = std::time::Instant::now();
308        let message = crear_pool_postgres(url).await.unwrap_err().to_string();
309
310        // Assert
311        assert!(message.contains("127.0.0.1:1/shop"), "{message}");
312        assert!(message.to_lowercase().contains("refused"), "{message}");
313        assert!(!message.contains("s3cret"), "{message}");
314        assert!(!message.contains("devuser"), "{message}");
315        // A database that is down is reported at once, not after the pool's acquire timeout.
316        assert!(
317            started.elapsed() < Duration::from_secs(5),
318            "{:?}",
319            started.elapsed()
320        );
321    }
322
323    #[test]
324    fn should_describe_a_database_url_by_its_host_and_database_only() {
325        assert_eq!(
326            describe_url("postgres://u:p@db.internal:5432/shop?sslmode=require"),
327            "db.internal:5432/shop"
328        );
329        assert_eq!(describe_url("postgres://localhost/shop"), "localhost/shop");
330        assert_eq!(describe_url("not a database url"), "not a database url");
331    }
332
333    // ── Against a real server: `make test/db` ─────────────────────────────
334
335    async fn test_database() -> Option<PgPool> {
336        let server = std::env::var("PUERTO_TEST_DATABASE_URL").ok()?;
337        Some(
338            crear_pool_postgres(&format!("{server}/postgres"))
339                .await
340                .unwrap(),
341        )
342    }
343
344    #[tokio::test]
345    #[ignore = "needs Postgres: make test/db"]
346    async fn postgres_unique_violation_maps_to_duplicate() {
347        let Some(pool) = test_database().await else {
348            eprintln!("PUERTO_TEST_DATABASE_URL not set — skipping (run `make test/db`)");
349            return;
350        };
351        let mut conn = pool.acquire().await.unwrap();
352        // A temporary table lives and dies with this connection: nothing to clean up.
353        sqlx::query("CREATE TEMPORARY TABLE skus (sku TEXT UNIQUE)")
354            .execute(&mut *conn)
355            .await
356            .unwrap();
357        sqlx::query("INSERT INTO skus VALUES ('A-1')")
358            .execute(&mut *conn)
359            .await
360            .unwrap();
361
362        let err = sqlx::query("INSERT INTO skus VALUES ('A-1')")
363            .execute(&mut *conn)
364            .await
365            .unwrap_err();
366
367        assert_eq!(mapear_error_sqlx(&err), ErrorRepositorio::Duplicado);
368    }
369
370    #[tokio::test]
371    #[ignore = "needs Postgres: make test/db"]
372    async fn postgres_foreign_key_violation_maps_to_the_missing_field() {
373        let Some(pool) = test_database().await else {
374            eprintln!("PUERTO_TEST_DATABASE_URL not set — skipping (run `make test/db`)");
375            return;
376        };
377        let mut conn = pool.acquire().await.unwrap();
378        sqlx::query("CREATE TEMPORARY TABLE customers (id UUID PRIMARY KEY)")
379            .execute(&mut *conn)
380            .await
381            .unwrap();
382        sqlx::query(
383            "CREATE TEMPORARY TABLE orders (customer_id UUID NOT NULL \
384             CONSTRAINT orders_customer_id_fkey REFERENCES customers (id))",
385        )
386        .execute(&mut *conn)
387        .await
388        .unwrap();
389
390        let err = sqlx::query("INSERT INTO orders VALUES (gen_random_uuid())")
391            .execute(&mut *conn)
392            .await
393            .unwrap_err();
394
395        assert_eq!(
396            mapear_error_sqlx_con(&err, ORDER_REFERENCES),
397            ErrorRepositorio::ReferenciaInexistente("customer_id")
398        );
399    }
400
401    #[tokio::test]
402    #[ignore = "needs Postgres: make test/db"]
403    async fn postgres_ping_reports_ready() {
404        let Some(pool) = test_database().await else {
405            eprintln!("PUERTO_TEST_DATABASE_URL not set — skipping (run `make test/db`)");
406            return;
407        };
408
409        assert!(ping(&pool).await);
410    }
411}