← Back to swift ORM Libraries

SQLite.swift

SQLite.swift is developed as "A type-safe, Swift-language layer over SQLite3" - a Swift-exclusive SQLite database access library. It functions as a thin wrapper over SQLite3, providing database operations leveraging Swift's type safety and expressive syntax. Supporting all Apple platforms including iOS, macOS, watchOS, and tvOS, it is widely adopted in mobile app development and Mac application development as a lightweight alternative to heavyweight frameworks like Core Data and Realm.

SwiftSQLiteDatabaseType-safeiOSmacOSQuery Builder

GitHub Overview

stephencelis/SQLite.swift

A type-safe, Swift-language layer over SQLite3.

Stars9,973
Watchers217
Forks1,607
Created:October 4, 2014
Language:Swift
License:MIT License

Topics

sqliteswift

Star History

stephencelis/SQLite.swift Star History
Data as of: 7/19/2025, 10:28 AM

Library

SQLite.swift

Overview

SQLite.swift is developed as "A type-safe, Swift-language layer over SQLite3" - a Swift-exclusive SQLite database access library. It functions as a thin wrapper over SQLite3, providing database operations leveraging Swift's type safety and expressive syntax. Supporting all Apple platforms including iOS, macOS, watchOS, and tvOS, it is widely adopted in mobile app development and Mac application development as a lightweight alternative to heavyweight frameworks like Core Data and Realm.

Details

SQLite.swift 2025 edition leverages the latest Swift 5.9+ features (async/await, Property Wrappers, Result Builder, etc.) and is optimized for modern Swift development paradigms. It comprehensively supports type-safe query builders, prepared statements, transaction management, and migration functionality, providing the reliability and performance necessary for enterprise-level iOS app development. Natural integration with SwiftUI, Combine support, and complete Swift Package Manager compatibility enable seamless integration into modern iOS development workflows.

Key Features

  • Type Safety: Completely type-safe query construction leveraging Swift's type system
  • Lightweight Design: Minimal overhead as a thin wrapper over SQLite3
  • Full Platform Support: iOS, macOS, watchOS, tvOS support
  • Modern Swift: Support for latest features like async/await, Property Wrappers
  • Query Builder: Intuitive and readable query construction API
  • Migration: Automatic management of database schema changes

Pros and Cons

Pros

  • Retains SQLite3's high performance while enjoying Swift's type safety
  • Lightweight with lower learning costs compared to Core Data or Realm
  • High transparency in database operations with SQL-like descriptions
  • Consistent development experience across all Apple platforms
  • Easy introduction and version management through Swift Package Manager
  • High security through prepared statements

Cons

  • Swift/Apple platform exclusive with no cross-platform support
  • No advanced object mapping features like Core Data
  • Complex relationship management requires manual implementation
  • Performance optimization for large datasets depends on developer
  • Background processing and multi-threading require careful attention
  • NoSQL-like flexibility depends on SQLite constraints

Reference Pages

Code Examples

Setup

// Package.swift
// swift-tools-version: 5.9
import PackageDescription

let package = Package(
    name: "SQLiteSwiftExample",
    platforms: [
        .iOS(.v15),
        .macOS(.v12),
        .watchOS(.v8),
        .tvOS(.v15)
    ],
    dependencies: [
        .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.15.3")
    ],
    targets: [
        .target(
            name: "SQLiteSwiftExample",
            dependencies: [
                .product(name: "SQLite", package: "SQLite.swift")
            ]
        ),
        .testTarget(
            name: "SQLiteSwiftExampleTests",
            dependencies: ["SQLiteSwiftExample"]
        )
    ]
)

// For iOS Xcode projects
// File -> Add Package Dependencies -> https://github.com/stephencelis/SQLite.swift.git

import SQLite
import Foundation

Basic Usage

import SQLite
import Foundation

// Database connection and table definition
class UserDatabase {
    private var db: Connection?
    
    // Table definition
    private let users = Table("users")
    private let id = Expression<Int64>("id")
    private let name = Expression<String>("name")
    private let email = Expression<String>("email")
    private let age = Expression<Int>("age")
    private let isActive = Expression<Bool>("is_active")
    private let createdAt = Expression<Date>("created_at")
    private let profile = Expression<String?>("profile") // Store as JSON string
    
