import SQLite

let db = Database("path/to/db.sqlite3")

db.execute(
    "CREATE TABLE users (" +
        "id INTEGER PRIMARY KEY, " +
        "email TEXT NOT NULL UNIQUE, " +
        "manager_id INTEGER, " +
        "FOREIGN KEY(manager_id) REFERENCES users(id)" +
    ")"
)

let stmt = db.prepare("INSERT INTO users (email) VALUES (?)")
for email in ["[email protected]", "[email protected]"] {
    stmt.run(email)
}

db.totalChanges // 2
db.lastChanges  // {Some 1}
db.lastID       // {Some 2}

for row in db.prepare("SELECT id, email FROM users") {
    println("id: \(row[0]), email: \(row[1])")
    // id: Optional(1), email: Optional("[email protected]")
    // id: Optional(2), email: Optional("[email protected]")
}

db.scalar("SELECT count(*) FROM users") // {Some 2}

let jr = db.prepare("INSERT INTO users (email, manager_id) VALUES (?, ?)")
db.transaction(
    stmt.run("[email protected]"),
    jr.run("[email protected]", db.lastID)
)

let users = db["users"]
let email = Expression<String>("email")
let admin = Expression<Bool>("admin")
let age = Expression<Int>("age")

for user in users.filter(admin && age >= 30).order(age.desc) { /* ... */ }
// SELECT * FROM users WHERE (admin) AND (age >= 30) ORDER BY age DESC

for user in users.group(age, having: count(age) == 1) { /* ... */ }
// SELECT * FROM users GROUP BY age HAVING count(age) = 1

users.count
// SELECT count(*) FROM users

users.filter(admin).average(age)
// SELECT average(age) FROM users WHERE admin

if let id = users.insert(email <- "[email protected]") { /* ... */ }
// INSERT INTO users (email) VALUES ('[email protected]')

let ageless = users.filter(admin && age == nil)
let updates: Int = ageless.update(admin <- false)
// UPDATE users SET admin = 0 WHERE (admin) AND (age IS NULL)