torii_storage_sqlite/
lib.rs

1//! SQLite storage backend for Torii
2//!
3//! This crate provides a SQLite-based storage implementation for the Torii authentication framework.
4//! It includes implementations for all core storage traits and provides a complete authentication
5//! storage solution using SQLite as the underlying database.
6//!
7//! # Features
8//!
9//! - **User Management**: Store and retrieve user accounts with email verification support
10//! - **Session Management**: Handle user sessions with configurable expiration
11//! - **Password Authentication**: Secure password hashing and verification
12//! - **OAuth Integration**: Store OAuth account connections and tokens
13//! - **Passkey Support**: WebAuthn/FIDO2 passkey storage and challenge management
14//! - **Magic Link Authentication**: Generate and verify magic links for passwordless login
15//! - **Database Migrations**: Automatic schema management and upgrades
16//!
17//! # Usage
18//!
19//! ```rust,no_run
20//! use torii_storage_sqlite::SqliteStorage;
21//! use torii_core::UserId;
22//!
23//! #[tokio::main]
24//! async fn main() -> Result<(), Box<dyn std::error::Error>> {
25//!     // Connect to SQLite database
26//!     let storage = SqliteStorage::connect("sqlite://todos.db?mode=rwc").await?;
27//!     
28//!     // Run migrations to set up the schema
29//!     storage.migrate().await?;
30//!     
31//!     // Use with Torii
32//!     let repositories = std::sync::Arc::new(storage.into_repository_provider());
33//!     let torii = torii::Torii::new(repositories);
34//!     
35//!     Ok(())
36//! }
37//! ```
38//!
39//! # Repository Provider
40//!
41//! The crate provides [`SqliteRepositoryProvider`] which implements the [`RepositoryProvider`] trait
42//! from `torii-core`, allowing it to be used directly with the main Torii authentication coordinator.
43//!
44//! # Storage Implementations
45//!
46//! This crate implements the following storage traits:
47//! - [`UserStorage`](torii_core::storage::UserStorage) - User account management
48//! - [`SessionStorage`](torii_core::storage::SessionStorage) - Session management
49//! - Password repository for secure password storage
50//! - OAuth repository for third-party authentication
51//! - Passkey repository for WebAuthn support
52//!
53//! # Database Schema
54//!
55//! The SQLite schema includes tables for:
56//! - `users` - User accounts and profile information
57//! - `sessions` - Active user sessions
58//! - `passwords` - Hashed password credentials
59//! - `oauth_accounts` - Connected OAuth accounts
60//! - `passkeys` - WebAuthn passkey credentials
61//! - `passkey_challenges` - Temporary passkey challenges
62//!
63//! All tables include appropriate indexes for optimal query performance.
64
65mod migrations;
66mod oauth;
67mod passkey;
68mod password;
69mod repositories;
70mod session;
71
72use async_trait::async_trait;
73use chrono::DateTime;
74use chrono::Utc;
75use migrations::CreateIndexes;
76use migrations::{
77    CreateOAuthAccountsTable, CreatePasskeyChallengesTable, CreatePasskeysTable,
78    CreateSessionsTable, CreateUsersTable, SqliteMigrationManager,
79};
80use sqlx::SqlitePool;
81use torii_core::error::StorageError;
82use torii_core::{
83    User, UserId,
84    storage::{NewUser, UserStorage},
85};
86use torii_migration::{Migration, MigrationManager};
87
88pub use repositories::SqliteRepositoryProvider;
89
90#[derive(Clone)]
91pub struct SqliteStorage {
92    pool: SqlitePool,
93}
94
95impl SqliteStorage {
96    pub fn new(pool: SqlitePool) -> Self {
97        Self { pool }
98    }
99
100    pub async fn connect(database_url: &str) -> Result<Self, StorageError> {
101        let pool = SqlitePool::connect(database_url).await.map_err(|e| {
102            tracing::error!(error = %e, "Failed to connect to database");
103            StorageError::Database("Failed to connect to database".to_string())
104        })?;
105
106        Ok(Self::new(pool))
107    }
108
109    pub async fn migrate(&self) -> Result<(), StorageError> {
110        let manager = SqliteMigrationManager::new(self.pool.clone());
111        manager.initialize().await.map_err(|e| {
112            tracing::error!(error = %e, "Failed to initialize migrations");
113            StorageError::Database("Failed to initialize migrations".to_string())
114        })?;
115
116        let migrations: Vec<Box<dyn Migration<_>>> = vec![
117            Box::new(CreateUsersTable),
118            Box::new(CreateSessionsTable),
119            Box::new(CreateOAuthAccountsTable),
120            Box::new(CreatePasskeysTable),
121            Box::new(CreatePasskeyChallengesTable),
122            Box::new(CreateIndexes),
123        ];
124        manager.up(&migrations).await.map_err(|e| {
125            tracing::error!(error = %e, "Failed to run migrations");
126            StorageError::Database("Failed to run migrations".to_string())
127        })?;
128
129        Ok(())
130    }
131
132    /// Create a repository provider from this storage instance
133    pub fn into_repository_provider(self) -> SqliteRepositoryProvider {
134        SqliteRepositoryProvider::new(self.pool)
135    }
136}
137
138#[derive(Debug, Clone, sqlx::FromRow)]
139pub struct SqliteUser {
140    id: String,
141    email: String,
142    name: Option<String>,
143    email_verified_at: Option<i64>,
144    created_at: i64,
145    updated_at: i64,
146}
147
148impl From<SqliteUser> for User {
149    fn from(user: SqliteUser) -> Self {
150        User::builder()
151            .id(UserId::new(&user.id))
152            .email(user.email)
153            .name(user.name)
154            .email_verified_at(user.email_verified_at.map(|timestamp| {
155                DateTime::from_timestamp(timestamp, 0).expect("Invalid timestamp")
156            }))
157            .created_at(DateTime::from_timestamp(user.created_at, 0).expect("Invalid timestamp"))
158            .updated_at(DateTime::from_timestamp(user.updated_at, 0).expect("Invalid timestamp"))
159            .build()
160            .unwrap()
161    }
162}
163
164impl From<User> for SqliteUser {
165    fn from(user: User) -> Self {
166        SqliteUser {
167            id: user.id.into_inner(),
168            email: user.email,
169            name: user.name,
170            email_verified_at: user
171                .email_verified_at
172                .map(|timestamp| timestamp.timestamp()),
173            created_at: user.created_at.timestamp(),
174            updated_at: user.updated_at.timestamp(),
175        }
176    }
177}
178
179#[async_trait]
180impl UserStorage for SqliteStorage {
181    async fn create_user(&self, user: &NewUser) -> Result<User, torii_core::Error> {
182        let now = Utc::now();
183        let user = sqlx::query_as::<_, SqliteUser>(
184            r#"
185            INSERT INTO users (id, email, name, email_verified_at, created_at, updated_at) 
186            VALUES (?, ?, ?, ?, ?, ?)
187            RETURNING id, email, name, email_verified_at, created_at, updated_at
188            "#,
189        )
190        .bind(user.id.as_str())
191        .bind(&user.email)
192        .bind(&user.name)
193        .bind(
194            user.email_verified_at
195                .map(|timestamp| timestamp.timestamp()),
196        )
197        .bind(now.timestamp())
198        .bind(now.timestamp())
199        .fetch_one(&self.pool)
200        .await
201        .map_err(|e| {
202            tracing::error!(error = %e, "Failed to create user");
203            StorageError::Database("Failed to create user".to_string())
204        })?;
205
206        Ok(user.into())
207    }
208
209    async fn get_user(&self, id: &UserId) -> Result<Option<User>, torii_core::Error> {
210        let user = sqlx::query_as::<_, SqliteUser>(
211            r#"
212            SELECT id, email, name, email_verified_at, created_at, updated_at 
213            FROM users 
214            WHERE id = ?
215            "#,
216        )
217        .bind(id.as_str())
218        .fetch_optional(&self.pool)
219        .await
220        .map_err(|e| {
221            tracing::error!(error = %e, "Failed to get user");
222            StorageError::Database("Failed to get user".to_string())
223        })?;
224
225        if let Some(user) = user {
226            Ok(Some(user.into()))
227        } else {
228            Ok(None)
229        }
230    }
231
232    async fn get_user_by_email(&self, email: &str) -> Result<Option<User>, torii_core::Error> {
233        let user = sqlx::query_as::<_, SqliteUser>(
234            r#"
235            SELECT id, email, name, email_verified_at, created_at, updated_at 
236            FROM users 
237            WHERE email = ?
238            "#,
239        )
240        .bind(email)
241        .fetch_optional(&self.pool)
242        .await
243        .map_err(|e| {
244            tracing::error!(error = %e, "Failed to get user by email");
245            StorageError::Database("Failed to get user by email".to_string())
246        })?;
247
248        if let Some(user) = user {
249            Ok(Some(user.into()))
250        } else {
251            Ok(None)
252        }
253    }
254
255    async fn get_or_create_user_by_email(&self, email: &str) -> Result<User, torii_core::Error> {
256        let user = self.get_user_by_email(email).await?;
257        if let Some(user) = user {
258            return Ok(user);
259        }
260
261        let user = self
262            .create_user(
263                &NewUser::builder()
264                    .id(UserId::new_random())
265                    .email(email.to_string())
266                    .build()
267                    .unwrap(),
268            )
269            .await
270            .map_err(|e| {
271                tracing::error!(error = %e, "Failed to get or create user by email");
272                StorageError::Database("Failed to get or create user by email".to_string())
273            })?;
274
275        Ok(user)
276    }
277
278    async fn update_user(&self, user: &User) -> Result<User, torii_core::Error> {
279        let now = Utc::now();
280        let user = sqlx::query_as::<_, SqliteUser>(
281            r#"
282            UPDATE users 
283            SET email = ?, name = ?, email_verified_at = ?, updated_at = ? 
284            WHERE id = ?
285            RETURNING id, email, name, email_verified_at, created_at, updated_at
286            "#,
287        )
288        .bind(&user.email)
289        .bind(&user.name)
290        .bind(
291            user.email_verified_at
292                .map(|timestamp| timestamp.timestamp()),
293        )
294        .bind(now.timestamp())
295        .bind(user.id.as_str())
296        .fetch_one(&self.pool)
297        .await
298        .map_err(|e| {
299            tracing::error!(error = %e, "Failed to update user");
300            StorageError::Database("Failed to update user".to_string())
301        })?;
302
303        Ok(user.into())
304    }
305
306    async fn delete_user(&self, id: &UserId) -> Result<(), torii_core::Error> {
307        sqlx::query("DELETE FROM users WHERE id = ?")
308            .bind(id.as_str())
309            .execute(&self.pool)
310            .await
311            .map_err(|e| {
312                tracing::error!(error = %e, "Failed to delete user");
313                StorageError::Database("Failed to delete user".to_string())
314            })?;
315
316        Ok(())
317    }
318
319    async fn set_user_email_verified(&self, user_id: &UserId) -> Result<(), torii_core::Error> {
320        sqlx::query("UPDATE users SET email_verified_at = ? WHERE id = ?")
321            .bind(Utc::now().timestamp())
322            .bind(user_id.as_str())
323            .execute(&self.pool)
324            .await
325            .map_err(|e| {
326                tracing::error!(error = %e, "Failed to set user email verified");
327                StorageError::Database("Failed to set user email verified".to_string())
328            })?;
329
330        Ok(())
331    }
332}
333
334#[cfg(test)]
335mod tests {
336    use std::time::Duration;
337
338    use sqlx::{Sqlite, types::chrono::Utc};
339    use torii_migration::{Migration, MigrationManager};
340
341    use super::*;
342    use crate::migrations::{
343        CreateOAuthAccountsTable, CreateSessionsTable, CreateUsersTable, SqliteMigrationManager,
344    };
345    use crate::session::test::create_test_session;
346
347    pub(crate) async fn setup_sqlite_storage() -> Result<SqliteStorage, sqlx::Error> {
348        let _ = tracing_subscriber::fmt().try_init();
349        let pool = SqlitePool::connect("sqlite::memory:").await?;
350        let manager = SqliteMigrationManager::new(pool.clone());
351        manager
352            .initialize()
353            .await
354            .expect("Failed to initialize migrations");
355
356        let migrations: Vec<Box<dyn Migration<Sqlite>>> = vec![
357            Box::new(CreateUsersTable),
358            Box::new(CreateSessionsTable),
359            Box::new(CreateOAuthAccountsTable),
360        ];
361        manager
362            .up(&migrations)
363            .await
364            .expect("Failed to run migrations");
365
366        Ok(SqliteStorage::new(pool))
367    }
368
369    pub(crate) async fn create_test_user(
370        storage: &SqliteStorage,
371        id: &str,
372    ) -> Result<User, torii_core::Error> {
373        storage
374            .create_user(
375                &NewUser::builder()
376                    .id(UserId::new(id))
377                    .email(format!("test{id}@example.com"))
378                    .build()
379                    .expect("Failed to build user"),
380            )
381            .await
382    }
383
384    #[tokio::test]
385    async fn test_sqlite_storage() {
386        let storage = setup_sqlite_storage()
387            .await
388            .expect("Failed to setup storage");
389        let user = create_test_user(&storage, "1")
390            .await
391            .expect("Failed to create user");
392        assert_eq!(user.email, format!("test1@example.com"));
393
394        let fetched = storage
395            .get_user(&UserId::new("1"))
396            .await
397            .expect("Failed to get user");
398        assert_eq!(
399            fetched.expect("User should exist").email,
400            format!("test1@example.com")
401        );
402
403        storage
404            .delete_user(&UserId::new("1"))
405            .await
406            .expect("Failed to delete user");
407        let deleted = storage
408            .get_user(&UserId::new("1"))
409            .await
410            .expect("Failed to get user");
411        assert!(deleted.is_none());
412    }
413
414    #[tokio::test]
415    async fn test_timestamps_are_set_correctly() {
416        let storage = setup_sqlite_storage()
417            .await
418            .expect("Failed to setup storage");
419
420        // Create test user
421        let user = create_test_user(&storage, "1")
422            .await
423            .expect("Failed to create user");
424
425        // Verify user timestamps are set
426        assert!(user.created_at <= Utc::now());
427        assert!(user.updated_at <= Utc::now());
428        assert_eq!(user.created_at, user.updated_at);
429
430        // Create test session
431        let session = create_test_session(&storage, "session1", "1", Duration::from_secs(3600))
432            .await
433            .expect("Failed to create session");
434
435        // Verify session timestamps are set
436        assert!(session.created_at <= Utc::now());
437        assert!(session.updated_at <= Utc::now());
438        assert_eq!(session.created_at, session.updated_at);
439        assert!(session.expires_at > Utc::now());
440
441        // Update user
442
443        tokio::time::sleep(Duration::from_secs(1)).await; // Need to sleep for at least 1 second to ensure the updated_at is different
444
445        let mut updated_user = user.clone();
446        updated_user.name = Some("Test User".to_string());
447        let updated_user = storage
448            .update_user(&updated_user)
449            .await
450            .expect("Failed to update user");
451
452        // Verify updated timestamps
453        assert_eq!(updated_user.created_at, user.created_at);
454        assert!(updated_user.updated_at > user.updated_at);
455    }
456}
torii_storage_sqlite/lib.rs

torii_storage_sqlite/
lib.rs