    // Posts table
    private let posts = Table("posts")
    private let postId = Expression<Int64>("post_id")
    private let userId = Expression<Int64>("user_id")
    private let title = Expression<String>("title")
    private let content = Expression<String>("content")
    private let published = Expression<Bool>("published")
    private let publishedAt = Expression<Date?>("published_at")
    private let viewCount = Expression<Int>("view_count")
    
    init() {
        do {
            let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
            let dbPath = "\(path)/userdb.sqlite3"
            db = try Connection(dbPath)
            print("Database connected at: \(dbPath)")
            createTables()
        } catch {
            print("Database connection failed: \(error)")
        }
    }
    
    // Create tables
    private func createTables() {
        do {
            // Create users table
            try db?.run(users.create(ifNotExists: true) { table in
                table.column(id, primaryKey: .autoincrement)
                table.column(name)
                table.column(email, unique: true)
                table.column(age)
                table.column(isActive, defaultValue: true)
                table.column(createdAt, defaultValue: Date())
                table.column(profile)
            })
            
            // Create posts table
            try db?.run(posts.create(ifNotExists: true) { table in
                table.column(postId, primaryKey: .autoincrement)
                table.column(userId)
                table.column(title)
                table.column(content)
                table.column(published, defaultValue: false)
                table.column(publishedAt)
                table.column(viewCount, defaultValue: 0)
                table.foreignKey(userId, references: users, id, delete: .cascade)
            })
            
            // Create indexes
            try db?.run(users.createIndex(email, ifNotExists: true))
            try db?.run(posts.createIndex(userId, ifNotExists: true))
            try db?.run(posts.createIndex(published, ifNotExists: true))
            
            print("Tables created successfully")
            
        } catch {
            print("Create table error: \(error)")
        }
    }
}

// User model
struct User {
    let id: Int64?
    let name: String
    let email: String
    let age: Int
    let isActive: Bool
    let createdAt: Date
    let profile: UserProfile?
    
    init(id: Int64? = nil, name: String, email: String, age: Int, isActive: Bool = true, createdAt: Date = Date(), profile: UserProfile? = nil) {
        self.id = id
        self.name = name
        self.email = email
        self.age = age
        self.isActive = isActive
        self.createdAt = createdAt
        self.profile = profile
    }
}

// User profile (JSON)
struct UserProfile: Codable {
    let bio: String?
    let website: String?
    let location: String?
    let skills: [String]
    
    init(bio: String? = nil, website: String? = nil, location: String? = nil, skills: [String] = []) {
        self.bio = bio
        self.website = website
        self.location = location
        self.skills = skills
    }
}

// Post model
struct Post {
    let id: Int64?
    let userId: Int64
    let title: String
    let content: String
    let published: Bool
    let publishedAt: Date?
    let viewCount: Int
    
    init(id: Int64? = nil, userId: Int64, title: String, content: String, published: Bool = false, publishedAt: Date? = nil, viewCount: Int = 0) {
        self.id = id
        self.userId = userId
        self.title = title
        self.content = content
        self.published = published
        self.publishedAt = publishedAt
        self.viewCount = viewCount
    }
}

// User service extension
extension UserDatabase {
    
    // Create user
    func createUser(_ user: User) throws -> Int64 {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let profileJSON = try user.profile?.toJSONString()
        
        let insert = users.insert(
            name <- user.name,
            email <- user.email,
            age <- user.age,
            isActive <- user.isActive,
            createdAt <- user.createdAt,
            profile <- profileJSON
        )
        
        let rowid = try db.run(insert)
        print("Created user with ID: \(rowid)")
        return rowid
    }
    
    // Get all users
    func getAllUsers() throws -> [User] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var result: [User] = []
        
        for user in try db.prepare(users.order(createdAt.desc)) {
            let userProfile = try? UserProfile.fromJSONString(user[profile])
            
            result.append(User(
                id: user[id],
                name: user[name],
                email: user[email],
                age: user[age],
                isActive: user[isActive],
                createdAt: user[createdAt],
                profile: userProfile
            ))
        }
        
