Duck: DuckDB for Swift. Powerful SQL inside iOS and macOS apps

Hi, DEV!

I've been working with Swift for many years, and quite a few of my open-source projects grew out of the same simple desire: I want everyday code to be clean, expressive, and pleasant to write.

Back in 2018, that led me to build CodyFire and UIKitPlus. Later came SwifQL, FCM, and a number of other libraries I mostly use in server-side Swift projects.

Over the years, working on the server side also shaped the way I like to work with databases. I got used to PostgreSQL, powerful SQL, and the idea that the database itself can do a large part of the heavy lifting.

At the same time, my own libraries already let me describe tables as ordinary Swift models, build SQL with type-safe key paths, and use declarative result-builder syntax that feels very natural in modern Swift.

Eventually I reached a point where I didn't want to give that up every time I switched back to iOS or macOS development.

Ideally, I wanted to describe models and queries almost the same way regardless of whether the code was running on a server or inside an app, while still having a similarly powerful database engine on the client.

The Swift side of that problem wasn't especially mysterious to me. I already had SwifQL, now SwiftStream/SQL, ideas from Bridges, and a pretty clear picture of what I wanted the code to look like.

Choosing the database engine was much more interesting.

Looking for PostgreSQL for mobile apps

The obvious thought, if you want PostgreSQL-like capabilities on the client, is to try PostgreSQL itself.

The problem is that PostgreSQL was designed as a server database. It expects its own process, its own connection lifecycle, and an architecture very different from what I wanted inside an iOS app.

What I needed was something I could embed directly into the application, much more like SQLite.

I looked at existing projects that tried to bring PostgreSQL to mobile devices, but I didn't find a ready-made option that fit what I wanted. Turning PostgreSQL into a truly comfortable embedded database would require a fairly serious adaptation of the engine and its runtime model, which is a completely different project from the one I wanted to build.

There are, of course, excellent embedded databases already. SQLite is everywhere, and the Swift ecosystem around local persistence includes GRDB, Core Data, SwiftData, and many other approaches.

I have no problem with SQLite. It solves an enormous number of real-world problems extremely well.

What I wanted to explore was something different: a much richer SQL engine, while keeping syntax and capabilities familiar to someone who spends a lot of time with PostgreSQL.

That search eventually led me to DuckDB, and it turned out to be surprisingly close to what I had in mind.

DuckDB is a full relational database that runs directly inside your application process and doesn't require a separate database server. Its SQL dialect is heavily inspired by PostgreSQL, and the engine gives you much more than simple record persistence: complex queries, powerful aggregations, window functions, rich data types, transactions, vectorized execution, and efficient processing of large datasets.

DuckDB was originally designed primarily for analytical workloads, and that distinction matters. It is not a port of PostgreSQL, and I don't consider it a universal replacement for SQLite.

For my use case, though, the combination of an embedded architecture, a powerful SQL engine, and familiar syntax was exactly the kind of foundation I wanted.

DuckDB already has an official Swift client, but it mostly exposes fairly basic engine interaction. I wanted a much richer API that would make proper use of modern Swift and feel as pleasant as the database code I was already used to writing on the server.

So I took SwiftStream/SQL, brought over ideas from Bridges, added models, Codable, async/await, migrations, transactions, and the other pieces I wanted around it, and gradually built a higher-level Swift layer on top of DuckDB.

That became Duck, which recently reached its first stable release.

Under the hood it uses real DuckDB 2.0.0, while the Swift layer tries to preserve the style of database code I wanted from the beginning.

Models and everyday data access

Let's start with an ordinary model:

import Duck import Foundation struct Todo: Table { @Column("id") var id: UUID @Column("name") var name: String @Column("created_at") var createdAt: Date @Column("done_at") var doneAt: Date? @Column("deleted_at") var deletedAt: Date? init() {} init( id: UUID = UUID(), name: String, createdAt: Date = Date(), doneAt: Date? = nil, deletedAt: Date? = nil ) { self.id = id self.name = name self.createdAt = createdAt self.doneAt = doneAt self.deletedAt = deletedAt } }

If you've used some of my SQL libraries before, this should look fairly familiar.

Todo is just a Swift structure conforming to Table, while @Column connects its properties to database columns and gives us typed references we can later use in queries.

Duck uses DatabaseIdentifier, so we don't need to manually open a connection for every small operation.

For example, we can define a database stored in Application Support:

private var appDatabaseDirectory: URL { FileManager.default .urls( for: .applicationSupportDirectory, in: .userDomainMask )[0] } extension DatabaseIdentifier { static var app: Self { Self( path: appDatabaseDirectory .appendingPathComponent("Todos.duckdb") .path ) } }

Creating a DatabaseIdentifier does not immediately open a native connection. It identifies the logical database, while the required resources are created when operations actually need them.

After the schema exists, inserting a model is straightforward:

