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