bottle_orm/database.rs
1//! # Database Module
2//!
3//! This module provides the core database connection and management functionality for Bottle ORM.
4//! It handles connection pooling, driver detection, table creation, and foreign key management
5//! across multiple database backends.
6//!
7//! ## Supported Database Drivers
8//!
9//! - **PostgreSQL**: Full support with advanced features like UUID, JSONB, arrays
10//! - **MySQL**: Complete support for standard MySQL/MariaDB features
11//! - **SQLite**: In-memory and file-based SQLite databases
12//!
13//! ## Features
14//!
15//! - **Connection Pooling**: Automatic connection pool management via sqlx
16//! - **Driver Detection**: Automatic database driver detection from connection URL
17//! - **Schema Management**: Table creation with indexes, constraints, and foreign keys
18//! - **Type Safety**: Type-safe operations across different database backends
19//!
20//! ## Example Usage
21//!
22//! ```rust,ignore
23//! use bottle_orm::Database;
24//!
25//! // Connect to PostgreSQL
26//! let db = Database::connect("postgres://user:pass@localhost/mydb").await?;
27//!
28//! // Connect to SQLite
29//! let db = Database::connect("sqlite::memory:").await?;
30//!
31//! // Connect to MySQL
32//! let db = Database::connect("mysql://user:pass@localhost/mydb").await?;
33//!
34//! // Create table for a model
35//! db.create_table::<User>().await?;
36//!
37//! // Assign foreign keys
38//! db.assign_foreign_keys::<Post>().await?;
39//!
40//! // Start building queries
41//! let users = db.model::<User>().scan().await?;
42//! ```
43
44// ============================================================================
45// External Crate Imports
46// ============================================================================
47
48use std::time::Duration;
49use heck::ToSnakeCase;
50use sqlx::{any::AnyPoolOptions, AnyPool, Error, Row};
51
52// ============================================================================
53// Internal Crate Imports
54// ============================================================================
55
56use crate::{migration::Migrator, model::Model, query_builder::QueryBuilder, Transaction};
57
58// ============================================================================
59// Database Driver Enumeration
60// ============================================================================
61
62/// Supported database driver types.
63///
64/// This enum represents the different database backends that Bottle ORM can work with.
65/// The driver type is automatically detected from the connection URL and used to
66/// generate appropriate SQL syntax for each database system.
67///
68/// # Variants
69///
70/// * `Postgres` - PostgreSQL database (9.5+)
71/// * `SQLite` - SQLite database (3.x)
72/// * `MySQL` - MySQL or MariaDB database (5.7+/10.2+)
73///
74/// # SQL Dialect Differences
75///
76/// Different drivers use different SQL syntax:
77///
78/// - **Placeholders**:
79/// - PostgreSQL: `$1, $2, $3` (numbered)
80/// - SQLite/MySQL: `?, ?, ?` (positional)
81///
82/// - **Type Casting**:
83/// - PostgreSQL: `$1::UUID`, `$2::TIMESTAMPTZ`
84/// - SQLite/MySQL: Automatic type inference
85///
86/// - **Schema Queries**:
87/// - PostgreSQL: `information_schema` tables
88/// - SQLite: `sqlite_master` system table
89/// - MySQL: `information_schema` tables
90///
91/// # Example
92///
93/// ```rust,ignore
94/// match db.driver {
95/// Drivers::Postgres => println!("Using PostgreSQL"),
96/// Drivers::SQLite => println!("Using SQLite"),
97/// Drivers::MySQL => println!("Using MySQL"),
98/// }
99/// ```
100#[derive(Clone, Debug, Copy)]
101pub enum Drivers {
102 /// PostgreSQL driver.
103 ///
104 /// Used for PostgreSQL databases. Supports advanced features like:
105 /// - UUID native type
106 /// - JSONB for JSON data
107 /// - Array types
108 /// - Full-text search
109 /// - Advanced indexing (GiST, GIN, etc.)
110 Postgres,
111
112 /// SQLite driver.
113 ///
114 /// Used for SQLite databases (both in-memory and file-based). Characteristics:
115 /// - Lightweight and embedded
116 /// - Single-file database
117 /// - Limited concurrent write support
118 /// - Good for development and small applications
119 SQLite,
120
121 /// MySQL driver.
122 ///
123 /// Used for MySQL and MariaDB databases. Features:
124 /// - Wide compatibility
125 /// - Good performance for read-heavy workloads
126 /// - Mature ecosystem
127 /// - ACID compliance (with InnoDB)
128 MySQL,
129}
130
131// ============================================================================
132// Database Builder
133// ============================================================================
134
135/// A builder for creating a `Database` connection with custom options.
136///
137/// Allows configuration of connection pool settings such as maximum connections,
138/// timeouts, and lifetimes.
139///
140/// # Example
141///
142/// ```rust,ignore
143/// let db = Database::builder()
144/// .max_connections(10)
145/// .min_connections(2)
146/// .acquire_timeout(std::time::Duration::from_secs(5))
147/// .connect("postgres://...")
148/// .await?;
149/// ```
150pub struct DatabaseBuilder {
151 options: AnyPoolOptions,
152}
153
154impl DatabaseBuilder {
155 /// Sets the maximum number of connections that this pool should maintain.
156 pub fn max_connections(mut self, max: u32) -> Self {
157 self.options = self.options.max_connections(max);
158 self
159 }
160
161 /// Sets the minimum number of connections that this pool should maintain.
162 pub fn min_connections(mut self, min: u32) -> Self {
163 self.options = self.options.min_connections(min);
164 self
165 }
166
167 /// Sets the maximum amount of time to spend waiting for a connection.
168 pub fn acquire_timeout(mut self, timeout: Duration) -> Self {
169 self.options = self.options.acquire_timeout(timeout);
170 self
171 }
172
173 /// Sets the maximum amount of time a connection may be idle.
174 pub fn idle_timeout(mut self, timeout: Duration) -> Self {
175 self.options = self.options.idle_timeout(Some(timeout));
176 self
177 }
178
179 /// Sets the maximum lifetime of a connection.
180 pub fn max_lifetime(mut self, lifetime: Duration) -> Self {
181 self.options = self.options.max_lifetime(Some(lifetime));
182 self
183 }
184
185 /// Connects to the database using the configured options.
186 pub async fn connect(self, url: &str) -> Result<Database, Error> {
187 // Install default drivers for sqlx::Any
188 sqlx::any::install_default_drivers();
189
190 let pool = self.options.connect(url).await?;
191
192 // Detect driver type from URL scheme
193 let (driver_str, _) = url.split_once(':').unwrap_or(("sqlite", ""));
194 let driver = match driver_str {
195 "postgresql" | "postgres" => Drivers::Postgres,
196 "mysql" => Drivers::MySQL,
197 _ => Drivers::SQLite,
198 };
199
200 Ok(Database { pool, driver })
201 }
202}
203
204// ============================================================================
205// Database Connection and Management
206// ============================================================================
207
208/// The main entry point for database connection and management.
209///
210/// `Database` handles connection pooling, driver detection, and provides methods
211/// for schema operations and query building. It uses sqlx's `AnyPool` to support
212/// multiple database backends with a unified interface.
213///
214/// # Fields
215///
216/// * `pool` - The sqlx connection pool for executing queries
217/// * `driver` - The detected database driver type
218///
219/// # Thread Safety
220///
221/// `Database` implements `Clone` and can be safely shared across threads.
222/// The underlying connection pool is thread-safe and handles connection
223/// distribution automatically.
224///
225/// # Example
226///
227/// ```rust,ignore
228/// use bottle_orm::Database;
229///
230/// #[tokio::main]
231/// async fn main() -> Result<(), Box<dyn std::error::Error>> {
232/// // Connect to database
233/// let db = Database::connect("postgres://localhost/mydb").await?;
234///
235/// // Create migrator
236/// let migrator = db.migrator();
237///
238/// // Build queries
239/// let query = db.model::<User>();
240///
241/// // Database can be cloned and shared
242/// let db_clone = db.clone();
243/// tokio::spawn(async move {
244/// let users = db_clone.model::<User>().scan().await;
245/// });
246///
247/// Ok(())
248/// }
249/// ```
250#[derive(Clone)]
251pub struct Database {
252 /// The sqlx connection pool for executing database queries.
253 ///
254 /// This pool manages a set of database connections that can be reused
255 /// across multiple queries, improving performance by avoiding the overhead
256 /// of creating new connections for each operation.
257 pub(crate) pool: AnyPool,
258
259 /// The detected database driver type.
260 ///
261 /// Used to generate driver-specific SQL syntax (e.g., placeholders,
262 /// type casting, schema queries).
263 pub(crate) driver: Drivers,
264}
265
266// ============================================================================
267// Database Implementation
268// ============================================================================
269
270impl Database {
271 // ========================================================================
272 // Connection Management
273 // ========================================================================
274
275 /// Creates a builder to configure the database connection options.
276 ///
277 /// Returns a `DatabaseBuilder` which allows setting pool options like
278 /// `max_connections`, timeouts, etc.
279 ///
280 /// # Example
281 ///
282 /// ```rust,ignore
283 /// let db = Database::builder()
284 /// .max_connections(20)
285 /// .connect("postgres://...")
286 /// .await?;
287 /// ```
288 pub fn builder() -> DatabaseBuilder {
289 DatabaseBuilder {
290 options: AnyPoolOptions::new(),
291 }
292 }
293
294 /// Connects to the database using a connection string (Database URL).
295 ///
296 /// This method establishes a connection pool to the specified database and
297 /// automatically detects the driver type based on the URL scheme. The connection
298 /// pool is configured with a default maximum of 5 connections.
299 ///
300 /// # Arguments
301 ///
302 /// * `url` - The database connection string with the format:
303 /// `<scheme>://<user>:<password>@<host>:<port>/<database>`
304 ///
305 /// # Supported URL Schemes
306 ///
307 /// - **PostgreSQL**: `postgres://` or `postgresql://`
308 /// - **MySQL**: `mysql://`
309 /// - **SQLite**: `sqlite://` or `sqlite::memory:` (for in-memory databases)
310 ///
311 /// # Connection Pool Configuration
312 ///
313 /// - Maximum connections: 5
314 /// - Connection timeout: Default (30 seconds)
315 /// - Idle timeout: Default (10 minutes)
316 ///
317 /// # Returns
318 ///
319 /// * `Ok(Database)` - Successfully connected database instance
320 /// * `Err(sqlx::Error)` - Connection error (invalid URL, authentication failure, etc.)
321 ///
322 /// # Examples
323 ///
324 /// ```rust,ignore
325 /// // PostgreSQL connection
326 /// let db = Database::connect("postgres://user:password@localhost:5432/mydb").await?;
327 ///
328 /// // PostgreSQL with SSL
329 /// let db = Database::connect("postgres://user:password@localhost/mydb?sslmode=require").await?;
330 ///
331 /// // SQLite in-memory database (great for testing)
332 /// let db = Database::connect("sqlite::memory:").await?;
333 ///
334 /// // SQLite file-based database
335 /// let db = Database::connect("sqlite://./database.db").await?;
336 ///
337 /// // MySQL connection
338 /// let db = Database::connect("mysql://user:password@localhost:3306/mydb").await?;
339 /// ```
340 ///
341 /// # Error Handling
342 ///
343 /// ```rust,ignore
344 /// match Database::connect("postgres://localhost/mydb").await {
345 /// Ok(db) => println!("Connected successfully"),
346 /// Err(e) => eprintln!("Connection failed: {}", e),
347 /// }
348 /// ```
349 pub async fn connect(url: &str) -> Result<Self, Error> {
350 Self::builder().max_connections(5).connect(url).await
351 }
352
353 // ========================================================================
354 // Schema Management
355 // ========================================================================
356
357 /// Creates a `Migrator` instance to manage schema migrations.
358 ///
359 /// The migrator allows you to register multiple models and execute
360 /// all necessary schema changes (table creation, foreign keys) in the
361 /// correct order.
362 ///
363 /// # Returns
364 ///
365 /// A new `Migrator` instance associated with this database connection
366 ///
367 /// # Example
368 ///
369 /// ```rust,ignore
370 /// let db = Database::connect("sqlite::memory:").await?;
371 ///
372 /// db.migrator()
373 /// .register::<User>()
374 /// .register::<Post>()
375 /// .register::<Comment>()
376 /// .run()
377 /// .await?;
378 /// ```
379 ///
380 /// # See Also
381 ///
382 /// * [`Migrator`] - For detailed migration documentation
383 /// * [`Migrator::register()`] - For registering models
384 /// * [`Migrator::run()`] - For executing migrations
385 pub fn migrator(&self) -> Migrator<'_> {
386 Migrator::new(self)
387 }
388
389 // ========================================================================
390 // Query Building
391 // ========================================================================
392
393 /// Starts building a query for a specific Model.
394 ///
395 /// This method creates a new `QueryBuilder` instance configured for the
396 /// specified model type. The query builder provides a fluent interface
397 /// for constructing SELECT and INSERT queries.
398 ///
399 /// # Type Parameters
400 ///
401 /// * `T` - The Model type to query. Must implement `Model + Send + Sync + Unpin`
402 ///
403 /// # Returns
404 ///
405 /// A new `QueryBuilder` instance ready for query construction
406 ///
407 /// # Example
408 ///
409 /// ```rust,ignore
410 /// // Simple query
411 /// let users: Vec<User> = db.model::<User>().scan().await?;
412 ///
413 /// // Filtered query
414 /// let adults: Vec<User> = db.model::<User>()
415 /// .filter("age", ">=", 18)
416 /// .scan()
417 /// .await?;
418 ///
419 /// // Insert operation
420 /// let new_user = User { /* ... */ };
421 /// db.model::<User>().insert(&new_user).await?;
422 /// ```
423 ///
424 /// # See Also
425 ///
426 /// * [`QueryBuilder`] - For detailed query building documentation
427 /// * [`QueryBuilder::filter()`] - For adding WHERE clauses
428 /// * [`QueryBuilder::scan()`] - For executing SELECT queries
429 /// * [`QueryBuilder::insert()`] - For INSERT operations
430 pub fn model<T: Model + Send + Sync + Unpin>(&self) -> QueryBuilder<'_, T, Self> {
431 // Get active column names from the model
432 let active_columns = T::active_columns();
433 let mut columns: Vec<String> = Vec::with_capacity(active_columns.capacity());
434
435 // Convert column names to snake_case and strip 'r#' prefix if present
436 for col in active_columns {
437 columns.push(col.strip_prefix("r#").unwrap_or(col).to_snake_case());
438 }
439
440 // Create and return the query builder
441 QueryBuilder::new(self.clone(), self.driver, T::table_name(), T::columns(), columns)
442 }
443
444 // ========================================================================
445 // Table Creation
446 // ========================================================================
447
448 /// Creates the table for model `T` if it does not exist.
449 ///
450 /// This method generates and executes SQL to create a table based on the
451 /// model's structure. It handles column definitions, primary keys, unique
452 /// constraints, default values, and indexes.
453 ///
454 /// # Type Parameters
455 ///
456 /// * `T` - The Model type representing the table
457 ///
458 /// # Returns
459 ///
460 /// * `Ok(&Self)` - Reference to self for method chaining
461 /// * `Err(sqlx::Error)` - Database error during table creation
462 ///
463 /// # Generated SQL Features
464 ///
465 /// - **Primary Keys**: Automatically marked with `PRIMARY KEY`
466 /// - **NOT NULL**: Non-nullable fields get `NOT NULL` constraint
467 /// - **UNIQUE**: Fields marked with `#[orm(unique)]` get `UNIQUE` constraint
468 /// - **DEFAULT**: Fields marked with `#[orm(create_time)]` get `DEFAULT CURRENT_TIMESTAMP`
469 /// - **Indexes**: Fields marked with `#[orm(index)]` get database indexes
470 ///
471 /// # Example
472 ///
473 /// ```rust,ignore
474 /// use bottle_orm::Model;
475 /// use uuid::Uuid;
476 /// use chrono::{DateTime, Utc};
477 ///
478 /// #[derive(Model)]
479 /// struct User {
480 /// #[orm(primary_key)]
481 /// id: Uuid,
482 /// #[orm(size = 50, unique)]
483 /// username: String,
484 /// #[orm(index)]
485 /// email: String,
486 /// age: i32,
487 /// #[orm(create_time)]
488 /// created_at: DateTime<Utc>,
489 /// }
490 ///
491 /// // Creates table with:
492 /// // - UUID primary key
493 /// // - Unique username constraint
494 /// // - Index on email
495 /// // - created_at with DEFAULT CURRENT_TIMESTAMP
496 /// db.create_table::<User>().await?;
497 /// ```
498 ///
499 /// # Generated SQL Example (PostgreSQL)
500 ///
501 /// ```sql
502 /// CREATE TABLE IF NOT EXISTS "user" (
503 /// "id" UUID PRIMARY KEY,
504 /// "username" VARCHAR(50) NOT NULL UNIQUE,
505 /// "email" TEXT NOT NULL,
506 /// "age" INTEGER NOT NULL,
507 /// "created_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
508 /// );
509 /// CREATE INDEX IF NOT EXISTS "idx_user_email" ON "user" ("email");
510 /// ```
511 pub async fn create_table<T: Model>(&self) -> Result<&Self, Error> {
512 // Get table name in snake_case format
513 let table_name = T::table_name().to_snake_case();
514 let columns = T::columns();
515
516 let mut column_defs = Vec::new();
517 let mut index_statements = Vec::new();
518
519 // Build column definitions
520 for col in &columns {
521 // Strip 'r#' prefix if present (for Rust keywords used as column names)
522 let col_name = col.name.strip_prefix("r#").unwrap_or(col.name).to_snake_case();
523 let mut def = format!("\"{}\" {}", col_name, col.sql_type);
524
525 // Add PRIMARY KEY constraint
526 if col.is_primary_key {
527 def.push_str(" PRIMARY KEY");
528 }
529
530 // Add NOT NULL constraint (except for primary keys, which are implicitly NOT NULL)
531 if !col.is_nullable && !col.is_primary_key {
532 def.push_str(" NOT NULL");
533 }
534
535 // Add DEFAULT CURRENT_TIMESTAMP for create_time fields
536 if col.create_time {
537 def.push_str(" DEFAULT CURRENT_TIMESTAMP");
538 }
539
540 // Add UNIQUE constraint
541 if col.unique {
542 def.push_str(" UNIQUE");
543 }
544
545 column_defs.push(def);
546
547 // Generate index creation statement if needed
548 if col.index {
549 let index_type = if col.unique { "UNIQUE INDEX" } else { "INDEX" };
550 let index_name = format!("idx_{}_{}", table_name, col_name);
551
552 let index_query = format!(
553 "CREATE {} IF NOT EXISTS \"{}\" ON \"{}\" (\"{}\")",
554 index_type, index_name, table_name, col_name,
555 );
556
557 index_statements.push(index_query);
558 }
559 }
560
561 // Add SQLite Foreign Keys inline (SQLite doesn't support ADD CONSTRAINT)
562 if let Drivers::SQLite = self.driver {
563 for col in &columns {
564 if let (Some(f_table), Some(f_key)) = (col.foreign_table, col.foreign_key) {
565 let col_name = col.name.strip_prefix("r#").unwrap_or(col.name).to_snake_case();
566 let f_table_clean = f_table.to_snake_case();
567 let f_key_clean = f_key.to_snake_case();
568
569 let fk_def = format!(
570 "FOREIGN KEY (\"{}\") REFERENCES \"{}\" (\"{}\")",
571 col_name, f_table_clean, f_key_clean
572 );
573 column_defs.push(fk_def);
574 }
575 }
576 }
577
578 // Build and execute CREATE TABLE statement
579 let create_table_query =
580 format!("CREATE TABLE IF NOT EXISTS \"{}\" ({})", table_name.to_snake_case(), column_defs.join(", "));
581 log::info!("{}", create_table_query);
582
583 sqlx::query(&create_table_query).execute(&self.pool).await?;
584
585 // Create indexes
586 for idx_stmt in index_statements {
587 sqlx::query(&idx_stmt).execute(&self.pool).await?;
588 }
589
590 Ok(self)
591 }
592
593 /// Starts a new database transaction.
594 ///
595 /// Returns a `Transaction` wrapper that can be used to execute multiple
596 /// queries atomically. The transaction must be explicitly committed
597 /// using `commit()`, otherwise it will be rolled back when dropped.
598 ///
599 /// # Returns
600 ///
601 /// * `Ok(Transaction)` - A new transaction instance
602 /// * `Err(sqlx::Error)` - Database error starting transaction
603 ///
604 /// # Example
605 ///
606 /// ```rust,ignore
607 /// let mut tx = db.begin().await?;
608 /// // ... perform operations ...
609 /// tx.commit().await?;
610 /// ```
611 pub async fn begin<'a>(&self) -> Result<Transaction<'a>, sqlx::Error> {
612 let tx = self.pool.begin().await?;
613 Ok(Transaction { tx, driver: self.driver })
614 }
615
616 // ========================================================================
617 // Foreign Key Management
618 // ========================================================================
619
620 /// Checks for and assigns Foreign Keys for model `T`.
621 ///
622 /// This method examines all columns marked with `#[orm(foreign_key = "Table::Column")]`
623 /// and creates the appropriate foreign key constraints. It verifies that constraints
624 /// don't already exist before attempting to create them, preventing duplication errors.
625 ///
626 /// # Type Parameters
627 ///
628 /// * `T` - The Model type to process for foreign keys
629 ///
630 /// # Returns
631 ///
632 /// * `Ok(&Self)` - Reference to self for method chaining
633 /// * `Err(sqlx::Error)` - Database error during foreign key creation
634 ///
635 /// # Constraint Naming
636 ///
637 /// Foreign key constraints are named using the pattern:
638 /// `fk_{table_name}_{column_name}`
639 ///
640 /// # Example
641 ///
642 /// ```rust,ignore
643 /// use bottle_orm::Model;
644 /// use uuid::Uuid;
645 ///
646 /// #[derive(Model)]
647 /// struct User {
648 /// #[orm(primary_key)]
649 /// id: Uuid,
650 /// username: String,
651 /// }
652 ///
653 /// #[derive(Model)]
654 /// struct Post {
655 /// #[orm(primary_key)]
656 /// id: Uuid,
657 /// #[orm(foreign_key = "User::id")]
658 /// user_id: Uuid,
659 /// title: String,
660 /// }
661 ///
662 /// // Create tables first
663 /// db.create_table::<User>().await?;
664 /// db.create_table::<Post>().await?;
665 ///
666 /// // Then assign foreign keys
667 /// db.assign_foreign_keys::<Post>().await?;
668 /// ```
669 ///
670 /// # Generated SQL Example
671 ///
672 /// ```sql
673 /// ALTER TABLE "post"
674 /// ADD CONSTRAINT "fk_post_user_id"
675 /// FOREIGN KEY ("user_id")
676 /// REFERENCES "user" ("id");
677 /// ```
678 ///
679 /// # Important Notes
680 ///
681 /// - Foreign key assignment should be done **after** all tables are created
682 /// - The referenced table and column must exist before creating the foreign key
683 /// - Use the `Migrator` to handle the correct order automatically
684 /// - Currently optimized for PostgreSQL (uses `information_schema`)
685 ///
686 /// # See Also
687 ///
688 /// * [`Migrator`] - For automatic migration order management
689 pub async fn assign_foreign_keys<T: Model>(&self) -> Result<&Self, Error> {
690 // SQLite handles FKs in create_table, so we skip here
691 if let Drivers::SQLite = self.driver {
692 return Ok(self);
693 }
694
695 // Get table name in snake_case format
696 let table_name = T::table_name().to_snake_case();
697 let columns = T::columns();
698
699 // Process each column that has a foreign key definition
700 for col in columns {
701 if let (Some(f_table), Some(f_key)) = (col.foreign_table, col.foreign_key) {
702 // Clean up column and reference names
703 let col_name = col.name.strip_prefix("r#").unwrap_or(col.name).to_snake_case();
704 let f_table_clean = f_table.to_snake_case();
705 let f_key_clean = f_key.to_snake_case();
706
707 // Generate constraint name
708 let constraint_name = format!("fk_{}_{}", table_name, col_name);
709
710 // Check if constraint already exists
711 let count: i64 = match self.driver {
712 Drivers::Postgres => {
713 let check_query = "SELECT count(*) FROM information_schema.table_constraints WHERE constraint_name = $1";
714 let row = sqlx::query(check_query)
715 .bind(&constraint_name)
716 .fetch_one(&self.pool)
717 .await?;
718 row.try_get(0).unwrap_or(0)
719 }
720 Drivers::MySQL => {
721 let check_query = "SELECT count(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ? AND TABLE_SCHEMA = DATABASE()";
722 let row = sqlx::query(check_query)
723 .bind(&constraint_name)
724 .fetch_one(&self.pool)
725 .await?;
726 row.try_get(0).unwrap_or(0)
727 }
728 Drivers::SQLite => 0, // Unreachable
729 };
730
731 // Skip if constraint already exists
732 if count > 0 {
733 continue;
734 }
735
736 // Create foreign key constraint
737 let alter_query = format!(
738 "ALTER TABLE \"{}\" ADD CONSTRAINT \"{}\" FOREIGN KEY (\"{}\") REFERENCES \"{}\" (\"{}\")",
739 table_name, constraint_name, col_name, f_table_clean, f_key_clean
740 );
741
742 sqlx::query(&alter_query).execute(&self.pool).await?;
743 }
744 }
745
746 Ok(self)
747 }
748}
749
750/// A trait representing a database connection or transaction.
751///
752/// This trait abstracts over `Database` (pool) and `Transaction` types, allowing
753/// the `QueryBuilder` to work seamlessly with both. It uses Generic Associated Types (GATs)
754/// to handle the lifetimes of the executor references correctly.
755pub trait Connection {
756 /// The type of the executor returned by this connection.
757 ///
758 /// This uses GATs to bind the lifetime of the executor (`'c`) to the lifetime
759 /// of the borrow of the connection (`&'c mut self`).
760 type Exec<'c>: sqlx::Executor<'c, Database = sqlx::Any>
761 where
762 Self: 'c;
763
764 /// Returns a mutable reference to the SQLx executor.
765 ///
766 /// # Returns
767 ///
768 /// An executor capable of running SQL queries (either a Pool or a Transaction).
769 fn executor<'c>(&'c mut self) -> Self::Exec<'c>;
770}
771
772/// Implementation of Connection for the main Database struct.
773///
774/// Uses the internal connection pool to execute queries.
775impl Connection for Database {
776 type Exec<'c> = &'c sqlx::Pool<sqlx::Any>;
777
778 fn executor<'c>(&'c mut self) -> Self::Exec<'c> {
779 &self.pool
780 }
781}
782
783/// Implementation of Connection for a mutable reference to Database.
784impl<'a> Connection for &'a mut Database {
785 type Exec<'c> = &'c sqlx::Pool<sqlx::Any>
786 where
787 Self: 'c;
788
789 fn executor<'c>(&'c mut self) -> Self::Exec<'c> {
790 &self.pool
791 }
792}
793
794/// Implementation of Connection for a mutable reference to sqlx::Transaction.
795impl<'a> Connection for &mut sqlx::Transaction<'a, sqlx::Any> {
796 type Exec<'c> = &'c mut sqlx::AnyConnection
797 where
798 Self: 'c;
799
800 fn executor<'c>(&'c mut self) -> Self::Exec<'c> {
801 &mut **self
802 }
803}