1use std::{
2 hash::Hash,
3 marker::PhantomData,
4 sync::Arc,
5 time::{SystemTime, UNIX_EPOCH},
6};
7
8use async_trait::async_trait;
9use kape::{CacheBackend, CacheEntry, KapeResult, SetItem, validate_set_items};
10use sqlx::{AssertSqlSafe, PgPool, Postgres, Row, Transaction};
11
12use crate::mutation::{MutationPlan, MutationPlanner, PlannedSet, PlannedUpsert};
13use crate::{PostgresBackendError, PostgresCodec, PostgresKey, PostgresValue, StringCodec, codec};
14
15pub struct PostgresBackend<K = String, V = String, C = StringCodec> {
17 pool: PgPool,
18 codec: C,
19 table: String,
20 namespace: String,
21 marker: PhantomData<fn(K, V)>,
22}
23
24impl<K, V, C> Clone for PostgresBackend<K, V, C>
25where
26 C: Clone,
27{
28 fn clone(&self) -> Self {
29 Self {
30 pool: self.pool.clone(),
31 codec: self.codec.clone(),
32 table: self.table.clone(),
33 namespace: self.namespace.clone(),
34 marker: PhantomData,
35 }
36 }
37}
38
39impl<K, V> PostgresBackend<K, V> {
40 #[must_use]
42 pub fn new(pool: PgPool) -> Self {
43 Self {
44 pool,
45 codec: StringCodec,
46 table: "\"kape_entries\"".to_owned(),
47 namespace: String::new(),
48 marker: PhantomData,
49 }
50 }
51}
52
53impl<K, V, C> PostgresBackend<K, V, C> {
54 #[must_use]
56 pub fn with_codec<D>(self, codec: D) -> PostgresBackend<K, V, D>
57 where
58 D: PostgresCodec<K, V>,
59 {
60 PostgresBackend {
61 pool: self.pool,
62 codec,
63 table: self.table,
64 namespace: self.namespace,
65 marker: PhantomData,
66 }
67 }
68
69 pub fn with_table(mut self, table: &str) -> KapeResult<Self> {
75 self.table = validate_table_name(table)
76 .ok_or_else(|| PostgresBackendError::InvalidTableName(table.to_owned()))?;
77 Ok(self)
78 }
79
80 #[must_use]
82 pub fn namespace(mut self, namespace: impl Into<String>) -> Self {
83 self.namespace = namespace.into();
84 self
85 }
86}
87
88impl<K, V, C> PostgresBackend<K, V, C>
89where
90 C: PostgresCodec<K, V>,
91{
92 fn namespace_prefix(&self) -> C::EncodedKey {
93 C::EncodedKey::namespace_prefix(&self.namespace)
94 }
95
96 fn encode_key(&self, key: &K) -> Result<C::EncodedKey, PostgresBackendError> {
97 codec::encode_namespaced_key(&self.codec, &self.namespace, key)
98 }
99
100 pub async fn purge_expired(&self) -> KapeResult<u64> {
109 let prefix = self.namespace_prefix();
110 let statement = format!(
111 "DELETE FROM {} \
112 WHERE substring(key from 1 for length($1)) = $1 \
113 AND expires_at_ms IS NOT NULL \
114 AND expires_at_ms <= (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT",
115 self.table
116 );
117 let result = sqlx::query(AssertSqlSafe(statement))
118 .bind(prefix)
119 .execute(&self.pool)
120 .await
121 .map_err(PostgresBackendError::Sqlx)?;
122 Ok(result.rows_affected())
123 }
124}
125
126#[async_trait]
127impl<K, V, C> CacheBackend<K, V> for PostgresBackend<K, V, C>
128where
129 K: Send + Sync,
130 V: Send + Sync,
131 C: PostgresCodec<K, V>,
132{
133 async fn get(&self, key: &K) -> KapeResult<Option<CacheEntry<V>>> {
134 let key = self.encode_key(key)?;
135 let statement = format!(
136 "SELECT value, CASE WHEN expires_at_ms IS NULL THEN NULL \
137 ELSE expires_at_ms - (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT \
138 END AS remaining_ms FROM {} WHERE key = $1",
139 self.table
140 );
141 let Some(row) = sqlx::query(AssertSqlSafe(statement))
142 .bind(key)
143 .fetch_optional(&self.pool)
144 .await
145 .map_err(PostgresBackendError::Sqlx)?
146 else {
147 return Ok(None);
148 };
149 let value: C::EncodedValue = row.try_get("value").map_err(PostgresBackendError::Sqlx)?;
150 let remaining_ms: Option<i64> = row
151 .try_get("remaining_ms")
152 .map_err(PostgresBackendError::Sqlx)?;
153 crate::lookup::decode_lookup(&self.codec, Some(value), remaining_ms).map_err(Into::into)
154 }
155
156 async fn set(&self, key: &K, value: Arc<V>, ttl: i64) -> KapeResult<()> {
157 let now_ms = (ttl > 0).then(unix_now_ms).transpose()?;
158 let planner = MutationPlanner::new(&self.codec, &self.namespace);
159 match planner.plan_set(key, value.as_ref(), ttl, now_ms)? {
160 PlannedSet::Upsert(plan) => upsert(&self.pool, &self.table, plan).await,
161 PlannedSet::Delete(key) => delete_one(&self.pool, &self.table, key).await,
162 }
163 }
164
165 async fn remove(&self, key: &K) -> KapeResult<()> {
166 let key = self.encode_key(key)?;
167 delete_one(&self.pool, &self.table, key).await
168 }
169
170 async fn get_many(&self, keys: &[&K]) -> KapeResult<Vec<Option<CacheEntry<V>>>> {
171 if keys.is_empty() {
172 return Ok(Vec::new());
173 }
174 let keys = keys
175 .iter()
176 .map(|key| self.encode_key(key))
177 .collect::<Result<Vec<_>, _>>()?;
178 let statement = format!(
179 "WITH requested(key, ord) AS (\
180 SELECT * FROM UNNEST($1) WITH ORDINALITY\
181 ) SELECT entries.value, CASE WHEN entries.expires_at_ms IS NULL THEN NULL \
182 ELSE entries.expires_at_ms - \
183 (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT END AS remaining_ms \
184 FROM requested LEFT JOIN {} AS entries ON entries.key = requested.key \
185 ORDER BY requested.ord",
186 self.table
187 );
188 let rows = sqlx::query(AssertSqlSafe(statement))
189 .bind(keys)
190 .fetch_all(&self.pool)
191 .await
192 .map_err(PostgresBackendError::Sqlx)?;
193 rows.into_iter()
194 .map(|row| {
195 let value: Option<C::EncodedValue> =
196 row.try_get("value").map_err(PostgresBackendError::Sqlx)?;
197 let remaining_ms: Option<i64> = row
198 .try_get("remaining_ms")
199 .map_err(PostgresBackendError::Sqlx)?;
200 crate::lookup::decode_lookup(&self.codec, value, remaining_ms)
201 })
202 .collect::<Result<Vec<_>, PostgresBackendError>>()
203 .map_err(Into::into)
204 }
205
206 async fn set_many(&self, items: &[SetItem<&K, V>]) -> KapeResult<()>
207 where
208 K: Eq + Hash,
209 {
210 validate_set_items(items)?;
211 if items.is_empty() {
212 return Ok(());
213 }
214
215 let now_ms = items
216 .iter()
217 .any(|item| item.ttl > 0)
218 .then(unix_now_ms)
219 .transpose()?;
220 let MutationPlan { upserts, deletes } =
221 MutationPlanner::new(&self.codec, &self.namespace).plan_many(items, now_ms)?;
222
223 match (upserts.is_empty(), deletes.is_empty()) {
224 (false, true) => upsert_many(&self.pool, &self.table, upserts).await,
225 (true, false) => delete_many(&self.pool, &self.table, deletes).await,
226 (false, false) => {
227 let mut transaction = self
228 .pool
229 .begin()
230 .await
231 .map_err(PostgresBackendError::Sqlx)?;
232 upsert_many_in_transaction(&mut transaction, &self.table, upserts).await?;
233 delete_many_in_transaction(&mut transaction, &self.table, deletes).await?;
234 transaction
235 .commit()
236 .await
237 .map_err(PostgresBackendError::Sqlx)?;
238 Ok(())
239 }
240 (true, true) => Ok(()),
241 }
242 }
243
244 async fn remove_many(&self, keys: &[&K]) -> KapeResult<()> {
245 if keys.is_empty() {
246 return Ok(());
247 }
248 let keys = keys
249 .iter()
250 .map(|key| self.encode_key(key))
251 .collect::<Result<Vec<_>, _>>()?;
252 delete_many(&self.pool, &self.table, keys).await
253 }
254
255 async fn clear(&self) -> KapeResult<()> {
256 let prefix = self.namespace_prefix();
257 let statement = format!(
258 "DELETE FROM {} WHERE substring(key from 1 for length($1)) = $1",
259 self.table
260 );
261 sqlx::query(AssertSqlSafe(statement))
262 .bind(prefix)
263 .execute(&self.pool)
264 .await
265 .map_err(PostgresBackendError::Sqlx)?;
266 Ok(())
267 }
268}
269
270fn unix_now_ms() -> Result<i64, PostgresBackendError> {
272 let elapsed = SystemTime::now()
273 .duration_since(UNIX_EPOCH)
274 .map_err(PostgresBackendError::SystemClockBeforeUnixEpoch)?;
275 i64::try_from(elapsed.as_millis()).map_err(|_| PostgresBackendError::TimestampOverflow)
276}
277
278async fn upsert<K, V>(pool: &PgPool, table: &str, upsert: PlannedUpsert<K, V>) -> KapeResult<()>
279where
280 K: PostgresValue,
281 V: PostgresValue,
282{
283 let PlannedUpsert {
284 key,
285 value,
286 expires_at_ms,
287 } = upsert;
288 let statement = upsert_statement(table);
289 sqlx::query(AssertSqlSafe(statement))
290 .bind(key)
291 .bind(value)
292 .bind(expires_at_ms)
293 .execute(pool)
294 .await
295 .map_err(PostgresBackendError::Sqlx)?;
296 Ok(())
297}
298
299async fn upsert_many<K, V>(
300 pool: &PgPool,
301 table: &str,
302 upserts: Vec<PlannedUpsert<K, V>>,
303) -> KapeResult<()>
304where
305 K: PostgresValue,
306 V: PostgresValue,
307{
308 let statement = upsert_many_statement::<K, V>(table);
309 let (keys, values, expires_at_ms) = sql_upsert_arrays(upserts);
310 sqlx::query(AssertSqlSafe(statement))
311 .bind(keys)
312 .bind(values)
313 .bind(expires_at_ms)
314 .execute(pool)
315 .await
316 .map_err(PostgresBackendError::Sqlx)?;
317 Ok(())
318}
319
320async fn upsert_many_in_transaction<K, V>(
321 transaction: &mut Transaction<'_, Postgres>,
322 table: &str,
323 upserts: Vec<PlannedUpsert<K, V>>,
324) -> KapeResult<()>
325where
326 K: PostgresValue,
327 V: PostgresValue,
328{
329 let statement = upsert_many_statement::<K, V>(table);
330 let (keys, values, expires_at_ms) = sql_upsert_arrays(upserts);
331 sqlx::query(AssertSqlSafe(statement))
332 .bind(keys)
333 .bind(values)
334 .bind(expires_at_ms)
335 .execute(&mut **transaction)
336 .await
337 .map_err(PostgresBackendError::Sqlx)?;
338 Ok(())
339}
340
341fn sql_upsert_arrays<K, V>(
342 upserts: Vec<PlannedUpsert<K, V>>,
343) -> (Vec<K>, Vec<V>, Vec<Option<i64>>) {
344 let mut keys = Vec::with_capacity(upserts.len());
345 let mut values = Vec::with_capacity(upserts.len());
346 let mut expires_at_ms = Vec::with_capacity(upserts.len());
347 for upsert in upserts {
348 keys.push(upsert.key);
349 values.push(upsert.value);
350 expires_at_ms.push(upsert.expires_at_ms);
351 }
352 (keys, values, expires_at_ms)
353}
354
355async fn delete_one<K>(pool: &PgPool, table: &str, key: K) -> KapeResult<()>
356where
357 K: PostgresValue,
358{
359 let statement = format!("DELETE FROM {table} WHERE key = $1");
360 sqlx::query(AssertSqlSafe(statement))
361 .bind(key)
362 .execute(pool)
363 .await
364 .map_err(PostgresBackendError::Sqlx)?;
365 Ok(())
366}
367
368async fn delete_many<K>(pool: &PgPool, table: &str, keys: Vec<K>) -> KapeResult<()>
370where
371 K: PostgresValue,
372{
373 let statement = format!("DELETE FROM {table} WHERE key = ANY($1)");
374 sqlx::query(AssertSqlSafe(statement))
375 .bind(keys)
376 .execute(pool)
377 .await
378 .map_err(PostgresBackendError::Sqlx)?;
379 Ok(())
380}
381
382async fn delete_many_in_transaction<K>(
384 transaction: &mut Transaction<'_, Postgres>,
385 table: &str,
386 keys: Vec<K>,
387) -> KapeResult<()>
388where
389 K: PostgresValue,
390{
391 let statement = format!("DELETE FROM {table} WHERE key = ANY($1)");
392 sqlx::query(AssertSqlSafe(statement))
393 .bind(keys)
394 .execute(&mut **transaction)
395 .await
396 .map_err(PostgresBackendError::Sqlx)?;
397 Ok(())
398}
399
400fn upsert_statement(table: &str) -> String {
402 format!(
403 "INSERT INTO {table} (key, value, expires_at_ms) VALUES ($1, $2, $3) \
404 ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, \
405 expires_at_ms = EXCLUDED.expires_at_ms"
406 )
407}
408
409fn upsert_many_statement<K, V>(table: &str) -> String
410where
411 K: PostgresValue,
412 V: PostgresValue,
413{
414 let key_array = K::array_type_name();
415 let value_array = V::array_type_name();
416 format!(
417 "INSERT INTO {table} (key, value, expires_at_ms) \
418 SELECT input.key, input.value, input.expires_at_ms \
419 FROM UNNEST($1::{key_array}, $2::{value_array}, $3::BIGINT[]) \
420 AS input(key, value, expires_at_ms) \
421 ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, \
422 expires_at_ms = EXCLUDED.expires_at_ms"
423 )
424}
425
426fn validate_table_name(table: &str) -> Option<String> {
427 let parts = table.split('.').collect::<Vec<_>>();
428 if parts.is_empty() || parts.len() > 2 || !parts.iter().all(|part| valid_identifier(part)) {
429 return None;
430 }
431 Some(
432 parts
433 .into_iter()
434 .map(|part| format!("\"{part}\""))
435 .collect::<Vec<_>>()
436 .join("."),
437 )
438}
439
440fn valid_identifier(identifier: &str) -> bool {
441 let mut chars = identifier.chars();
442 let Some(first) = chars.next() else {
443 return false;
444 };
445 (first == '_' || first.is_ascii_alphabetic())
446 && chars.all(|character| character == '_' || character.is_ascii_alphanumeric())
447}
448
449#[cfg(test)]
450mod tests {
451 use super::*;
452
453 #[test]
454 fn validates_table_names() {
455 assert_eq!(
456 validate_table_name("kape_entries").as_deref(),
457 Some("\"kape_entries\"")
458 );
459 assert_eq!(
460 validate_table_name("app.kape_entries").as_deref(),
461 Some("\"app\".\"kape_entries\"")
462 );
463 assert_eq!(validate_table_name("bad-name"), None);
464 assert_eq!(validate_table_name("public.entries;DROP TABLE users"), None);
465 assert_eq!(validate_table_name("a.b.c"), None);
466 }
467}