SQLumen — macOS Database Client

Project overview

Native macOS database client built with SwiftUI. Portfolio project targeting App Store. Positioned as a fast, beautiful alternative to TablePlus for developers who work primarily with local and self-hosted databases.

Tagline: "A native macOS database client that doesn't compromise on aesthetics or performance."

Target audience: macOS developers (indie, startup, student) who need a polished local DB client without paying $99/year for TablePlus.

Monetization: Freemium. Core viewing is free. SQL editor, export, and editing behind a one-time IAP (~$19–29). No subscription.

Goals for this codebase

Ship a working v1 to the Mac App Store
Demonstrate systems-level Swift expertise (Swift Concurrency, protocol-oriented architecture, Keychain integration, C interop for sqlite3/libpq)
Clean, readable code suitable for portfolio and technical interviews
Pluggable driver architecture that makes adding new databases straightforward

Tech stack

Language: Swift 6 (strict concurrency)
UI: SwiftUI + AppKit where needed (e.g. NSTableView for large result sets)
Async: Swift Concurrency throughout — no GCD, no callbacks
Persistence: SwiftData for app state (saved connections, query history, favourites)
Credentials: macOS Keychain via Security.framework — never UserDefaults
C interop: sqlite3.h (bundled in macOS SDK), libpq (PostgreSQL), libmysqlclient
Minimum deployment: macOS 14 (Sonoma) — use all modern APIs freely

Architecture

Three-layer architecture. Each layer only knows about the layer below it.

┌─────────────────────────────────────────────────────┐
│  UI Layer (SwiftUI)                                 │
│  ConnectionManager · TableBrowser · SQLEditor       │
│  SchemaViewer · ERDiagram · ExportSheet             │
└───────────────────┬─────────────────────────────────┘
                    │ @MainActor ViewModels
┌───────────────────▼─────────────────────────────────┐
│  Core Layer                                         │
│  QueryEngine · ResultSetStream · SafeMode           │
│  ConnectionPool · SchemaParser · ExportService      │
└───────────────────┬─────────────────────────────────┘
                    │ DatabaseDriver protocol
┌───────────────────▼─────────────────────────────────┐
│  Driver Layer                                       │
│  SQLiteDriver · PostgreSQLDriver · MySQLDriver      │
│  DuckDBDriver · (future: RedisDriver · MSSQLDriver) │
└─────────────────────────────────────────────────────┘

DatabaseDriver protocol

Every database backend conforms to this protocol. Core layer never imports a specific driver.

protocol DatabaseDriver: Actor {
    var info: ConnectionInfo { get }

    func connect() async throws
    func disconnect() async

    func query(_ sql: String, parameters: [SQLValue]) async throws -> ResultSet
    func stream(_ sql: String, parameters: [SQLValue]) -> AsyncThrowingStream<ResultRow, Error>

    func fetchSchema() async throws -> DatabaseSchema
    func beginTransaction() async throws -> Transaction
}

ResultSet streaming

Never load entire result sets into memory. Use AsyncThrowingStream for all data fetching. NSTableView / List should page lazily. Default page size: 500 rows.

Safe Mode

All DML (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER) must go through SafeModeGuard. It generates a human-readable preview and requires explicit confirmation before execution. This is a Core-layer middleware — drivers never bypass it.

actor SafeModeGuard {
    func validate(_ statement: ParsedStatement) async throws -> SafeModeDecision
    // .allow / .requireConfirmation(preview:) / .block(reason:)
}

Connection credentials

Store only the connection name and driver type in SwiftData. All sensitive fields (password, SSH key passphrase) go to Keychain with kSecAttrService = "com.yourname.sqlumen".

Feature roadmap

v1 — MVP (App Store launch)

Free tier:

Open SQLite, DuckDB, PostgreSQL, MySQL/MariaDB connections
Table browser with virtualised grid (NSTableView backed)
Column sorting, basic search/filter
Schema inspector (columns, types, indexes, PKs, FKs)
Recent connections with Keychain-stored credentials

Pro IAP (unlock once):