        return result
    }
    
    // Get user by ID
    func getUserById(_ userId: Int64) throws -> User? {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = users.filter(id == userId)
        
        guard let user = try db.pluck(query) else { return nil }
        
        let userProfile = try? UserProfile.fromJSONString(user[profile])
        
        return User(
            id: user[id],
            name: user[name],
            email: user[email],
            age: user[age],
            isActive: user[isActive],
            createdAt: user[createdAt],
            profile: userProfile
        )
    }
    
    // Search user by email
    func getUserByEmail(_ userEmail: String) throws -> User? {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = users.filter(email == userEmail)
        
        guard let user = try db.pluck(query) else { return nil }
        
        let userProfile = try? UserProfile.fromJSONString(user[profile])
        
        return User(
            id: user[id],
            name: user[name],
            email: user[email],
            age: user[age],
            isActive: user[isActive],
            createdAt: user[createdAt],
            profile: userProfile
        )
    }
    
    // Update user
    func updateUser(_ userId: Int64, name: String, email: String, age: Int, profile: UserProfile?) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let userToUpdate = users.filter(id == userId)
        let profileJSON = try profile?.toJSONString()
        
        let update = userToUpdate.update(
            self.name <- name,
            self.email <- email,
            self.age <- age,
            self.profile <- profileJSON
        )
        
        let changes = try db.run(update)
        return changes > 0
    }
    
    // Change user active status
    func setUserActive(_ userId: Int64, active: Bool) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let userToUpdate = users.filter(id == userId)
        let update = userToUpdate.update(isActive <- active)
        
        let changes = try db.run(update)
        return changes > 0
    }
    
    // Delete user
    func deleteUser(_ userId: Int64) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let userToDelete = users.filter(id == userId)
        let changes = try db.run(userToDelete.delete())
        
        return changes > 0
    }
    
    // Search by name
    func searchUsersByName(_ searchTerm: String) throws -> [User] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = users.filter(name.like("%\(searchTerm)%")).order(name)
        var result: [User] = []
        
        for user in try db.prepare(query) {
            let userProfile = try? UserProfile.fromJSONString(user[profile])
            
            result.append(User(
                id: user[id],
                name: user[name],
                email: user[email],
                age: user[age],
                isActive: user[isActive],
                createdAt: user[createdAt],
                profile: userProfile
            ))
        }
        
        return result
    }
    
    // Search by age range
    func getUsersByAgeRange(min: Int, max: Int) throws -> [User] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = users.filter(age >= min && age <= max).order(age, name)
        var result: [User] = []
        
        for user in try db.prepare(query) {
            let userProfile = try? UserProfile.fromJSONString(user[profile])
            
            result.append(User(
                id: user[id],
                name: user[name],
                email: user[email],
                age: user[age],
                isActive: user[isActive],
                createdAt: user[createdAt],
                profile: userProfile
            ))
        }
        
        return result
    }
    
    // Count active users
    func getActiveUserCount() throws -> Int {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        return try db.scalar(users.filter(isActive == true).count)
    }
    
    // Total user count
    func getTotalUserCount() throws -> Int {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        return try db.scalar(users.count)
    }
}

// Usage example
func demonstrateBasicOperations() {
    let database = UserDatabase()
    
    do {
        // Create users
        let profile1 = UserProfile(bio: "iOS Developer", website: "https://example.com", location: "Tokyo", skills: ["Swift", "iOS", "SwiftUI"])
        let user1 = User(name: "Alice Johnson", email: "[email protected]", age: 28, profile: profile1)
        let userId1 = try database.createUser(user1)
        
        let profile2 = UserProfile(bio: "Backend Developer", location: "Osaka", skills: ["Swift", "Vapor", "PostgreSQL"])
        let user2 = User(name: "Bob Smith", email: "[email protected]", age: 32, profile: profile2)
        let userId2 = try database.createUser(user2)
        
        let user3 = User(name: "Charlie Brown", email: "[email protected]", age: 25)
        let userId3 = try database.createUser(user3)
        
        print("Created users: \(userId1), \(userId2), \(userId3)")
        
        // Get all users
        let allUsers = try database.getAllUsers()
        print("\n=== All Users ===")
        for user in allUsers {
            print("ID: \(user.id ?? 0), Name: \(user.name), Email: \(user.email), Age: \(user.age)")
            if let profile = user.profile {
                print("  Bio: \(profile.bio ?? "N/A"), Skills: \(profile.skills.joined(separator: ", "))")
            }
        }
        
        // Get specific user
        if let retrievedUser = try database.getUserById(userId1) {
            print("\n=== Retrieved User ===")
            print("Retrieved: \(retrievedUser.name) (\(retrievedUser.email))")
        }
        
        // Update user
        let updatedProfile = UserProfile(bio: "Senior iOS Developer", website: "https://alice-dev.com", location: "Tokyo", skills: ["Swift", "iOS", "SwiftUI", "Combine"])
        let updated = try database.updateUser(userId1, name: "Alice Johnson Smith", email: "[email protected]", age: 29, profile: updatedProfile)
        if updated {
            print("\n=== User Updated ===")
            if let updatedUser = try database.getUserById(userId1) {
                print("Updated: \(updatedUser.name) (\(updatedUser.email))")
            }
        }
        
        // Name search
        let searchResults = try database.searchUsersByName("Alice")
        print("\n=== Search Results ===")
        for user in searchResults {
            print("Found: \(user.name) (\(user.email))")
        }
        
        // Age range search
        let ageRangeUsers = try database.getUsersByAgeRange(min: 25, max: 30)
        print("\n=== Users aged 25-30 ===")
        for user in ageRangeUsers {
            print("\(user.name): \(user.age) years old")
        }
        
        // Statistics
        let totalUsers = try database.getTotalUserCount()
        let activeUsers = try database.getActiveUserCount()
        print("\n=== Statistics ===")
        print("Total users: \(totalUsers)")
        print("Active users: \(activeUsers)")
        
    } catch {
        print("Error: \(error)")
    }
}

