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    pub async fn migrate(&self) -> Result<()> {
112        sqlx::raw_sql(MIGRATION_SQL)
113            .execute(&self.pool)
114            .await
115            .map_err(|e| Error::Backend(format!("migration failed: {e}")))?;
116        Ok(())
117    }
118
119    /// Enqueue a job inside an existing transaction (transactional outbox pattern).
120    ///
121    /// The job is only visible to workers after `tx.commit()`. If the transaction
122    /// rolls back, the job disappears — no orphaned notifications.
123    pub async fn enqueue_in_tx(
124        &self,
125        kind: &str,
126        queue: &str,
127        payload: Vec<u8>,
128        max_attempts: u32,
129        scheduled_at: DateTime<Utc>,
130        tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
131    ) -> Result<JobId> {
132        let id = Uuid::new_v4();
133        sqlx::query(
134            r#"INSERT INTO asyncq_jobs
135               (id, kind, queue, payload, max_attempts, scheduled_at)
136               VALUES ($1, $2, $3, $4, $5, $6)"#,
137        )
138        .bind(id)
139        .bind(kind)
140        .bind(queue)
141        .bind(&payload)
142        .bind(max_attempts as i32)
143        .bind(scheduled_at)
144        .execute(&mut **tx)
145        .await
146        .map_err(|e| Error::Backend(e.to_string()))?;
147        Ok(id)
148    }
149}
150
151#[async_trait]
152impl Backend for PostgresBackend {
153    async fn enqueue(&self, record: JobRecord) -> Result<JobId> {
154        let id = record.id;
155        sqlx::query(
156            r#"INSERT INTO asyncq_jobs
157               (id, kind, queue, payload, attempt, max_attempts, scheduled_at, created_at)
158               VALUES ($1, $2, $3, $4, $5, $6, $7, $8)"#,
159        )
160        .bind(record.id)
161        .bind(&record.kind)
162        .bind(&record.queue)
163        .bind(&record.payload)
164        .bind(record.attempt as i32)
165        .bind(record.max_attempts as i32)
166        .bind(record.scheduled_at)
167        .bind(record.created_at)
168        .execute(&self.pool)
169        .await
170        .map_err(|e| Error::Backend(e.to_string()))?;
171        Ok(id)
172    }
173
174    async fn claim(&self, queues: &[&str], timeout: Duration) -> Result<Option<JobRecord>> {
175        let deadline = tokio::time::Instant::now() + timeout;
176        let sleep_ms = 50u64;
177
178        loop {
179            // Skip LOCK SKIP LOCKED — use a single UPDATE that atomically claims
180            let row = sqlx::query(
181                r#"UPDATE asyncq_jobs
182                   SET status = 'running',
183                       attempt = attempt + 1,
184                       heartbeat_at = NOW()
185                   WHERE id = (
186                       SELECT id FROM asyncq_jobs
187                       WHERE queue = ANY($1)
188                         AND status = 'pending'
189                         AND scheduled_at <= NOW()
190                       ORDER BY scheduled_at ASC
191                       LIMIT 1
192                       FOR UPDATE SKIP LOCKED
193                   )
194                   RETURNING id, kind, queue, payload, attempt, max_attempts,
195                             scheduled_at, created_at, last_error"#,
196            )
197            .bind(queues)
198            .fetch_optional(&self.pool)
199            .await
200            .map_err(|e| Error::Backend(e.to_string()))?;
201
202            if let Some(row) = row {
203                return Ok(Some(row_to_record(row)?));
204            }
205
206            if timeout.is_zero() || tokio::time::Instant::now() >= deadline {
207                return Ok(None);
208            }
209            tokio::time::sleep(Duration::from_millis(sleep_ms)).await;
210        }
211    }
212
213    async fn ack(&self, id: JobId) -> Result<()> {
214        sqlx::query("UPDATE asyncq_jobs SET status = 'completed' WHERE id = $1")
215            .bind(id)
216            .execute(&self.pool)
217            .await
218            .map_err(|e| Error::Backend(e.to_string()))?;
219        Ok(())
220    }
221
222    async fn nack(&self, id: JobId, error: &str, retry_at: Option<DateTime<Utc>>) -> Result<()> {
223        match retry_at {
224            Some(at) => {
225                sqlx::query(
226                    r#"UPDATE asyncq_jobs
227                       SET status = 'pending', scheduled_at = $2, last_error = $3,
228                           heartbeat_at = NULL
229                       WHERE id = $1"#,
230                )
231                .bind(id)
232                .bind(at)
233                .bind(error)
234                .execute(&self.pool)
235                .await
236                .map_err(|e| Error::Backend(e.to_string()))?;
237            }
238            None => {
239                sqlx::query(
240                    r#"UPDATE asyncq_jobs
241                       SET status = 'dead', last_error = $2, heartbeat_at = NULL
242                       WHERE id = $1"#,
243                )
244                .bind(id)
245                .bind(error)
246                .execute(&self.pool)
247                .await
248                .map_err(|e| Error::Backend(e.to_string()))?;
249            }
250        }
251        Ok(())
252    }
253
254    async fn heartbeat(&self, id: JobId) -> Result<()> {
255        sqlx::query("UPDATE asyncq_jobs SET heartbeat_at = NOW() WHERE id = $1")
256            .bind(id)
257            .execute(&self.pool)
258            .await
259            .map_err(|e| Error::Backend(e.to_string()))?;
260        Ok(())
261    }
262
263    async fn reap_stuck(&self, older_than: Duration) -> Result<u64> {
264        let cutoff = Utc::now()
265            - chrono::Duration::from_std(older_than).unwrap_or(chrono::Duration::seconds(120));
266        let result = sqlx::query(
267            r#"UPDATE asyncq_jobs
268               SET status = 'pending', heartbeat_at = NULL
269               WHERE status = 'running' AND heartbeat_at < $1"#,
270        )
271        .bind(cutoff)
272        .execute(&self.pool)
273        .await
274        .map_err(|e| Error::Backend(e.to_string()))?;
275        Ok(result.rows_affected())
276    }
277
278    async fn stats(&self, queue: &str) -> Result<QueueStats> {
279        let row = sqlx::query(
280            r#"SELECT
281               COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
282               COUNT(*) FILTER (WHERE status = 'running')   AS running,
283               COUNT(*) FILTER (WHERE status = 'completed') AS completed,
284               COUNT(*) FILTER (WHERE status = 'failed')    AS failed,
285               COUNT(*) FILTER (WHERE status = 'dead')      AS dead
286               FROM asyncq_jobs WHERE queue = $1"#,
287        )
288        .bind(queue)
289        .fetch_one(&self.pool)
290        .await
291        .map_err(|e| Error::Backend(e.to_string()))?;
292
293        Ok(QueueStats {
294            queue: queue.to_owned(),
295            pending:   row.get::<i64, _>("pending")   as u64,
296            running:   row.get::<i64, _>("running")   as u64,
297            completed: row.get::<i64, _>("completed") as u64,
298            failed:    row.get::<i64, _>("failed")    as u64,
299            dead:      row.get::<i64, _>("dead")      as u64,
300        })
301    }
302
303    async fn dead_jobs(&self, queue: &str, limit: i64, offset: i64) -> Result<Vec<JobRecord>> {
304        let rows = sqlx::query(
305            r#"SELECT id, kind, queue, payload, attempt, max_attempts,
306                      scheduled_at, created_at, last_error
307               FROM asyncq_jobs
308               WHERE queue = $1 AND status = 'dead'
309               ORDER BY created_at DESC
310               LIMIT $2 OFFSET $3"#,
311        )
312        .bind(queue)
313        .bind(limit)
314        .bind(offset)
315        .fetch_all(&self.pool)
316        .await
317        .map_err(|e| Error::Backend(e.to_string()))?;
318
319        rows.into_iter().map(row_to_record).collect()
320    }
321
322    async fn retry_dead(&self, id: JobId) -> Result<()> {
323        let result = sqlx::query(
324            r#"UPDATE asyncq_jobs
325               SET status = 'pending', scheduled_at = NOW(),
326                   last_error = NULL, heartbeat_at = NULL
327               WHERE id = $1 AND status = 'dead'"#,
328        )
329        .bind(id)
330        .execute(&self.pool)
331        .await
332        .map_err(|e| Error::Backend(e.to_string()))?;
333
334        if result.rows_affected() == 0 {
335            return Err(Error::NotFound(id.to_string()));
336        }
337        Ok(())
338    }
339
340    async fn retry_all_dead(&self, queue: &str) -> Result<u64> {
341        let result = sqlx::query(
342            r#"UPDATE asyncq_jobs
343               SET status = 'pending', scheduled_at = NOW(),
344                   last_error = NULL, heartbeat_at = NULL
345               WHERE queue = $1 AND status = 'dead'"#,
346        )
347        .bind(queue)
348        .execute(&self.pool)
349        .await
350        .map_err(|e| Error::Backend(e.to_string()))?;
351        Ok(result.rows_affected())
352    }
353}
354
355// ── Row mapping ───────────────────────────────────────────────────────────────
356
357fn row_to_record(row: sqlx::postgres::PgRow) -> Result<JobRecord> {
358    Ok(JobRecord {
359        id:           row.get("id"),
360        kind:         row.get("kind"),
361        queue:        row.get("queue"),
362        payload:      row.get("payload"),
363        attempt:      row.get::<i32, _>("attempt") as u32,
364        max_attempts: row.get::<i32, _>("max_attempts") as u32,
365        scheduled_at: row.get("scheduled_at"),
366        created_at:   row.get("created_at"),
367        last_error:   row.get("last_error"),
368    })
369}