// 22_database.ruchy - Database operations and SQL integration
import std::db
import std::sql
fn main() {
println("=== Database Operations ===\n")
// Database connection
println("=== Connecting to Database ===")
let conn = db::connect({
driver: "postgres",
host: "localhost",
port: 5432,
database: "myapp",
user: "dbuser",
password: "secret"
})
// Alternative connection strings
let sqlite_conn = db::connect("sqlite:///path/to/database.db")
let mysql_conn = db::connect("mysql://user:pass@localhost/dbname")
// Basic queries
println("\n=== Basic Queries ===")
// Create table
conn.execute("
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
")
// Insert data
conn.execute("
INSERT INTO users (name, email, age)
VALUES ($1, $2, $3)
", ["Alice", "alice@example.com", 30])
// Parameterized queries to prevent SQL injection
fn insert_user(conn, name, email, age) {
conn.execute("
INSERT INTO users (name, email, age)
VALUES ($1, $2, $3)
", [name, email, age])
}
insert_user(conn, "Bob", "bob@example.com", 25)
insert_user(conn, "Charlie", "charlie@example.com", 35)
// Query data
println("\n=== Querying Data ===")
let users = conn.query("
SELECT id, name, email, age
FROM users
WHERE age > $1
ORDER BY name
", [20])
for user in users {
println(f"User: {user.name} ({user.email}), Age: {user.age}")
}
// Query builder pattern
println("\n=== Query Builder ===")
let query = sql::select("users")
.columns(["id", "name", "email"])
.where("age > ?", 25)
.where("email LIKE ?", "%@example.com")
.order_by("name", "ASC")
.limit(10)
let results = conn.query(query)
println(f"Found {results.len()} users")
// Transactions
println("\n=== Transactions ===")
fn transfer_funds(conn, from_account, to_account, amount) {
let tx = conn.begin_transaction()
try {
// Debit from account
tx.execute("
UPDATE accounts
SET balance = balance - $1
WHERE id = $2 AND balance >= $1
", [amount, from_account])
// Credit to account
tx.execute("
UPDATE accounts
SET balance = balance + $1
WHERE id = $2
", [amount, to_account])
// Commit transaction
tx.commit()
Ok("Transfer successful")
} catch e {
// Rollback on error
tx.rollback()
Err(f"Transfer failed: {e}")
}
}
// Connection pooling
println("\n=== Connection Pool ===")
let pool = db::Pool::new({
driver: "postgres",
host: "localhost",
database: "myapp",
max_connections: 10,
min_connections: 2,
connection_timeout: 5000
})
async fn handle_request(pool) {
let conn = await pool.acquire()
try {
let result = conn.query("SELECT * FROM users LIMIT 1")
println(f"Got user: {result[0].name}")
} finally {
pool.release(conn)
}
}
// ORM-like interface
println("\n=== ORM Interface ===")
#[table("users")]
struct User {
#[primary_key]
id: int,
name: string,
email: string,
age: int,
created_at: datetime
}
impl User {
// Find by ID
fn find(conn, id) {
conn.query_one("
SELECT * FROM users WHERE id = $1
", [id]).map(User::from_row)
}
// Find all
fn all(conn) {
conn.query("SELECT * FROM users")
.map(rows => rows.map(User::from_row))
}
// Find with conditions
fn where(conn, conditions) {
let query = sql::select("users")
for (key, value) in conditions {
query.where(f"{key} = ?", value)
}
conn.query(query).map(rows => rows.map(User::from_row))
}
// Save (insert or update)
fn save(self, conn) {
if self.id == 0 {
// Insert
let result = conn.query_one("
INSERT INTO users (name, email, age)
VALUES ($1, $2, $3)
RETURNING id
", [self.name, self.email, self.age])
self.id = result.id
} else {
// Update
conn.execute("
UPDATE users
SET name = $1, email = $2, age = $3
WHERE id = $4
", [self.name, self.email, self.age, self.id])
}
}
// Delete
fn delete(self, conn) {
conn.execute("DELETE FROM users WHERE id = $1", [self.id])
}
fn from_row(row) {
User {
id: row.id,
name: row.name,
email: row.email,
age: row.age,
created_at: row.created_at
}
}
}
// Using ORM interface
let user = User::find(conn, 1)
match user {
Some(u) => {
println(f"Found user: {u.name}")
u.age += 1
u.save(conn)
},
None => println("User not found")
}
// Migrations
println("\n=== Database Migrations ===")
let migrator = db::Migrator::new(conn, "migrations/")
// Define migrations
migrator.add_migration("001_create_users", {
up: "
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
",
down: "DROP TABLE users;"
})
migrator.add_migration("002_add_age_column", {
up: "ALTER TABLE users ADD COLUMN age INT;",
down: "ALTER TABLE users DROP COLUMN age;"
})
// Run migrations
migrator.migrate() // Run all pending migrations
migrator.rollback() // Rollback last migration
migrator.reset() // Rollback all and re-run
// Prepared statements
println("\n=== Prepared Statements ===")
let stmt = conn.prepare("
SELECT * FROM users
WHERE age BETWEEN $1 AND $2
")
let young_users = stmt.query([18, 25])
let middle_aged = stmt.query([26, 40])
let seniors = stmt.query([60, 100])
// Batch operations
println("\n=== Batch Operations ===")
let users_to_insert = [
{ name: "David", email: "david@example.com", age: 28 },
{ name: "Eve", email: "eve@example.com", age: 31 },
{ name: "Frank", email: "frank@example.com", age: 29 }
]
conn.batch_insert("users", users_to_insert)
// Database introspection
println("\n=== Database Introspection ===")
let tables = conn.list_tables()
println(f"Tables: {tables}")
let columns = conn.list_columns("users")
for col in columns {
println(f"Column: {col.name} ({col.type})")
}
let indexes = conn.list_indexes("users")
println(f"Indexes: {indexes}")
// Backup and restore
println("\n=== Backup and Restore ===")
conn.backup("backup.sql")
println("Database backed up")
// Restore from backup
let new_conn = db::connect("sqlite:///restored.db")
new_conn.restore("backup.sql")
println("Database restored")
// Close connection
conn.close()
}