SQL editor with syntax highlighting + autocomplete
Safe mode with SQL preview before DML
Export: CSV, JSON, SQL INSERT
Row editing (INSERT / UPDATE / DELETE with confirmation)
Export to Excel (.xlsx via SheetJS or native)

v2

ER diagram (visualise FK relationships)
DDL editor (create/alter tables via UI)
Metrics board: basic charts from query results (pie, bar, scoreboard)
SSH tunnel support
Favourite queries with folders
⌘K "Open Anything" palette
macOS Tahoe / Liquid Glass UI adaptation

v3 / Pro

AI SQL assistant (natural language → SQL via Anthropic API)
iCloud sync for saved queries and connections
CLI tool (sqlumen export --connection prod --table users)
AppleScript / Shortcuts support

Database drivers — implementation order

1. SQLiteDriver (v1, week 1–2)

Use sqlite3.h from the macOS SDK — zero external dependencies.

import SQLite3

actor SQLiteDriver: DatabaseDriver {
    private var db: OpaquePointer?
    // sqlite3_open_v2, sqlite3_prepare_v2, sqlite3_step, sqlite3_finalize
}

Key details:

File-based: path stored in ConnectionInfo, no host/port
Enable WAL mode on open: PRAGMA journal_mode=WAL
Enable foreign keys: PRAGMA foreign_keys=ON
Use sqlite3_bind_* for all parameters — never string interpolation

2. PostgreSQLDriver (v1, week 3–4)

Use libpq via C interop. Link libpq.tbd in Xcode.

import CPostgres // module map wrapping libpq

actor PostgreSQLDriver: DatabaseDriver {
    private var conn: OpaquePointer?
    // PQconnectdb, PQexec, PQprepare, PQexecPrepared
}

Key details:

Use PQsetSingleRowMode for streaming large result sets
Parse pg_catalog for schema introspection
Support SSL via connection string params

3. MySQLDriver (v1, week 5–6)

Use libmysqlclient via C interop.

Key details:

Use mysql_use_result (not mysql_store_result) for streaming
Parse information_schema for schema introspection
MariaDB is wire-compatible — same driver, detect via @@version

Code conventions

Swift Concurrency

// All drivers are actors — no @unchecked Sendable anywhere
// ViewModels are @MainActor
// Data flows: Driver (actor) → Core (actor) → ViewModel (@MainActor) → View

// Prefer structured concurrency
async let schema = driver.fetchSchema()
async let rowCount = driver.query("SELECT COUNT(*) FROM \(table)", parameters: [])
let (s, c) = try await (schema, rowCount)

// Use TaskGroup for parallel table fetches
await withThrowingTaskGroup(of: TableSchema.self) { group in
    for table in tableNames {
        group.addTask { try await driver.fetchSchema(for: table) }
    }
}

Error handling

enum SQLumenError: LocalizedError {
    case connectionFailed(underlying: Error)
    case queryFailed(sql: String, message: String)
    case safeModeBlocked(reason: String)
    case exportFailed(path: URL, underlying: Error)
    // ...
}

Always wrap driver errors in SQLumenError before surfacing to UI. Never expose raw C error codes or libpq messages directly to the user.

Naming

Files: FeatureName+Extension.swift for extensions, FeatureNameView.swift for views
ViewModels: FeatureNameViewModel.swift, marked @MainActor final class
Actors: FeatureNameActor.swift or driver files directly
No Manager suffix except ConnectionManager (established term)
Prefer fetchX() for async reads, submitX() for async writes

Testing

Unit test all Core layer logic (QueryEngine, SafeModeGuard, SchemaParser)
Integration test each driver against a real DB instance (use Docker in CI)
UI tests for critical flows: open connection, run query, export CSV
Use swift-testing (not XCTest) for new tests

Project structure

