lib_migrations_sql/
sqlite.rs

1use crate::migration::SqlExecutor;
2use lib_migrations_core::{MigrationRecord, MigrationStore};
3use rusqlite::{params, Connection};
4use std::path::Path;
5use std::time::{SystemTime, UNIX_EPOCH};
6
7/// SQLite execution context wrapping a connection.
8pub struct SqliteContext {
9    conn: Connection,
10}
11
12impl SqliteContext {
13    /// Open a SQLite database
14    pub fn open(path: impl AsRef<Path>) -> Result<Self, rusqlite::Error> {
15        let conn = Connection::open(path)?;
16        conn.execute_batch(
17            "PRAGMA journal_mode=WAL;
18             PRAGMA synchronous=NORMAL;
19             PRAGMA foreign_keys=ON;",
20        )?;
21        Ok(Self { conn })
22    }
23
24    /// Open an in-memory SQLite database
25    pub fn open_in_memory() -> Result<Self, rusqlite::Error> {
26        let conn = Connection::open_in_memory()?;
27        Ok(Self { conn })
28    }
29
30    /// Get the underlying connection
31    pub fn connection(&self) -> &Connection {
32        &self.conn
33    }
34
35    /// Get mutable access to the underlying connection
36    pub fn connection_mut(&mut self) -> &mut Connection {
37        &mut self.conn
38    }
39
40    /// Consume and return the underlying connection
41    pub fn into_connection(self) -> Connection {
42        self.conn
43    }
44}
45
46impl SqlExecutor for SqliteContext {
47    type Error = rusqlite::Error;
48
49    fn execute(&mut self, sql: &str) -> Result<(), Self::Error> {
50        self.conn.execute_batch(sql)
51    }
52}
53
54/// SQLite-backed migration store.
55///
56/// Stores migration history in a `_migrations` table.
57pub struct SqliteStore {
58    conn: Connection,
59}
60
61impl SqliteStore {
62    /// Open a SQLite database for migration tracking
63    pub fn open(path: impl AsRef<Path>) -> Result<Self, rusqlite::Error> {
64        let conn = Connection::open(path)?;
65        conn.execute_batch(
66            "PRAGMA journal_mode=WAL;
67             PRAGMA synchronous=NORMAL;",
68        )?;
69        Ok(Self { conn })
70    }
71
72    /// Open an in-memory SQLite database
73    pub fn open_in_memory() -> Result<Self, rusqlite::Error> {
74        let conn = Connection::open_in_memory()?;
75        Ok(Self { conn })
76    }
77
78    /// Get the underlying connection
79    pub fn connection(&self) -> &Connection {
80        &self.conn
81    }
82
83    /// Consume and return the underlying connection
84    pub fn into_connection(self) -> Connection {
85        self.conn
86    }
87
88    fn now() -> u64 {
89        SystemTime::now()
90            .duration_since(UNIX_EPOCH)
91            .map(|d| d.as_secs())
92            .unwrap_or(0)
93    }
94}
95
96impl MigrationStore for SqliteStore {
97    fn init(&mut self) -> lib_migrations_core::Result<()> {
98        self.conn
99            .execute_batch(
100                "CREATE TABLE IF NOT EXISTS _migrations (
101                    version INTEGER PRIMARY KEY,
102                    name TEXT NOT NULL,
103                    applied_at INTEGER NOT NULL
104                );",
105            )
106            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))
107    }
108
109    fn applied(&self) -> lib_migrations_core::Result<Vec<MigrationRecord>> {
110        let mut stmt = self
111            .conn
112            .prepare("SELECT version, name, applied_at FROM _migrations ORDER BY version")
113            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))?;
114
115        let records = stmt
116            .query_map([], |row| {
117                Ok(MigrationRecord {
118                    version: row.get::<_, i64>(0)? as u64,
119                    name: row.get(1)?,
120                    applied_at: row.get::<_, i64>(2)? as u64,
121                })
122            })
123            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))?
124            .collect::<Result<Vec<_>, _>>()
125            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))?;
126
127        Ok(records)
128    }
129
130    fn mark_applied(&mut self, version: u64, name: &str) -> lib_migrations_core::Result<()> {
131        self.conn
132            .execute(
133                "INSERT INTO _migrations (version, name, applied_at) VALUES (?1, ?2, ?3)",
134                params![version as i64, name, Self::now() as i64],
135            )
136            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))?;
137        Ok(())
138    }
139
140    fn mark_rolled_back(&mut self, version: u64) -> lib_migrations_core::Result<()> {
141        self.conn
142            .execute(
143                "DELETE FROM _migrations WHERE version = ?1",
144                params![version as i64],
145            )
146            .map_err(|e| lib_migrations_core::Error::store(e.to_string()))?;
147        Ok(())
148    }
149}
150
151#[cfg(test)]
152mod tests {
153    use super::*;
154    use crate::SqlMigration;
155    use lib_migrations_core::MigrationRunner;
156
157    #[test]
158    fn test_sqlite_store() {
159        let mut store = SqliteStore::open_in_memory().unwrap();
160        store.init().unwrap();
161
162        assert_eq!(store.current_version().unwrap(), 0);
163        assert!(store.applied().unwrap().is_empty());
164
165        store.mark_applied(1, "first").unwrap();
166        assert_eq!(store.current_version().unwrap(), 1);
167
168        let applied = store.applied().unwrap();
169        assert_eq!(applied.len(), 1);
170        assert_eq!(applied[0].version, 1);
171        assert_eq!(applied[0].name, "first");
172
173        store.mark_rolled_back(1).unwrap();
174        assert_eq!(store.current_version().unwrap(), 0);
175    }
176
177    #[test]
178    fn test_sqlite_context() {
179        let mut ctx = SqliteContext::open_in_memory().unwrap();
180        ctx.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
181            .unwrap();
182        ctx.execute("INSERT INTO test (id) VALUES (1)").unwrap();
183
184        let count: i64 = ctx
185            .connection()
186            .query_row("SELECT COUNT(*) FROM test", [], |row| row.get(0))
187            .unwrap();
188        assert_eq!(count, 1);
189    }
190
191    #[test]
192    fn test_full_migration_flow() {
193        let store = SqliteStore::open_in_memory().unwrap();
194        let mut ctx = SqliteContext::open_in_memory().unwrap();
195
196        let mut runner = MigrationRunner::new(store)
197            .add(
198                SqlMigration::new(
199                    1,
200                    "create_users",
201                    "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
202                )
203                .with_down("DROP TABLE users"),
204            )
205            .add(
206                SqlMigration::new(
207                    2,
208                    "add_email",
209                    "ALTER TABLE users ADD COLUMN email TEXT",
210                )
211                .with_down("ALTER TABLE users DROP COLUMN email"),
212            );
213
214        runner.init().unwrap();
215
216        // Check pending
217        let pending = runner.pending().unwrap();
218        assert_eq!(pending.len(), 2);
219
220        // Migrate
221        let count = runner.migrate(&mut ctx).unwrap();
222        assert_eq!(count, 2);
223        assert_eq!(runner.current_version().unwrap(), 2);
224
225        // Verify table exists
226        ctx.execute("INSERT INTO users (name, email) VALUES ('test', 'test@example.com')")
227            .unwrap();
228
229        // Rollback to version 1
230        runner.migrate_to(&mut ctx, 1).unwrap();
231        assert_eq!(runner.current_version().unwrap(), 1);
232
233        // Rollback to version 0
234        runner.migrate_to(&mut ctx, 0).unwrap();
235        assert_eq!(runner.current_version().unwrap(), 0);
236    }
237}
lib_migrations_sql/sqlite.rs

lib_migrations_sql/
sqlite.rs
