Practice Now
Swift
SQLite
Database
iOS Development
Programming

Accessing an SQLite Database in Swift

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
DatabasesiOS & Android

Introduction

SQLite is a lightweight, file-based database engine embedded directly in iOS and macOS. Swift can access SQLite through the C API (libsqlite3), which ships with every Apple platform — no additional dependencies needed. For most apps, the workflow is: open a database file, execute SQL statements using sqlite3_exec or prepared statements, read results, and close the connection. While the C API works, wrapper libraries like GRDB.swift and SQLite.swift provide safer, more idiomatic Swift interfaces.

Using the C API Directly

Add libsqlite3 to your project by importing the module:

swift
1import SQLite3
2
3class DatabaseManager {
4    var db: OpaquePointer?
5
6    func openDatabase() -> Bool {
7        let fileURL = try! FileManager.default
8            .url(for: .documentDirectory, in: .userDomainMask,
9                 appropriateFor: nil, create: false)
10            .appendingPathComponent("app.sqlite")
11
12        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
13            print("Error opening database: \(String(cString: sqlite3_errmsg(db)))")
14            return false
15        }
16        return true
17    }
18
19    func createTable() {
20        let sql = """
21            CREATE TABLE IF NOT EXISTS users (
22                id INTEGER PRIMARY KEY AUTOINCREMENT,
23                name TEXT NOT NULL,
24                email TEXT UNIQUE NOT NULL
25            );
26            """
27        var errMsg: UnsafeMutablePointer<Int8>?
28        if sqlite3_exec(db, sql, nil, nil, &errMsg) != SQLITE_OK {
29            print("Error creating table: \(String(cString: errMsg!))")
30            sqlite3_free(errMsg)
31        }
32    }
33
34    deinit {
35        sqlite3_close(db)
36    }
37}

Inserting Data with Prepared Statements

Prepared statements prevent SQL injection and improve performance for repeated inserts:

swift
1func insertUser(name: String, email: String) -> Bool {
2    let sql = "INSERT INTO users (name, email) VALUES (?, ?);"
3    var stmt: OpaquePointer?
4
5    guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else {
6        print("Error preparing insert: \(String(cString: sqlite3_errmsg(db)))")
7        return false
8    }
9
10    sqlite3_bind_text(stmt, 1, (name as NSString).utf8String, -1, nil)
11    sqlite3_bind_text(stmt, 2, (email as NSString).utf8String, -1, nil)
12
13    let result = sqlite3_step(stmt) == SQLITE_DONE
14    sqlite3_finalize(stmt)
15    return result
16}

Querying Data

swift
1func fetchAllUsers() -> [(id: Int, name: String, email: String)] {
2    let sql = "SELECT id, name, email FROM users;"
3    var stmt: OpaquePointer?
4    var users: [(id: Int, name: String, email: String)] = []
5
6    guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else {
7        print("Error preparing query: \(String(cString: sqlite3_errmsg(db)))")
8        return users
9    }
10
11    while sqlite3_step(stmt) == SQLITE_ROW {
12        let id = Int(sqlite3_column_int(stmt, 0))
13        let name = String(cString: sqlite3_column_text(stmt, 1))
14        let email = String(cString: sqlite3_column_text(stmt, 2))
15        users.append((id: id, name: name, email: email))
16    }
17
18    sqlite3_finalize(stmt)
19    return users
20}

GRDB.swift provides a type-safe, Swift-native API. Add it via Swift Package Manager:

swift
1import GRDB
2
3struct User: Codable, FetchableRecord, PersistableRecord {
4    var id: Int64?
5    var name: String
6    var email: String
7}
8
9// Open database
10let dbQueue = try DatabaseQueue(path: dbPath)
11
12// Create table
13try dbQueue.write { db in
14    try db.create(table: "users", ifNotExists: true) { t in
15        t.autoIncrementedPrimaryKey("id")
16        t.column("name", .text).notNull()
17        t.column("email", .text).notNull().unique()
18    }
19}
20
21// Insert
22try dbQueue.write { db in
23    var user = User(id: nil, name: "Alice", email: "[email protected]")
24    try user.insert(db)
25}
26
27// Query
28let users = try dbQueue.read { db in
29    try User.fetchAll(db)
30}

Bundling a Pre-populated Database

To ship a database with your app, add the .sqlite file to your Xcode project and copy it to the Documents directory on first launch:

swift
1func copyDatabaseIfNeeded() {
2    let fileManager = FileManager.default
3    let documentsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask).first!
4    let destURL = documentsURL.appendingPathComponent("app.sqlite")
5
6    if !fileManager.fileExists(atPath: destURL.path) {
7        let bundleURL = Bundle.main.url(forResource: "app", withExtension: "sqlite")!
8        try! fileManager.copyItem(at: bundleURL, to: destURL)
9    }
10}

Common Pitfalls

  • Forgetting sqlite3_finalize(): Every sqlite3_prepare_v2 call must be paired with sqlite3_finalize. Leaking statements causes memory leaks and eventually SQLITE_BUSY errors.
  • Thread safety: SQLite's default serialized mode is safe for multi-threaded reads, but concurrent writes from different threads can cause SQLITE_BUSY. Use a serial DispatchQueue or GRDB's DatabaseQueue for write serialization.
  • SQL injection with string interpolation: Never use "INSERT INTO users VALUES ('\(name)')". Always use ? parameter binding with sqlite3_bind_text.
  • Database file location: On iOS, store the database in the Documents or Application Support directory. The app bundle is read-only — you cannot write to a database stored there.
  • Not handling SQLITE_BUSY: When another connection holds a write lock, queries return SQLITE_BUSY. Implement retry logic or use WAL mode (PRAGMA journal_mode=WAL) for better concurrency.

Summary

  • Import SQLite3 to use the C API directly — no extra dependencies needed on Apple platforms
  • Use prepared statements (sqlite3_prepare_v2 + sqlite3_bind_*) for safe, efficient queries
  • Always finalize statements and close the database connection to prevent resource leaks
  • GRDB.swift provides a type-safe, Codable-compatible Swift wrapper that eliminates most C API boilerplate
  • Store database files in the Documents or Application Support directory, not the app bundle
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.