SQLumen/
├── App/
│   ├── SQLumenApp.swift
│   └── AppDelegate.swift
├── UI/
│   ├── Connection/
│   ├── TableBrowser/
│   ├── SQLEditor/
│   ├── SchemaViewer/
│   └── Shared/
├── Core/
│   ├── QueryEngine.swift
│   ├── ResultSet.swift
│   ├── SafeModeGuard.swift
│   ├── ConnectionPool.swift
│   ├── SchemaParser.swift
│   └── ExportService.swift
├── Drivers/
│   ├── DatabaseDriver.swift        ← protocol
│   ├── SQLite/
│   │   └── SQLiteDriver.swift
│   ├── PostgreSQL/
│   │   └── PostgreSQLDriver.swift
│   └── MySQL/
│       └── MySQLDriver.swift
├── Models/
│   ├── ConnectionInfo.swift
│   ├── DatabaseSchema.swift
│   ├── ResultSet.swift
│   └── SQLValue.swift
└── Resources/
    └── CLAUDE.md                   ← this file

What to build first

Start here, in order:

DatabaseDriver protocol + SQLValue enum + ResultSet types
SQLiteDriver — full implementation, unit tested
TableBrowserView + TableBrowserViewModel against SQLiteDriver
SQLEditorView with syntax highlighting (use NSTextView + regex-based highlighter)
SchemaInspectorView
ConnectionManagerView + Keychain integration
PostgreSQLDriver
MySQLDriver
Export (CSV first, then JSON, then SQL INSERT)
Safe Mode guard
App Store assets, privacy policy, IAP setup

App Store checklist

Privacy manifest (PrivacyInfo.xcprivacy) — declare Keychain, no tracking
No network calls except to user-specified DB hosts
Sandbox entitlements: com.apple.security.network.client only
No MAS-prohibited APIs
Support both light and dark mode
Support macOS accessibility (VoiceOver labels on all interactive elements)
Universal binary: Apple Silicon + Intel

Competitive context

Tool	Price	Weakness we exploit
TablePlus	$99/seat	Expensive, C++/ObjC codebase, not pure SwiftUI
DB Browser for SQLite	Free	SQLite only, Qt UI looks dated
Sequel Pro / Ace	Free	MySQL only
Jakob Egger MDB Viewer	$19	Read-only, no SQL, MDB only

Our angle: native SwiftUI, modern Swift 6 concurrency, free to try, one-time purchase.

DuckDB driver notes

4. DuckDBDriver (v1, week 7)

DuckDB is an in-process OLAP database — architecture identical to SQLite (file-based, C API, zero server). Growing fast among data-oriented developers. TablePlus added it in 2024.

Use the official DuckDB C API. Embed the static library (libduckdb.a) directly.

import CDuckDB // module map wrapping duckdb.h

actor DuckDBDriver: DatabaseDriver {
    private var db: duckdb_database?
    private var conn: duckdb_connection?
    // duckdb_open, duckdb_connect, duckdb_query, duckdb_destroy_result
}

Key details:

File-based like SQLite — path in ConnectionInfo, no host/port
Use duckdb_pending_query for async/streaming execution
Natively supports Parquet, CSV, JSON as virtual tables — expose in schema inspector
Default schema is main, catalog is the filename

Dark mode

SwiftUI handles dark/light automatically if you never hardcode colors. Rules:

Always use semantic colors: Color.primary, Color.secondary, Color.background, NSColor.controlBackgroundColor, NSColor.separatorColor, etc.
For custom UI elements (result grid cells, syntax highlight tokens, connection badges) define a color palette using Color(nsColor:) with adaptive NSColor:

extension Color {
    static let gridBackground = Color(NSColor(name: nil) { appearance in
        appearance.bestMatch(from: [.darkAqua, .aqua]) == .darkAqua
            ? NSColor(white: 0.12, alpha: 1)
            : NSColor(white: 0.98, alpha: 1)
    })

    static let gridAlternatingRow = Color(NSColor(name: nil) { appearance in
        appearance.bestMatch(from: [.darkAqua, .aqua]) == .darkAqua
            ? NSColor(white: 0.15, alpha: 1)
            : NSColor(white: 0.95, alpha: 1)
    })
}

NSTableView (result grid): set usesAlternatingRowBackgroundColors = true and let AppKit handle it. Override backgroundColor only if the default doesn't match the design.
Test every screen in both modes before shipping. Use Xcode's Environment Overrides (⌃⌘A in simulator/canvas) to toggle quickly.
Never use .opacity() as a substitute for a proper dark-mode color — it breaks on non-white backgrounds.