Post Functionality and JOIN Operations

// Post service extension
extension UserDatabase {
    
    // Create post
    func createPost(_ post: Post) throws -> Int64 {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let insert = posts.insert(
            userId <- post.userId,
            title <- post.title,
            content <- post.content,
            published <- post.published,
            publishedAt <- post.publishedAt,
            viewCount <- post.viewCount
        )
        
        let rowid = try db.run(insert)
        print("Created post with ID: \(rowid)")
        return rowid
    }
    
    // Get post
    func getPostById(_ postIdValue: Int64) throws -> Post? {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts.filter(postId == postIdValue)
        
        guard let post = try db.pluck(query) else { return nil }
        
        return Post(
            id: post[postId],
            userId: post[userId],
            title: post[title],
            content: post[content],
            published: post[published],
            publishedAt: post[publishedAt],
            viewCount: post[viewCount]
        )
    }
    
    // Get user's posts
    func getPostsByUser(_ userIdValue: Int64) throws -> [Post] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts.filter(userId == userIdValue).order(postId.desc)
        var result: [Post] = []
        
        for post in try db.prepare(query) {
            result.append(Post(
                id: post[postId],
                userId: post[userId],
                title: post[title],
                content: post[content],
                published: post[published],
                publishedAt: post[publishedAt],
                viewCount: post[viewCount]
            ))
        }
        