let todo = Todo(name: "Write a DEV article") try await todo.insertNonReturning(on: .app)

Or, if we want the inserted model back:

let inserted = try await todo.insert(on: .app)

Updating works with the changed model fields:

var updated = inserted updated.doneAt = Date() updated = try await updated.update( on: \.$id, on: .app )

And reading the whole table can be as short as:

let todos = try await Todo.all(on: .app)

None of this is a reason by itself to use DuckDB. A Todo app obviously doesn't need a serious analytical database engine.

The point is that I didn't want a powerful engine to make simple application code unnecessarily complicated.

When the query becomes more interesting, the rest of the stack is already there.

The SQL I wanted on the client too

One of my favorite parts of Duck is that query construction uses SwiftStream/SQL directly, so the way I work with SQL on the server and on the client can stay very close.

Suppose we want all non-deleted Todo items ordered by creation date:

let todos = try await Todo .query .where(Todo.$deletedAt == nil) .orderBy(.asc(Todo.$createdAt)) .all(on: .app)

Duck already knows that the result should become [Todo], because the query originates from the model.

But the form I personally enjoy more is the declarative one:

let query = SQL { Select { \Todo.$id \Todo.$name \Todo.$createdAt \Todo.$doneAt \Todo.$deletedAt } From(Todo.table) Where { \Todo.$deletedAt == nil } OrderBy { OrderByItem.asc(\Todo.$createdAt) } } let todos = try await query.all( decoding: Todo.self, on: .app )

This is still regular SQL in terms of structure. Instead of manually building a string, we describe the query through a Swift Result Builder.

Swift can keep column references typed, while dynamic values are passed through prepared bindings.

This gets even nicer once the query starts depending on normal Swift logic:

let includeCompleted = false let query = SQL { Select { \Todo.$id \Todo.$name \Todo.$createdAt \Todo.$doneAt \Todo.$deletedAt } From(Todo.table) Where { \Todo.$deletedAt == nil if !includeCompleted { \Todo.$doneAt == nil } } OrderBy { OrderByItem.asc(\Todo.$createdAt) } }

The same DSL can be used for JOINs, grouping, aggregates, subqueries, and more complex SQL without switching to another query system once the application becomes more complicated.

Reusing queries and query fragments

Another part of SwiftStream/SQL that I especially like is that queries can be composed from reusable Swift values.

For example, we can move our Todo filtering logic into its own type:

struct TodoFilter: SQLQuery { let includeCompleted: Bool var query: Query { Where { \Todo.$deletedAt == nil if !includeCompleted { \Todo.$doneAt == nil } } } }

Then use the same filter in a regular SELECT:

let query = SQL { Select(\Todo.$id, \Todo.$name) From(Todo.table) TodoFilter(includeCompleted: false) OrderBy(.asc(\Todo.$createdAt)) }

And reuse it again in a count query:

let query = SQL { Select(Fn.count(\Todo.$id) => "count") From(Todo.table) TodoFilter(includeCompleted: false) }

A complete query can be extracted the same way:

struct TodosQuery: SQLQuery { let includeCompleted: Bool var query: Query { Select { \Todo.$id \Todo.$name \Todo.$createdAt \Todo.$doneAt \Todo.$deletedAt } From(Todo.table) TodoFilter(includeCompleted: includeCompleted) OrderBy(.asc(\Todo.$createdAt)) } }

Now it can be reused from different parts of the application:

let todos = try await TodosQuery( includeCompleted: false ).all(decoding: Todo.self, on: .app)

What I like here is that composition doesn't require another abstraction layer.

Whole queries, query fragments, and ordinary SQL expressions are all just Swift values that can be combined.

Codable doesn't mean SELECT *

You also don't need to load the entire table just because you want Codable decoding.

Suppose a screen only needs id and name:

struct TodoSummary: Decodable { let id: UUID let name: String }

We can select exactly those columns:

let summaries = try await Todo .select(\.$id, \.$name) .where(\Todo.$deletedAt == nil) .all( decoding: TodoSummary.self, on: .app )

The same works with arbitrary SQL results.

For example, an aggregate result can be decoded into a dedicated type:

struct TodoCount: Decodable { let count: Int64 } let query = SQL { Select( Fn.cast(Fn.count(\Todo.$id), .bigint) => "count" ) From(Todo.table) Where(\Todo.$deletedAt == nil) } let count = try await query.first( decoding: TodoCount.self, on: .app )?.count ?? 0

Codable isn't dictating what query you're allowed to write.

You define the SQL you need, select the fields you care about, and decode the result into an appropriate Swift type. If you need full control, QueryResult and ResultRow are still available directly.

Migrations and transactions

We've already been using Todo, but obviously the database needs the corresponding schema.

I wanted to keep the migration model close to what I'm used to on the server:

extension Todo { struct Create: Migration { let name = "Todo.Create" var up: MigrationPlan { CreateTable("todo") { NewColumn("id", .uuid).primaryKey() NewColumn("name", .text).notNull() NewColumn("created_at", .timestamptz).notNull() NewColumn("done_at", .timestamptz) NewColumn("deleted_at", .timestamptz) } } var down: MigrationPlan { DropTableBuilder<Todo>() } } }

During application setup:

try FileManager.default.createDirectory( at: appDatabaseDirectory, withIntermediateDirectories: true ) _ = try await DatabaseIdentifier.app .migrator([Todo.Create()]) .migrate()

Duck keeps migration history and only applies migrations that haven't been executed yet. There is support for up, down, batches, and additional work inside migrations.

Transactions are similarly explicit:

let connection = try await DatabaseIdentifier.app.connect() try await connection.transaction { transaction in let first = Todo(name: "First") let second = Todo(name: "Second") try await first.insertNonReturning(on: transaction) try await second.insertNonReturning(on: transaction) }

If the closure completes successfully, Duck commits. If it throws or the task is cancelled, the transaction rolls back.

There is also an explicit connection.transaction() form for cases where the transaction lifetime cannot comfortably fit inside one closure. In that case, the caller owns finalization and must call commit() or rollback() explicitly.

A few more DuckDB features

The previous examples are enough for ordinary model-based application work, but the more interesting parts of DuckDB become visible once the data gets larger or the schema becomes richer.

Native LIST values

DuckDB supports a native LIST type, which means Swift arrays don't necessarily need to be serialized into JSON first.

For example:

struct User: Table { static let tableName = "users" @Column("id") var id: Int64 @Column("tags") var tags: [String] init() {} init(id: Int64, tags: [String]) { self.id = id self.tags = tags } }

The corresponding column can be created as a real DuckDB LIST:

NewColumn("tags", .list(.varchar)).notNull()

And the regular model API continues to work:

let user = User( id: 1, tags: ["swift", "duckdb"] ) let inserted = try await user.insert(on: .app) let loaded = try await User .query .where(\User.$id == inserted.id) .first(on: .app)

The first stable version currently supports one-dimensional LIST values and one immediate keyed STRUCT level.

More complex recursive combinations are still future work.

Bulk insertion

DuckDB can load large amounts of data efficiently through its Appender API, and I wanted that to be available both through model-level conveniences and a lower-level interface.

For an ordinary collection:

let todos = [ Todo(name: "One"), Todo(name: "Two"), Todo(name: "Three") ] try await todos.batchInsert(on: .app)

Or directly through Appender:

let todoTable = DB.Table(Todo.tableName) let connection = try await DatabaseIdentifier.app.connect() try await connection.appender( to: todoTable, columns: ["id", "name", "created_at"] ) { appender in for name in importedTodoNames { try await appender.append( UUID(), name, Date() ) } }

If the entire import should participate in one transaction, the same idea works through transaction.appender.

Reusable prepared statements are available as well when you want to prepare the same SQL shape once and execute it repeatedly with different parameters.

This ability to move gradually from a very small model API to lower-level DuckDB tools is one of the parts I care about most.

The simple code can stay simple, while more demanding workloads don't force you to replace the entire database stack.

Reactive SQL queries for SwiftUI and UIKit

Another feature I wanted on the client side was the ability to observe the result of a SQL query directly.

Suppose a SwiftUI screen has an ordinary Todo list:

@State private var todos: [Todo] = []

With Duck, the query itself can become an observation:

.task { do { for try await result in try Todo .select .where(Todo.$deletedAt == nil) .orderBy(.asc(Todo.$createdAt)) .observe(on: .app) { todos = try result.all(decoding: Todo.self) } } catch is CancellationError { // SwiftUI cancelled the task } catch { print("Duck error:", error) } }

This is implemented as a regular AsyncSequence<QueryResult>.

Duck itself doesn't depend on SwiftUI, so the same sequence can be consumed from UIKit, a command-line application, or any other Swift code.

When the Task is cancelled, cancellation propagates into the observation, the worker stops, and the subscription is released.

Observation is intentionally a latest-state abstraction rather than a log of every database event. Multiple invalidations can be coalesced, and identical QueryResult values are not published repeatedly.

There is also an important boundary: observation reacts to mutations routed through the same logical DatabaseIdentifier inside the process. It is not a filesystem watcher and it is not a cross-process synchronization mechanism.

Observation efficiency

After the release, I got a good question: how precisely does Duck determine whether a data change can actually affect a particular observed query?

In Duck 1.0, invalidation is deliberately conservative. Even a mutation to a completely unrelated table can currently cause a query to run again. Duck suppresses identical results before delivering them to the consumer, but the extra SQL execution has still happened.

Since I plan to use Duck myself in an application with millions of rows and a very high number of write operations, more precise invalidation is already planned for 1.1, including predicate-aware optimizations and dedicated high-load benchmarks on generated million-row datasets.