Syntax highlighting

Use Runestone + Tree-sitter for the SQL editor. This is the same approach used by Zed, Nova, and GitHub — incremental parsing, correct highlighting for complex SQL (CTEs, window functions, subqueries), and ready-made grammars.

Dependencies

Add via Swift Package Manager:

// Package.swift or Xcode SPM
"https://github.com/simonbs/Runestone"          // TextEditor view + highlight infra
"https://github.com/krzyzanowskim/STTextView"   // fallback if Runestone is too opinionated

Tree-sitter SQL grammar (compile as a C target bundled in the app):

"https://github.com/DerekStride/tree-sitter-sql"  // covers PostgreSQL, MySQL, SQLite dialects

Integration pattern

import Runestone
import TreeSitterSQL  // compiled grammar

// 1. Create a language configuration
let sqlLanguage = TreeSitterLanguage(
    language: tree_sitter_sql(),
    highlightsQuery: /* load highlights.scm from bundle */,
    injectionsQuery: nil,
    localsQuery: nil
)

// 2. Build a theme that respects dark/light mode
struct SQLumenTheme: Runestone.Theme {
    var textColor: UIColor { .label }
    var gutterBackgroundColor: UIColor { .secondarySystemBackground }
    var gutterHairlineColor: UIColor { .separator }

    func textColor(for highlight: String) -> UIColor? {
        switch highlight {
        case "keyword":   return .systemBlue
        case "string":    return .systemOrange
        case "number":    return .systemTeal
        case "comment":   return .systemGray
        case "function":  return .systemPurple
        case "operator":  return .systemRed
        default:          return nil
        }
    }

    func font(for highlight: String) -> UIFont? { nil }  // inherits editor font
}

// 3. Configure the TextView
let textView = TextView()
textView.setLanguageMode(TreeSitterLanguageMode(language: sqlLanguage))
textView.theme = SQLumenTheme()
textView.font = .monospacedSystemFont(ofSize: 13, weight: .regular)
textView.autocorrectionType = .no
textView.autocapitalizationType = .none
textView.smartDashesType = .no
textView.smartQuotesType = .no

Per-dialect keyword sets

Different databases have different reserved words. When the user switches dialects, update the active language mode:

enum SQLDialect {
    case sqlite, postgresql, mysql, duckdb
    
    var treeSitterLanguage: TreeSitterLanguage {
        // All use tree-sitter-sql grammar but with dialect-specific highlights.scm
        switch self {
        case .postgresql: return sqlLanguage(highlighting: "highlights-postgres.scm")
        case .mysql:      return sqlLanguage(highlighting: "highlights-mysql.scm")
        default:          return sqlLanguage(highlighting: "highlights-generic.scm")
        }
    }
}

Autocomplete

Runestone doesn't provide autocomplete — implement separately with NSTextView delegate or a custom UITextViewDelegate. Sources for suggestions:

SQL keywords (static list per dialect)
Table names from the active connection's schema (fetched async, cached)
Column names for the table referenced in the current FROM clause (parse the partial query)

Keep autocomplete simple in v1: keyword + table name completion only. Column-level completion is v2.

FilesExpand file tree

Claude.md

Latest commit

History

Claude.md

File metadata and controls

SQLumen — macOS Database Client

Project overview

Goals for this codebase

Tech stack

Architecture

DatabaseDriver protocol

ResultSet streaming

Safe Mode

Connection credentials

Feature roadmap

v1 — MVP (App Store launch)

v2

v3 / Pro

Database drivers — implementation order

1. SQLiteDriver (v1, week 1–2)

2. PostgreSQLDriver (v1, week 3–4)

3. MySQLDriver (v1, week 5–6)

Code conventions

Swift Concurrency

Error handling

Naming

Testing

Project structure

What to build first

App Store checklist

Competitive context

DuckDB driver notes

4. DuckDBDriver (v1, week 7)

Dark mode

Syntax highlighting

Dependencies

Integration pattern

Per-dialect keyword sets

Autocomplete