        return result
    }
    
    // Get published posts
    func getPublishedPosts(limit: Int = 20, offset: Int = 0) throws -> [Post] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts
            .filter(published == true)
            .order(publishedAt.desc)
            .limit(limit, offset: offset)
        
        var result: [Post] = []
        
        for post in try db.prepare(query) {
            result.append(Post(
                id: post[postId],
                userId: post[userId],
                title: post[title],
                content: post[content],
                published: post[published],
                publishedAt: post[publishedAt],
                viewCount: post[viewCount]
            ))
        }
        
        return result
    }
    
    // Get posts with user information joined
    func getPostsWithAuthors(limit: Int = 20) throws -> [(Post, User)] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts
            .join(users, on: users[id] == posts[userId])
            .order(posts[postId].desc)
            .limit(limit)
        
        var result: [(Post, User)] = []
        
        for row in try db.prepare(query) {
            let post = Post(
                id: row[posts[postId]],
                userId: row[posts[userId]],
                title: row[posts[title]],
                content: row[posts[content]],
                published: row[posts[published]],
                publishedAt: row[posts[publishedAt]],
                viewCount: row[posts[viewCount]]
            )
            
            let userProfile = try? UserProfile.fromJSONString(row[users[profile]])
            let user = User(
                id: row[users[id]],
                name: row[users[name]],
                email: row[users[email]],
                age: row[users[age]],
                isActive: row[users[isActive]],
                createdAt: row[users[createdAt]],
                profile: userProfile
            )
            
            result.append((post, user))
        }
        
        return result
    }
    
    // Publish post
    func publishPost(_ postIdValue: Int64) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let postToUpdate = posts.filter(postId == postIdValue)
        let update = postToUpdate.update(
            published <- true,
            publishedAt <- Date()
        )
        
        let changes = try db.run(update)
        return changes > 0
    }
    
    // Increment view count
    func incrementViewCount(_ postIdValue: Int64) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let postToUpdate = posts.filter(postId == postIdValue)
        let update = postToUpdate.update(viewCount <- viewCount + 1)
        
        let changes = try db.run(update)
        return changes > 0
    }
    
    // Delete post
    func deletePost(_ postIdValue: Int64) throws -> Bool {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let postToDelete = posts.filter(postId == postIdValue)
        let changes = try db.run(postToDelete.delete())
        
        return changes > 0
    }
    
    // Search posts by title
    func searchPostsByTitle(_ searchTerm: String) throws -> [Post] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts.filter(title.like("%\(searchTerm)%")).order(postId.desc)
        var result: [Post] = []
        
        for post in try db.prepare(query) {
            result.append(Post(
                id: post[postId],
                userId: post[userId],
                title: post[title],
                content: post[content],
                published: post[published],
                publishedAt: post[publishedAt],
                viewCount: post[viewCount]
            ))
        }
        
        return result
    }
    
    // Get popular posts (by view count)
    func getPopularPosts(limit: Int = 10) throws -> [Post] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = posts
            .filter(published == true)
            .order(viewCount.desc)
            .limit(limit)
        
        var result: [Post] = []
        
        for post in try db.prepare(query) {
            result.append(Post(
                id: post[postId],
                userId: post[userId],
                title: post[title],
                content: post[content],
                published: post[published],
                publishedAt: post[publishedAt],
                viewCount: post[viewCount]
            ))
        }
        
        return result
    }
    
    // User post counts
    func getUserPostCounts() throws -> [(User, Int)] {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let query = """
            SELECT u.*, COUNT(p.post_id) as post_count
            FROM users u
            LEFT JOIN posts p ON u.id = p.user_id
            GROUP BY u.id
            ORDER BY post_count DESC
        """
        
        var result: [(User, Int)] = []
        
        for row in try db.prepare(query) {
            let userProfile = try? UserProfile.fromJSONString(row[6] as? String)
            let user = User(
                id: row[0] as! Int64,
                name: row[1] as! String,
                email: row[2] as! String,
                age: row[3] as! Int,
                isActive: row[4] as! Bool,
                createdAt: row[5] as! Date,
                profile: userProfile
            )
            let postCount = row[7] as! Int
            
            result.append((user, postCount))
        }
        
        return result
    }
    
    // Post statistics
    func getPostStatistics() throws -> (total: Int, published: Int, avgViewCount: Double) {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let totalPosts = try db.scalar(posts.count)
        let publishedPosts = try db.scalar(posts.filter(published == true).count)
        let avgViews = try db.scalar(posts.select(viewCount.average)) ?? 0.0
        
        return (total: totalPosts, published: publishedPosts, avgViewCount: avgViews)
    }
}

// Usage example
func demonstratePostOperations() {
    let database = UserDatabase()
    
    do {
        // Create user
        let user = User(name: "John Doe", email: "[email protected]", age: 30)
        let userId = try database.createUser(user)
        
        // Create posts
        let post1 = Post(userId: userId, title: "My First Post", content: "This is the content of my first post.")
        let post2 = Post(userId: userId, title: "Another Post", content: "This is another interesting post.")
        let post3 = Post(userId: userId, title: "Published Post", content: "This post is published.", published: true, publishedAt: Date())
        
        let postId1 = try database.createPost(post1)
        let postId2 = try database.createPost(post2)
        let postId3 = try database.createPost(post3)
        
        print("Created posts: \(postId1), \(postId2), \(postId3)")
        
        // Publish post
        _ = try database.publishPost(postId1)
        print("Published post: \(postId1)")
        
        // Increment view count
        _ = try database.incrementViewCount(postId1)
        _ = try database.incrementViewCount(postId3)
        _ = try database.incrementViewCount(postId3)
        
        // Get published posts
        let publishedPosts = try database.getPublishedPosts()
        print("\n=== Published Posts ===")
        for post in publishedPosts {
            print("Post: \(post.title) (Views: \(post.viewCount))")
        }
        
        // Join posts and user information
        let postsWithAuthors = try database.getPostsWithAuthors()
        print("\n=== Posts with Authors ===")
        for (post, author) in postsWithAuthors {
            print("Post: \(post.title) by \(author.name)")
        }
        
        // Popular posts
        let popularPosts = try database.getPopularPosts(limit: 5)
        print("\n=== Popular Posts ===")
        for post in popularPosts {
            print("Popular: \(post.title) (\(post.viewCount) views)")
        }
        
        // Statistics
        let stats = try database.getPostStatistics()
        print("\n=== Post Statistics ===")
        print("Total posts: \(stats.total)")
        print("Published posts: \(stats.published)")
        print("Average view count: \(String(format: "%.1f", stats.avgViewCount))")
        
        // User post counts
        let userPostCounts = try database.getUserPostCounts()
        print("\n=== User Post Counts ===")
        for (user, count) in userPostCounts {
            print("\(user.name): \(count) posts")
        }
        
    } catch {
        print("Error: \(error)")
    }
}

