pipedash_core/infrastructure/database/
schema.rs

1use std::path::PathBuf;
2use std::time::Duration;
3
4use sqlx::postgres::{
5    PgPool,
6    PgPoolOptions,
7};
8use sqlx::sqlite::{
9    SqliteConnectOptions,
10    SqlitePoolOptions,
11};
12use sqlx::{
13    Executor,
14    SqlitePool,
15};
16
17pub async fn init_database(path: PathBuf) -> anyhow::Result<SqlitePool> {
18    let options = SqliteConnectOptions::new()
19        .filename(&path)
20        .create_if_missing(true)
21        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
22        .synchronous(sqlx::sqlite::SqliteSynchronous::Normal)
23        .busy_timeout(Duration::from_secs(5)); // Reduced from 30s to 5s to
24                                               // prevent compounding with
25                                               // retries
26
27    let pool = SqlitePoolOptions::new()
28        .max_connections(50)
29        .min_connections(10)
30        .acquire_timeout(Duration::from_secs(30))
31        .idle_timeout(Duration::from_secs(300))
32        .max_lifetime(Duration::from_secs(1800))
33        .connect_with(options)
34        .await?;
35
36    sqlx::migrate!("./migrations/sqlite")
37        .set_ignore_missing(true)
38        .run(&pool)
39        .await?;
40
41    pool.execute("PRAGMA cache_size = -64000").await?; // 64MB cache
42    pool.execute("PRAGMA temp_store = MEMORY").await?;
43    pool.execute("PRAGMA mmap_size = 268435456").await?; // 256MB mmap
44    pool.execute("PRAGMA auto_vacuum = INCREMENTAL").await?; // Enables incremental auto-vacuum
45    pool.execute("PRAGMA wal_autocheckpoint = 1000").await?; // Checkpoint every
46                                                             // 1000 pages
47
48    Ok(pool)
49}
50
51pub async fn init_postgres_database(connection_string: &str) -> anyhow::Result<PgPool> {
52    let pool = PgPoolOptions::new()
53        .max_connections(50)
54        .min_connections(10)
55        .acquire_timeout(Duration::from_secs(30))
56        .idle_timeout(Duration::from_secs(300))
57        .max_lifetime(Duration::from_secs(1800))
58        .after_connect(|conn, _meta| {
59            Box::pin(async move {
60                sqlx::query("SET search_path TO public")
61                    .execute(conn)
62                    .await?;
63                Ok(())
64            })
65        })
66        .connect(connection_string)
67        .await?;
68
69    sqlx::migrate!("./migrations/postgres")
70        .set_ignore_missing(true)
71        .run(&pool)
72        .await?;
73
74    Ok(pool)
75}
pipedash_core/infrastructure/database/schema.rs

pipedash_core/infrastructure/database/
schema.rs
