Skip to main content

asyncq_postgres/
lib.rs

1//! PostgreSQL backend for asyncq.
2//!
3//! # Quick start
4//!
5//! ```rust,no_run
6//! use asyncq::{Queue, Worker, Job, Perform, JobContext, JobResult};
7//! use asyncq_postgres::PostgresBackend;
8//! use serde::{Serialize, Deserialize};
9//!
10//! #[derive(Job, Serialize, Deserialize)]
11//! #[job(queue = "emails", retries = 3)]
12//! struct SendEmail { to: String }
13//!
14//! impl Perform for SendEmail {
15//!     async fn perform(self, _ctx: JobContext) -> JobResult {
16//!         println!("Sending to {}", self.to);
17//!         Ok(())
18//!     }
19//! }
20//!
21//! #[tokio::main]
22//! async fn main() {
23//!     let backend = PostgresBackend::new("postgres://user:pass@localhost/mydb").await.unwrap();
24//!     backend.migrate().await.unwrap();
25//!
26//!     let queue = Queue::new(backend);
27//!     queue.enqueue(SendEmail { to: "user@example.com".into() }).await.unwrap();
28//!     Worker::new(queue).register::<SendEmail>().run().await;
29//! }
30//! ```
31//!
32//! # Transactional outbox
33//!
34//! PostgreSQL enables the transactional outbox pattern natively: enqueue a job
35//! in the same transaction as your business logic. The job only becomes visible
36//! to workers after `tx.commit()` — if the transaction rolls back, the job
37//! disappears with it.
38//!
39//! ```rust,ignore
40//! use asyncq_postgres::PostgresBackend;
41//! use chrono::Utc;
42//! use serde_json::json;
43//!
44//! async fn create_order(backend: &PostgresBackend, pool: &sqlx::PgPool) -> anyhow::Result<()> {
45//!     let mut tx = pool.begin().await?;
46//!
47//!     sqlx::query("INSERT INTO orders (status) VALUES ('pending')")
48//!         .execute(&mut *tx).await?;
49//!
50//!     // Enqueue in the same transaction
51//!     backend.enqueue_in_tx(
52//!         "order.created",          // job kind — must match a registered Job type
53//!         "orders",                 // queue name
54//!         serde_json::to_vec(&json!({"order_id": 1}))?,
55//!         3,                        // max_attempts
56//!         Utc::now(),               // scheduled_at (now = run immediately)
57//!         &mut tx,
58//!     ).await?;
59//!
60//!     tx.commit().await?;           // job visible to workers only after this
61//!     Ok(())
62//! }
63//! ```
64
65use std::time::Duration;
66use async_trait::async_trait;
67use chrono::{DateTime, Utc};
68use sqlx::{PgPool, postgres::PgPoolOptions, Row};
69use uuid::Uuid;
70
71use asyncq::{
72    backend::Backend,
73    error::{Error, Result},
74    job::{JobId, JobRecord, QueueStats},
75};
76
77const MIGRATION_SQL: &str = include_str!("../migrations/0001_create_asyncq_jobs.sql");
78
79/// PostgreSQL backend for asyncq.
80///
81/// Uses a connection pool (`sqlx::PgPool`) for concurrent job processing.
82/// Call [`migrate()`](PostgresBackend::migrate) once at startup to create
83/// the `asyncq_jobs` table.
84#[derive(Clone)]
85pub struct PostgresBackend {
86    pool: PgPool,
87}
88
89impl PostgresBackend {
90    /// Connect to PostgreSQL at `database_url` with default pool settings.
91    pub async fn new(database_url: &str) -> Result<Self> {
92        let pool = PgPoolOptions::new()
93            .max_connections(20)
94            .connect(database_url)
95            .await
96            .map_err(|e| Error::Backend(e.to_string()))?;
97        Ok(Self { pool })
98    }
99
100    /// Build from an existing `sqlx::PgPool` (e.g. shared with your web app).
101    pub fn from_pool(pool: PgPool) -> Self {
102        Self { pool }
103    }
104
105    /// Access the underlying connection pool (e.g. for cleanup in tests).
106    pub fn pool_ref(&self) -> &PgPool {
107        &self.pool
108    }
109
110    /// Create the `asyncq_jobs` table and indexes. Safe to call on every startup (idempotent).
111    ///
112    /// Concurrent callers (e.g. parallel test setup) are safe: if two callers
113    /// race on `CREATE TABLE`, the losing transaction gets a `unique_violation`
114    /// (Postgres error 23505) from the internal `pg_type` catalog. The schema
115    /// exists either way, so that specific error is treated as success.
116    pub async fn migrate(&self) -> Result<()> {
117        match sqlx::raw_sql(MIGRATION_SQL).execute(&self.pool).await {
118            Ok(_) => Ok(()),
119            Err(e) => {
120                if let Some(db_err) = e.as_database_error() {
121                    if db_err.code().as_deref() == Some("23505") {
122                        return Ok(());
123                    }
124                }
125                Err(Error::Backend(format!("migration failed: {e}")))
126            }
127        }
128    }
129
130    /// Enqueue a job inside an existing transaction (transactional outbox pattern).
131    ///
132    /// The job is only visible to workers after `tx.commit()`. If the transaction
133    /// rolls back, the job disappears — no orphaned notifications.
134    pub async fn enqueue_in_tx(
135        &self,
136        kind: &str,
137        queue: &str,
138        payload: Vec<u8>,
139        max_attempts: u32,
140        scheduled_at: DateTime<Utc>,
141        tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
142    ) -> Result<JobId> {
143        let id = Uuid::new_v4();
144        sqlx::query(
145            r#"INSERT INTO asyncq_jobs
146               (id, kind, queue, payload, max_attempts, scheduled_at)
147               VALUES ($1, $2, $3, $4, $5, $6)"#,
148        )
149        .bind(id)
150        .bind(kind)
151        .bind(queue)
152        .bind(&payload)
153        .bind(max_attempts as i32)
154        .bind(scheduled_at)
155        .execute(&mut **tx)
156        .await
157        .map_err(|e| Error::Backend(e.to_string()))?;
158        Ok(id)
159    }
160}
161
162#[async_trait]
163impl Backend for PostgresBackend {
164    async fn enqueue(&self, record: JobRecord) -> Result<JobId> {
165        let id = record.id;
166        sqlx::query(
167            r#"INSERT INTO asyncq_jobs
168               (id, kind, queue, payload, attempt, max_attempts, scheduled_at, created_at)
169               VALUES ($1, $2, $3, $4, $5, $6, $7, $8)"#,
170        )
171        .bind(record.id)
172        .bind(&record.kind)
173        .bind(&record.queue)
174        .bind(&record.payload)
175        .bind(record.attempt as i32)
176        .bind(record.max_attempts as i32)
177        .bind(record.scheduled_at)
178        .bind(record.created_at)
179        .execute(&self.pool)
180        .await
181        .map_err(|e| Error::Backend(e.to_string()))?;
182        Ok(id)
183    }
184
185    async fn claim(&self, queues: &[&str], timeout: Duration) -> Result<Option<JobRecord>> {
186        let deadline = tokio::time::Instant::now() + timeout;
187        let sleep_ms = 50u64;
188
189        loop {
190            // Skip LOCK SKIP LOCKED — use a single UPDATE that atomically claims
191            let row = sqlx::query(
192                r#"UPDATE asyncq_jobs
193                   SET status = 'running',
194                       attempt = attempt + 1,
195                       heartbeat_at = NOW()
196                   WHERE id = (
197                       SELECT id FROM asyncq_jobs
198                       WHERE queue = ANY($1)
199                         AND status = 'pending'
200                         AND scheduled_at <= NOW()
201                       ORDER BY scheduled_at ASC
202                       LIMIT 1
203                       FOR UPDATE SKIP LOCKED
204                   )
205                   RETURNING id, kind, queue, payload, attempt, max_attempts,
206                             scheduled_at, created_at, last_error"#,
207            )
208            .bind(queues)
209            .fetch_optional(&self.pool)
210            .await
211            .map_err(|e| Error::Backend(e.to_string()))?;
212
213            if let Some(row) = row {
214                return Ok(Some(row_to_record(row)?));
215            }
216
217            if timeout.is_zero() || tokio::time::Instant::now() >= deadline {
218                return Ok(None);
219            }
220            tokio::time::sleep(Duration::from_millis(sleep_ms)).await;
221        }
222    }
223
224    async fn ack(&self, id: JobId) -> Result<()> {
225        sqlx::query("UPDATE asyncq_jobs SET status = 'completed' WHERE id = $1")
226            .bind(id)
227            .execute(&self.pool)
228            .await
229            .map_err(|e| Error::Backend(e.to_string()))?;
230        Ok(())
231    }
232
233    async fn nack(&self, id: JobId, error: &str, retry_at: Option<DateTime<Utc>>) -> Result<()> {
234        match retry_at {
235            Some(at) => {
236                sqlx::query(
237                    r#"UPDATE asyncq_jobs
238                       SET status = 'pending', scheduled_at = $2, last_error = $3,
239                           heartbeat_at = NULL
240                       WHERE id = $1"#,
241                )
242                .bind(id)
243                .bind(at)
244                .bind(error)
245                .execute(&self.pool)
246                .await
247                .map_err(|e| Error::Backend(e.to_string()))?;
248            }
249            None => {
250                sqlx::query(
251                    r#"UPDATE asyncq_jobs
252                       SET status = 'dead', last_error = $2, heartbeat_at = NULL
253                       WHERE id = $1"#,
254                )
255                .bind(id)
256                .bind(error)
257                .execute(&self.pool)
258                .await
259                .map_err(|e| Error::Backend(e.to_string()))?;
260            }
261        }
262        Ok(())
263    }
264
265    async fn heartbeat(&self, id: JobId) -> Result<()> {
266        sqlx::query("UPDATE asyncq_jobs SET heartbeat_at = NOW() WHERE id = $1")
267            .bind(id)
268            .execute(&self.pool)
269            .await
270            .map_err(|e| Error::Backend(e.to_string()))?;
271        Ok(())
272    }
273
274    async fn reap_stuck(&self, older_than: Duration) -> Result<u64> {
275        let cutoff = Utc::now()
276            - chrono::Duration::from_std(older_than).unwrap_or(chrono::Duration::seconds(120));
277        let result = sqlx::query(
278            r#"UPDATE asyncq_jobs
279               SET status = 'pending', heartbeat_at = NULL
280               WHERE status = 'running' AND heartbeat_at < $1"#,
281        )
282        .bind(cutoff)
283        .execute(&self.pool)
284        .await
285        .map_err(|e| Error::Backend(e.to_string()))?;
286        Ok(result.rows_affected())
287    }
288
289    async fn stats(&self, queue: &str) -> Result<QueueStats> {
290        let row = sqlx::query(
291            r#"SELECT
292               COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
293               COUNT(*) FILTER (WHERE status = 'running')   AS running,
294               COUNT(*) FILTER (WHERE status = 'completed') AS completed,
295               COUNT(*) FILTER (WHERE status = 'failed')    AS failed,
296               COUNT(*) FILTER (WHERE status = 'dead')      AS dead
297               FROM asyncq_jobs WHERE queue = $1"#,
298        )
299        .bind(queue)
300        .fetch_one(&self.pool)
301        .await
302        .map_err(|e| Error::Backend(e.to_string()))?;
303
304        Ok(QueueStats {
305            queue: queue.to_owned(),
306            pending:   row.get::<i64, _>("pending")   as u64,
307            running:   row.get::<i64, _>("running")   as u64,
308            completed: row.get::<i64, _>("completed") as u64,
309            failed:    row.get::<i64, _>("failed")    as u64,
310            dead:      row.get::<i64, _>("dead")      as u64,
311        })
312    }
313
314    async fn dead_jobs(&self, queue: &str, limit: i64, offset: i64) -> Result<Vec<JobRecord>> {
315        let rows = sqlx::query(
316            r#"SELECT id, kind, queue, payload, attempt, max_attempts,
317                      scheduled_at, created_at, last_error
318               FROM asyncq_jobs
319               WHERE queue = $1 AND status = 'dead'
320               ORDER BY created_at DESC
321               LIMIT $2 OFFSET $3"#,
322        )
323        .bind(queue)
324        .bind(limit)
325        .bind(offset)
326        .fetch_all(&self.pool)
327        .await
328        .map_err(|e| Error::Backend(e.to_string()))?;
329
330        rows.into_iter().map(row_to_record).collect()
331    }
332
333    async fn retry_dead(&self, id: JobId) -> Result<()> {
334        let result = sqlx::query(
335            r#"UPDATE asyncq_jobs
336               SET status = 'pending', scheduled_at = NOW(),
337                   last_error = NULL, heartbeat_at = NULL
338               WHERE id = $1 AND status = 'dead'"#,
339        )
340        .bind(id)
341        .execute(&self.pool)
342        .await
343        .map_err(|e| Error::Backend(e.to_string()))?;
344
345        if result.rows_affected() == 0 {
346            return Err(Error::NotFound(id.to_string()));
347        }
348        Ok(())
349    }
350
351    async fn retry_all_dead(&self, queue: &str) -> Result<u64> {
352        let result = sqlx::query(
353            r#"UPDATE asyncq_jobs
354               SET status = 'pending', scheduled_at = NOW(),
355                   last_error = NULL, heartbeat_at = NULL
356               WHERE queue = $1 AND status = 'dead'"#,
357        )
358        .bind(queue)
359        .execute(&self.pool)
360        .await
361        .map_err(|e| Error::Backend(e.to_string()))?;
362        Ok(result.rows_affected())
363    }
364}
365
366// ── Row mapping ───────────────────────────────────────────────────────────────
367
368fn row_to_record(row: sqlx::postgres::PgRow) -> Result<JobRecord> {
369    Ok(JobRecord {
370        id:           row.get("id"),
371        kind:         row.get("kind"),
372        queue:        row.get("queue"),
373        payload:      row.get("payload"),
374        attempt:      row.get::<i32, _>("attempt") as u32,
375        max_attempts: row.get::<i32, _>("max_attempts") as u32,
376        scheduled_at: row.get("scheduled_at"),
377        created_at:   row.get("created_at"),
378        last_error:   row.get("last_error"),
379    })
380}