SQLite

SQLite is perfect for development, testing, and small-scale deployments. It requires no separate database server.

Installation

Add the SQLite backend to your target:

.product(name: "SQLiteBackend", package: "SwiftDataServer")

File-Based Database

import SwiftDataServer
import SQLiteBackend

// Store in a file
let backend = SQLiteBackend(path: "./data/myapp.sqlite")
try await backend.connect()

In-Memory Database

Perfect for testing - fast and ephemeral:

// In-memory (data lost when connection closes)
let backend = SQLiteBackend(path: ":memory:")
try await backend.connect()

Development Setup

Common pattern for development with SQLite, production with PostgreSQL:

import SwiftDataServer
import SQLiteBackend
import PostgreSQLBackend

func configureDatabase(app: Application) async throws -> any DatabaseBackend {
    if let dbHost = Environment.get("DB_HOST") {
        // Production: PostgreSQL
        let backend = PostgreSQLBackend(
            hostname: dbHost,
            port: 5432,
            username: Environment.get("DB_USER") ?? "postgres",
            password: Environment.get("DB_PASS") ?? "password",
            database: Environment.get("DB_NAME") ?? "myapp"
        )
        try await backend.connect()
        return backend
    } else {
        // Development: SQLite
        let backend = SQLiteBackend(path: "./dev.sqlite")
        try await backend.connect()
        return backend
    }
}

Type Mappings

Swift Type SQLite Type
StringTEXT
Int, Int32, Int64INTEGER
Double, FloatREAL
BoolINTEGER (0/1)
DateREAL (Unix timestamp)
UUIDTEXT
DataBLOB
[Codable]TEXT (JSON)

Testing Example

import XCTest
import SwiftDataServer
import SQLiteBackend

final class UserTests: XCTestCase {
    var backend: SQLiteBackend!
    var container: ModelContainer!

    override func setUp() async throws {
        backend = SQLiteBackend(path: ":memory:")
        try await backend.connect()

        container = try ModelContainer(
            for: User.self,
            configurations: ModelConfiguration(backend: backend)
        )

        try await backend.migrate(schema: container.schema)
    }

    func testCreateUser() async throws {
        let context = container.mainContext()
        let user = User(name: "Test", email: "test@example.com", age: 25)
        context.insert(user)
        try await context.saveAsync()

        let fetched = try await context.fetchAsync(FetchDescriptor<User>())
        XCTAssertEqual(fetched.count, 1)
        XCTAssertEqual(fetched.first?.name, "Test")
    }
}

Limitations

  • Single writer - SQLite allows only one write operation at a time
  • No network access - Database file must be on local filesystem
  • Limited concurrency - Not ideal for high-traffic production use

Recommendation: Use SQLite for development and testing. For production deployments with concurrent users, use PostgreSQL or MySQL.

Next Steps