AppView in a box as a Vite plugin thing
hatk.dev
1# Multi-Database Support via Hexagonal Architecture
2
3## Motivation
4
5Support DuckDB, SQLite, and future PostgreSQL to give users a choice at project creation time and remove adoption barriers for users who can't or won't install DuckDB.
6
7Each hatk project commits to one database engine — no runtime switching.
8
9## Configuration
10
11Users set `database: 'duckdb' | 'sqlite'` in `hatk.config.ts`. At startup, hatk dynamically imports the matching adapter. Users only need the driver for their chosen database installed.
12
13## Architecture
14
15### Ports
16
17Two interfaces define the hexagonal boundary:
18
19**DatabasePort** — low-level SQL execution:
20
21```typescript
22interface DatabasePort {
23 open(path: string): Promise<void>
24 close(): Promise<void>
25
26 query<T>(sql: string, params?: unknown[]): Promise<T[]>
27 execute(sql: string, params?: unknown[]): Promise<void>
28
29 beginTransaction(): Promise<void>
30 commit(): Promise<void>
31 rollback(): Promise<void>
32
33 createBulkInserter(table: string, columns: string[]): Promise<BulkInserter>
34
35 dialect: Dialect
36}
37
38interface BulkInserter {
39 append(values: unknown[]): void
40 flush(): Promise<void>
41 close(): Promise<void>
42}
43
44type Dialect = 'duckdb' | 'sqlite' | 'postgres'
45```
46
47**SearchPort** — optional FTS capability:
48
49```typescript
50interface SearchPort {
51 createIndex(table: string, columns: string[]): Promise<void>
52 search(table: string, query: string, opts: SearchOpts): Promise<SearchResult[]>
53}
54```
55
56Adapters declare whether they implement `SearchPort`. When unavailable, hatk falls back to `LIKE` matching.
57
58### Dialect-Aware SQL Generation
59
60A `SqlDialect` helper provides per-engine variations so the shared layer avoids scattered conditionals:
61
62```typescript
63interface SqlDialect {
64 typeMap: Record<string, string>
65 param(index: number): string // $1 vs ?
66 supportsAppender: boolean
67 returningClause: boolean
68 upsertSyntax: 'on_conflict' | 'insert_or_replace'
69 jsonExtract(column: string, path: string): string
70}
71```
72
73Type mappings used by `schema.ts`:
74
75| Lexicon type | DuckDB | SQLite | Postgres |
76|-------------|-------------|-----------|---------------|
77| `string` | `TEXT` | `TEXT` | `TEXT` |
78| `integer` | `BIGINT` | `INTEGER` | `BIGINT` |
79| `boolean` | `BOOLEAN` | `INTEGER` | `BOOLEAN` |
80| `bytes` | `BLOB` | `BLOB` | `BYTEA` |
81| `datetime` | `TIMESTAMPTZ` | `TEXT` | `TIMESTAMPTZ` |
82
83SQLite stores booleans as integers and datetimes as text. The shared layer handles conversion at the binding/reading boundary.
84
85### Adapters
86
87**DuckDBAdapter** (~200-300 lines)
88- Wraps `@duckdb/node-api`
89- `BulkInserter` maps to DuckDB's native appender
90- Implements `SearchPort` using DuckDB's FTS extension
91- Read/write connection separation
92
93**SQLiteAdapter** (~200-300 lines)
94- Wraps `better-sqlite3`
95- `BulkInserter` batches rows into multi-row `INSERT` within a transaction
96- No `SearchPort` — falls back to `LIKE`
97- WAL mode for concurrent reads
98
99**PostgresAdapter** (future, ~200-300 lines)
100- Wraps `pg` (node-postgres)
101- `BulkInserter` uses `COPY FROM`
102- Implements `SearchPort` using `tsvector`/`tsquery`
103- Connection pooling
104
105### Adapter Loading
106
107```typescript
108async function createAdapter(config: HatkConfig): Promise<DatabasePort> {
109 switch (config.database) {
110 case 'duckdb': {
111 const { DuckDBAdapter } = await import('./adapters/duckdb.js')
112 return new DuckDBAdapter()
113 }
114 case 'sqlite': {
115 const { SQLiteAdapter } = await import('./adapters/sqlite.js')
116 return new SQLiteAdapter()
117 }
118 }
119}
120```
121
122### OAuth
123
124OAuth operations (sessions, tokens, keys, DPoP) go through the same `DatabasePort`. No separate database or port needed — the queries are simple CRUD.
125
126## File Structure
127
128All database code moves to `src/database/`:
129
130```
131src/database/
132 ports.ts # DatabasePort, BulkInserter, SearchPort interfaces
133 dialect.ts # SqlDialect interface + per-engine dialect configs
134 db.ts # Shared data access layer (refactored from current db.ts)
135 schema.ts # DDL generation (refactored from current schema.ts)
136 fts.ts # FTS dispatcher with LIKE fallback
137 adapters/
138 duckdb.ts # DuckDB adapter + SearchPort
139 sqlite.ts # SQLite adapter
140```
141
142The rest of the codebase (`server.ts`, `indexer.ts`, `main.ts`, etc.) imports from `database/db.ts` instead of `db.ts` — same API surface, different path.
143
144## Implementation
145
146Done as a single pass, not phased:
147
1481. Create `src/database/` with `ports.ts` and `dialect.ts`
1492. Extract DuckDB-specific code from current `db.ts` into `adapters/duckdb.ts`
1503. Refactor `db.ts` into `database/db.ts`, calling through `DatabasePort`
1514. Refactor `schema.ts` into `database/schema.ts`, using `SqlDialect.typeMap`
1525. Extract DuckDB FTS from `fts.ts` into adapter's `SearchPort`, add `LIKE` fallback
1536. Implement `SQLiteAdapter` in `adapters/sqlite.ts`
1547. Add `database` config option and dynamic adapter loading in startup
1558. Update all imports across the codebase
1569. Update `hatk new` scaffolding to include database choice