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 migrating = SqlitePoolOptions::new()
28        .max_connections(1)
29        .connect_with(options.clone())
30        .await?;
31    sqlx::migrate!("./migrations/sqlite")
32        .set_ignore_missing(true)
33        .run(&migrating)
34        .await?;
35    migrating.close().await;
36
37    let pool = SqlitePoolOptions::new()
38        .max_connections(50)
39        .min_connections(10)
40        .acquire_timeout(Duration::from_secs(30))
41        .idle_timeout(Duration::from_secs(300))
42        .max_lifetime(Duration::from_secs(1800))
43        .connect_with(options)
44        .await?;
45
46    pool.execute("PRAGMA cache_size = -64000").await?; // 64MB cache
47    pool.execute("PRAGMA temp_store = MEMORY").await?;
48    pool.execute("PRAGMA mmap_size = 268435456").await?; // 256MB mmap
49    pool.execute("PRAGMA auto_vacuum = INCREMENTAL").await?; // Enables incremental auto-vacuum
50    pool.execute("PRAGMA wal_autocheckpoint = 1000").await?; // Checkpoint every
51                                                             // 1000 pages
52
53    Ok(pool)
54}
55
56pub async fn init_postgres_database(connection_string: &str) -> anyhow::Result<PgPool> {
57    let pool = PgPoolOptions::new()
58        .max_connections(50)
59        .min_connections(10)
60        .acquire_timeout(Duration::from_secs(30))
61        .idle_timeout(Duration::from_secs(300))
62        .max_lifetime(Duration::from_secs(1800))
63        .after_connect(|conn, _meta| {
64            Box::pin(async move {
65                sqlx::query("SET search_path TO public")
66                    .execute(conn)
67                    .await?;
68                Ok(())
69            })
70        })
71        .connect(connection_string)
72        .await?;
73
74    sqlx::migrate!("./migrations/postgres")
75        .set_ignore_missing(true)
76        .run(&pool)
77        .await?;
78
79    Ok(pool)
80}
pipedash_core/infrastructure/database/schema.rs

pipedash_core/infrastructure/database/
schema.rs