Transactions and Advanced Features

// Transaction functionality and batch processing
extension UserDatabase {
    
    // Transaction: Create user and initial posts simultaneously
    func createUserWithPosts(user: User, initialPosts: [Post]) throws -> (userId: Int64, postIds: [Int64]) {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var userId: Int64 = 0
        var postIds: [Int64] = []
        
        try db.transaction {
            // Create user
            let profileJSON = try user.profile?.toJSONString()
            let userInsert = users.insert(
                name <- user.name,
                email <- user.email,
                age <- user.age,
                isActive <- user.isActive,
                createdAt <- user.createdAt,
                profile <- profileJSON
            )
            userId = try db.run(userInsert)
            
            // Create initial posts
            for postData in initialPosts {
                let postInsert = posts.insert(
                    self.userId <- userId,
                    title <- postData.title,
                    content <- postData.content,
                    published <- postData.published,
                    publishedAt <- postData.publishedAt,
                    viewCount <- postData.viewCount
                )
                let postId = try db.run(postInsert)
                postIds.append(postId)
            }
        }
        
        return (userId: userId, postIds: postIds)
    }
    
    // Transaction: Transfer posts between users
    func transferPosts(fromUserId: Int64, toUserId: Int64) throws -> Int {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var transferCount = 0
        
        try db.transaction {
            // Check existence of both users
            guard try db.pluck(users.filter(id == fromUserId)) != nil else {
                throw DatabaseError.userNotFound(fromUserId)
            }
            
            guard try db.pluck(users.filter(id == toUserId)) != nil else {
                throw DatabaseError.userNotFound(toUserId)
            }
            
            // Transfer posts
            let postsToTransfer = posts.filter(userId == fromUserId)
            transferCount = try db.run(postsToTransfer.update(userId <- toUserId))
        }
        
        return transferCount
    }
    
    // Batch processing: Bulk update multiple users' ages
    func batchUpdateUserAges(userAgeUpdates: [(userId: Int64, newAge: Int)]) throws -> Int {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var totalUpdated = 0
        
        try db.transaction {
            for update in userAgeUpdates {
                let userToUpdate = users.filter(id == update.userId)
                let changes = try db.run(userToUpdate.update(age <- update.newAge))
                totalUpdated += changes
            }
        }
        
        return totalUpdated
    }
    
    // Conditional deletion: Delete inactive users and their posts
    func cleanupInactiveUsers() throws -> (deletedUsers: Int, deletedPosts: Int) {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var deletedUsers = 0
        var deletedPosts = 0
        
        try db.transaction {
            // Get inactive users
            let inactiveUsers = users.filter(isActive == false)
            
            // Get inactive user IDs
            var inactiveUserIds: [Int64] = []
            for user in try db.prepare(inactiveUsers) {
                inactiveUserIds.append(user[id])
            }
            
            // Delete related posts
            for userIdValue in inactiveUserIds {
                let userPosts = posts.filter(userId == userIdValue)
                deletedPosts += try db.run(userPosts.delete())
            }
            
            // Delete users
            deletedUsers = try db.run(inactiveUsers.delete())
        }
        
        return (deletedUsers: deletedUsers, deletedPosts: deletedPosts)
    }
    
    // Database backup (simple version)
    func exportUserData() throws -> String {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        var exportData: [String: Any] = [:]
        
        // Export user data
        let allUsers = try getAllUsers()
        let usersData = allUsers.map { user in
            [
                "id": user.id ?? 0,
                "name": user.name,
                "email": user.email,
                "age": user.age,
                "isActive": user.isActive,
                "createdAt": ISO8601DateFormatter().string(from: user.createdAt),
                "profile": user.profile?.toDictionary() ?? [:]
            ]
        }
        exportData["users"] = usersData
        
        // Export post data
        let allPosts = try db.prepare(posts)
        let postsData = try allPosts.map { post in
            [
                "id": post[postId],
                "userId": post[userId],
                "title": post[title],
                "content": post[content],
                "published": post[published],
                "publishedAt": post[publishedAt] != nil ? ISO8601DateFormatter().string(from: post[publishedAt]!) : nil,
                "viewCount": post[viewCount]
            ]
        }
        exportData["posts"] = postsData
        
        let jsonData = try JSONSerialization.data(withJSONObject: exportData, options: .prettyPrinted)
        return String(data: jsonData, encoding: .utf8) ?? ""
    }
    
    // Database statistics
    func getDatabaseStatistics() throws -> DatabaseStatistics {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let totalUsers = try db.scalar(users.count)
        let activeUsers = try db.scalar(users.filter(isActive == true).count)
        let totalPosts = try db.scalar(posts.count)
        let publishedPosts = try db.scalar(posts.filter(published == true).count)
        let avgAge = try db.scalar(users.select(age.average)) ?? 0.0
        let avgViewCount = try db.scalar(posts.select(viewCount.average)) ?? 0.0
        
        return DatabaseStatistics(
            totalUsers: totalUsers,
            activeUsers: activeUsers,
            totalPosts: totalPosts,
            publishedPosts: publishedPosts,
            averageUserAge: avgAge,
            averagePostViewCount: avgViewCount
        )
    }
}

// Statistics structure
struct DatabaseStatistics {
    let totalUsers: Int
    let activeUsers: Int
    let totalPosts: Int
    let publishedPosts: Int
    let averageUserAge: Double
    let averagePostViewCount: Double
}

// Usage example
func demonstrateAdvancedFeatures() {
    let database = UserDatabase()
    
    do {
        // Create user and initial posts simultaneously
        let profile = UserProfile(bio: "Content Creator", skills: ["Writing", "Photography"])
        let user = User(name: "Content Creator", email: "[email protected]", age: 25, profile: profile)
        let initialPosts = [
            Post(userId: 0, title: "Welcome Post", content: "Welcome to my blog!"),
            Post(userId: 0, title: "About Me", content: "Let me introduce myself..."),
            Post(userId: 0, title: "My First Tutorial", content: "This is my first technical post.", published: true, publishedAt: Date())
        ]
        
        let (userId, postIds) = try database.createUserWithPosts(user: user, initialPosts: initialPosts)
        print("Created user \(userId) with posts: \(postIds)")
        
        // Create another user
        let targetUser = User(name: "Another User", email: "[email protected]", age: 30)
        let targetUserId = try database.createUser(targetUser)
        
        // Transfer posts
        let transferCount = try database.transferPosts(fromUserId: userId, toUserId: targetUserId)
        print("Transferred \(transferCount) posts from user \(userId) to user \(targetUserId)")
        
        // Batch age update
        let ageUpdates = [
            (userId: userId, newAge: 26),
            (userId: targetUserId, newAge: 31)
        ]
        let updatedCount = try database.batchUpdateUserAges(userAgeUpdates: ageUpdates)
        print("Updated ages for \(updatedCount) users")
        
        // Set user inactive
        _ = try database.setUserActive(userId, active: false)
        
        // Cleanup inactive users
        let (deletedUsers, deletedPosts) = try database.cleanupInactiveUsers()
        print("Cleanup: deleted \(deletedUsers) users and \(deletedPosts) posts")
        
        // Statistics
        let stats = try database.getDatabaseStatistics()
        print("\n=== Database Statistics ===")
        print("Total users: \(stats.totalUsers)")
        print("Active users: \(stats.activeUsers)")
        print("Total posts: \(stats.totalPosts)")
        print("Published posts: \(stats.publishedPosts)")
        print("Average user age: \(String(format: "%.1f", stats.averageUserAge))")
        print("Average post view count: \(String(format: "%.1f", stats.averagePostViewCount))")
        
        // Data export
        let exportedData = try database.exportUserData()
        print("\n=== Exported Data (first 200 chars) ===")
        print(String(exportedData.prefix(200)) + "...")
        
    } catch {
        print("Error: \(error)")
    }
}

Error Handling and Utility

// Custom error type
enum DatabaseError: Error, LocalizedError {
    case connectionFailed
    case userNotFound(Int64)
    case postNotFound(Int64)
    case invalidData(String)
    case transactionFailed(String)
    
    var errorDescription: String? {
        switch self {
        case .connectionFailed:
            return "Database connection failed"
        case .userNotFound(let id):
            return "User not found: ID \(id)"
        case .postNotFound(let id):
            return "Post not found: ID \(id)"
        case .invalidData(let message):
            return "Invalid data: \(message)"
        case .transactionFailed(let message):
            return "Transaction failed: \(message)"
        }
    }
}

// UserProfile utility extension
extension UserProfile {
    func toJSONString() throws -> String {
        let encoder = JSONEncoder()
        let data = try encoder.encode(self)
        return String(data: data, encoding: .utf8) ?? ""
    }
    
    static func fromJSONString(_ jsonString: String?) throws -> UserProfile? {
        guard let jsonString = jsonString,
              let data = jsonString.data(using: .utf8) else {
            return nil
        }
        
        let decoder = JSONDecoder()
        return try decoder.decode(UserProfile.self, from: data)
    }
    
    func toDictionary() -> [String: Any] {
        return [
            "bio": bio ?? "",
            "website": website ?? "",
            "location": location ?? "",
            "skills": skills
        ]
    }
}

// Migration functionality
extension UserDatabase {
    
    // Version management table
    private var migrationTable: Table { Table("migrations") }
    private var versionColumn: Expression<Int> { Expression<Int>("version") }
    private var appliedAtColumn: Expression<Date> { Expression<Date>("applied_at") }
    
    // Run migrations
    func runMigrations() throws {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        // Create migration table
        try db.run(migrationTable.create(ifNotExists: true) { table in
            table.column(versionColumn, primaryKey: true)
            table.column(appliedAtColumn, defaultValue: Date())
        })
        
        let currentVersion = getCurrentMigrationVersion()
        
        // Run each migration sequentially
        for version in (currentVersion + 1)...getLatestMigrationVersion() {
            try runMigration(version: version)
            try recordMigration(version: version)
            print("Applied migration version \(version)")
        }
    }
    
    private func getCurrentMigrationVersion() -> Int {
        do {
            guard let db = db else { return 0 }
            if let latestMigration = try db.pluck(migrationTable.order(versionColumn.desc)) {
                return latestMigration[versionColumn]
            }
        } catch {
            // Migration table doesn't exist
        }
        return 0
    }
    
    private func getLatestMigrationVersion() -> Int {
        return 3 // Current latest version
    }
    
    private func runMigration(version: Int) throws {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        switch version {
        case 1:
            // Add last_login_at column to users table
            try db.run("ALTER TABLE users ADD COLUMN last_login_at DATETIME")
            
        case 2:
            // Add category column to posts table
            try db.run("ALTER TABLE posts ADD COLUMN category TEXT DEFAULT 'general'")
            
        case 3:
            // Add avatar_url column to users table
            try db.run("ALTER TABLE users ADD COLUMN avatar_url TEXT")
            
        default:
            throw DatabaseError.invalidData("Unknown migration version: \(version)")
        }
    }
    
    private func recordMigration(version: Int) throws {
        guard let db = db else { throw DatabaseError.connectionFailed }
        
        let insert = migrationTable.insert(
            versionColumn <- version,
            appliedAtColumn <- Date()
        )
        try db.run(insert)
    }
}

// Async support (iOS 15+)
@available(iOS 15.0, *)
extension UserDatabase {
    
    func createUserAsync(_ user: User) async throws -> Int64 {
        return try await withUnsafeThrowingContinuation { continuation in
            do {
                let userId = try createUser(user)
                continuation.resume(returning: userId)
            } catch {
                continuation.resume(throwing: error)
            }
        }
    }
    
    func getAllUsersAsync() async throws -> [User] {
        return try await withUnsafeThrowingContinuation { continuation in
            do {
                let users = try getAllUsers()
                continuation.resume(returning: users)
            } catch {
                continuation.resume(throwing: error)
            }
        }
    }
}

// Main execution example
func main() {
    print("=== SQLite.swift Demo ===\n")
    
    demonstrateBasicOperations()
    print("\n" + String(repeating: "=", count: 50) + "\n")
    
    demonstratePostOperations()
    print("\n" + String(repeating: "=", count: 50) + "\n")
    
    demonstrateAdvancedFeatures()
}

// Execute
main()