sqlite

SQLite Adapter - TDTP Framework

The SQLite adapter: two-way integration with SQLite 3.x.

Status

✅ Production Ready (v1.0)

Implemented:

• types.go — the full SQLite/TDTP type mapping
• adapter.go — connection through modernc.org/sqlite: pure Go, no cgo
• export.go — table export with TDTQL pushed into SQL
• import.go — import, creating tables as needed
• integration_test.go — integration tests
• benchmark_test.go — benchmarks
• ✅ Transaction support - BEGIN/COMMIT/ROLLBACK
• import strategies — REPLACE, IGNORE, FAIL

---

Requirements

• SQLite: 3.x, any version
• Driver: modernc.org/sqlite — pure Go, no cgo needed
• Go: 1.21+

---

Why pure Go matters here

• Portable — runs on any OS with no C library to compile
• Simple to deploy — no system library dependencies
• Docker-friendly — small images, no build tools
• Fast to build — no cgo overhead
• Stable — fewer version-compatibility surprises

---

What it does

Data types

SQLite's core types:

SQLite Type         TDTP Type       Back again
─────────────────────────────────────────────────
INTEGER             INTEGER         INTEGER
REAL                REAL            REAL
NUMERIC(p,s)        DECIMAL         NUMERIC(p,s)
TEXT                TEXT            TEXT
BOOLEAN             BOOLEAN         INTEGER (0/1)
DATE                DATE            DATE
DATETIME            TIMESTAMP       DATETIME
BLOB                BLOB            BLOB

Type aliases, per SQLite's affinity rules:

SQLite Type         TDTP Type       Note
─────────────────────────────────────────────────
INT, BIGINT,        INTEGER         INTEGER affinity
TINYINT, SMALLINT

FLOAT, DOUBLE       REAL            REAL affinity

VARCHAR(n),         TEXT            TEXT affinity
CHAR(n), CLOB

DECIMAL(p,s)        DECIMAL         NUMERIC affinity

SQLite's peculiarities

Dynamic typing:

• SQLite does not enforce types strictly
• any column can hold any type
• the adapter goes by the declared type in the schema

How BOOLEAN is stored:

• SQLite has no native BOOLEAN
• it is stored as INTEGER: 0 for false, 1 for true
• converted automatically on export and import

AUTOINCREMENT:

• detected automatically for an INTEGER PRIMARY KEY
• preserved on import when the data carries the ID
• can be omitted, to let SQLite generate it

Transactions:

• full ACID transactions
• the default isolation level is SERIALIZABLE
• automatic rollback on error

---

Installing

go get github.com/queuebridge/tdtp/pkg/adapters/sqlite

Importing the package

import (
    "context"
    "github.com/queuebridge/tdtp/pkg/adapters"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"  // registers the adapter
)

---

Usage

Connecting

Through the factory:

import (
    "context"
    "github.com/queuebridge/tdtp/pkg/adapters"
)

ctx := context.Background()

cfg := adapters.Config{
    Type: "sqlite",
    DSN:  "myapp.db",
}

adapter, err := adapters.New(ctx, cfg)
if err != nil {
    panic(err)
}
defer adapter.Close(ctx)

Constructed directly:

import "github.com/queuebridge/tdtp/pkg/adapters/sqlite"

adapter, err := sqlite.NewAdapter(ctx, "database.db")
defer adapter.Close(ctx)

DSN forms:

// A plain file path
"app.db"
"./data/database.db"
"/var/lib/myapp/data.db"

// An in-memory database, for tests
":memory:"

// A SQLite URI, for the extended options
"file:test.db?mode=rw&cache=shared"
"file:readonly.db?mode=ro"

---

Export

Exporting a whole table:

import "github.com/queuebridge/tdtp/pkg/core/packet"

packets, err := adapter.ExportTable(ctx, "users")
if err != nil {
    panic(err)
}

// Write it to a file
for i, pkt := range packets {
    filename := fmt.Sprintf("users_part_%d.xml", i+1)
    generator := packet.NewGenerator()
    generator.WriteToFile(pkt, filename)
}

Exporting with a TDTQL filter:

import "github.com/queuebridge/tdtp/pkg/core/tdtql"

// SQL translated into TDTQL
translator := tdtql.NewTranslator()
query, err := translator.TranslateSQL(
    "SELECT * FROM users WHERE age >= 18 AND status = 'active' ORDER BY created_at DESC LIMIT 100"
)

// Exported with the filtering done by the database
packets, err := adapter.ExportTableWithQuery(ctx, "users", query)

What the exported schema looks like:

<Schema>
  <Field name="id" type="INTEGER" key="true"></Field>
  <Field name="username" type="TEXT" length="100"></Field>
  <Field name="age" type="INTEGER"></Field>
  <Field name="balance" type="DECIMAL" precision="18" scale="2"></Field>
  <Field name="is_active" type="BOOLEAN"></Field>
  <Field name="created_at" type="TIMESTAMP" timezone="UTC"></Field>
</Schema>

---

Import

Tables are created for you:

// A missing table is created automatically,
// from the schema in the TDTP packet
err = adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

// The table created:
// CREATE TABLE users (
//     id INTEGER PRIMARY KEY,
//     username TEXT,
//     age INTEGER,
//     balance NUMERIC(18,2),
//     is_active INTEGER,
//     created_at DATETIME
// )

The REPLACE strategy — full replacement:

// Deletes every row, then inserts the new ones
err = adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

The IGNORE strategy — skip duplicates:

// Uses INSERT OR IGNORE to skip existing primary keys
err = adapter.ImportPacket(ctx, packet, adapters.StrategyIgnore)

The FAIL strategy — error on a duplicate:

// Raises an error on a primary-key violation
err = adapter.ImportPacket(ctx, packet, adapters.StrategyFail)

Batch import:

// Import several packets
for _, pkt := range packets {
    err = adapter.ImportPacket(ctx, pkt, adapters.StrategyReplace)
    if err != nil {
        return err
    }
}

---

Transactions

// Begin
tx, err := adapter.BeginTx(ctx)
if err != nil {
    panic(err)
}

// Import inside the transaction
err = tx.ImportPacket(ctx, usersPacket, adapters.StrategyReplace)
if err != nil {
    tx.Rollback(ctx)
    panic(err)
}

err = tx.ImportPacket(ctx, ordersPacket, adapters.StrategyReplace)
if err != nil {
    tx.Rollback(ctx)
    panic(err)
}

// Commit
err = tx.Commit(ctx)
if err != nil {
    panic(err)
}

---

Listing tables

tables, err := adapter.ListTables(ctx)
if err != nil {
    panic(err)
}

for _, table := range tables {
    fmt.Println(table)
}
// Output:
// users
// orders
// products

---

Migration scenarios

SQLite → PostgreSQL (upgrade)

import (
    "context"
    "github.com/queuebridge/tdtp/pkg/adapters"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"
    _ "github.com/queuebridge/tdtp/pkg/adapters/postgres"
)

ctx := context.Background()

// Source: SQLite
sourceCfg := adapters.Config{
    Type: "sqlite",
    DSN:  "app.db",
}
source, _ := adapters.New(ctx, sourceCfg)
defer source.Close(ctx)

// Target: PostgreSQL
targetCfg := adapters.Config{
    Type: "postgres",
    DSN:  "postgres://user:pass@localhost/production",
}
target, _ := adapters.New(ctx, targetCfg)
defer target.Close(ctx)

// Migrate every table
tables, _ := source.ListTables(ctx)
for _, table := range tables {
    packets, _ := source.ExportTable(ctx, table)
    for _, pkt := range packets {
        target.ImportPacket(ctx, pkt, adapters.StrategyReplace)
    }
}
// Types are converted for you:
// SQLite INTEGER → PostgreSQL INT
// SQLite TEXT → PostgreSQL VARCHAR
// SQLite REAL → PostgreSQL DOUBLE PRECISION

PostgreSQL → SQLite (backup/downgrade)

// Source: PostgreSQL (production)
source, _ := adapters.New(ctx, adapters.Config{
    Type: "postgres",
    DSN:  "postgres://user:pass@prod-server/db",
})

// Target: SQLite (local backup)
target, _ := adapters.New(ctx, adapters.Config{
    Type: "sqlite",
    DSN:  "backup.db",
})

// Making a local backup
packets, _ := source.ExportTable(ctx, "critical_data")
target.ImportPacket(ctx, packets[0], adapters.StrategyReplace)

// PostgreSQL's special types convert like this:
// UUID → TEXT
// JSONB → TEXT — the JSON is kept as a string
// TIMESTAMPTZ → DATETIME

SQLite to SQLite — replication

// Master database
master, _ := sqlite.NewAdapter(ctx, "master.db")

// Replica database
replica, _ := sqlite.NewAdapter(ctx, "replica.db")

// Replicate a table
packets, _ := master.ExportTable(ctx, "users")
replica.ImportPacket(ctx, packets[0], adapters.StrategyReplace)

---

Examples

1. Basic SQLite use

package main

import (
    "context"
    "fmt"

    "github.com/queuebridge/tdtp/pkg/adapters"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"
)

func main() {
    ctx := context.Background()

    cfg := adapters.Config{
        Type: "sqlite",
        DSN:  "test.db",
    }

    adapter, err := adapters.New(ctx, cfg)
    if err != nil {
        panic(err)
    }
    defer adapter.Close(ctx)

    // List the tables
    tables, _ := adapter.ListTables(ctx)
    fmt.Println("Tables:", tables)

    // Export
    packets, _ := adapter.ExportTable(ctx, "users")
    fmt.Printf("Exported %d packets\n", len(packets))

    // Import
    err = adapter.ImportPacket(ctx, packets[0], adapters.StrategyReplace)
    if err != nil {
        panic(err)
    }
}

2. SQLite to a message queue

package main

import (
    "context"

    "github.com/queuebridge/tdtp/pkg/adapters"
    "github.com/queuebridge/tdtp/pkg/brokers"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"
)

func main() {
    ctx := context.Background()

    // SQLite adapter
    cfg := adapters.Config{
        Type: "sqlite",
        DSN:  "orders.db",
    }
    adapter, _ := adapters.New(ctx, cfg)
    defer adapter.Close(ctx)

    // RabbitMQ broker
    broker := brokers.NewRabbitMQ("amqp://guest:guest@localhost:5672/")
    broker.Connect()
    defer broker.Close()

    // Export → Publish
    packets, _ := adapter.ExportTable(ctx, "orders")
    for _, pkt := range packets {
        broker.Publish("orders_queue", pkt)
    }
}

3. An in-memory database for tests

package myapp

import (
    "context"
    "testing"

    "github.com/queuebridge/tdtp/pkg/adapters"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"
)

func TestMyFunction(t *testing.T) {
    ctx := context.Background()

    // In-memory, so each test is isolated
    cfg := adapters.Config{
        Type: "sqlite",
        DSN:  ":memory:",
    }

    adapter, err := adapters.New(ctx, cfg)
    if err != nil {
        t.Fatal(err)
    }
    defer adapter.Close(ctx)

    // Load the fixtures
    // ... import TDTP packets holding the test data

    // Your tests go here
}

4. Incremental synchronisation

package main

import (
    "context"
    "time"

    "github.com/queuebridge/tdtp/pkg/adapters"
    "github.com/queuebridge/tdtp/pkg/core/tdtql"
    _ "github.com/queuebridge/tdtp/pkg/adapters/sqlite"
)

func main() {
    ctx := context.Background()

    source, _ := adapters.New(ctx, adapters.Config{
        Type: "sqlite",
        DSN:  "source.db",
    })

    target, _ := adapters.New(ctx, adapters.Config{
        Type: "sqlite",
        DSN:  "target.db",
    })

    // Export only what changed in the last hour
    translator := tdtql.NewTranslator()
    lastHour := time.Now().Add(-1 * time.Hour).Format("2006-01-02 15:04:05")

    query, _ := translator.TranslateSQL(
        fmt.Sprintf("SELECT * FROM orders WHERE updated_at > '%s'", lastHour))

    packets, _ := source.ExportTableWithQuery(ctx, "orders", query)

    // Import the changes
    for _, pkt := range packets {
        target.ImportPacket(ctx, pkt, adapters.StrategyReplace)
    }
}

---

Performance

Benchmarks

Measured on SQLite 3.x, an SSD, and the modernc.org/sqlite driver:

Operation	Throughput
Export (1000 rows)	~15ms
Export (10,000 rows)	~100ms
Export (100,000 rows)	~900ms
Import REPLACE (1000 rows)	~25ms
Import IGNORE (1000 rows)	~30ms
TDTQL filter (10K rows)	~50ms
Table creation	~5ms

Straight from benchmark_test.go:

BenchmarkExport1000Rows-8        500     2,500,000 ns/op   (2.5ms)
BenchmarkExport10000Rows-8        50    20,000,000 ns/op   (20ms)
BenchmarkImportReplace1000-8     400     3,000,000 ns/op   (3ms)

What is optimised

• Prepared statements — every query is cached
• Batch inserts — INSERTs grouped inside transactions
• WAL mode — write-ahead logging, for better concurrency
• PRAGMA synchronous=NORMAL — fast writes that still hold their guarantees ✅ Push-down execution - TDTQL → SQL WHERE/ORDER BY
• Indexes — created automatically for the primary key

Advice

1. Use transactions for batch work
2. WAL mode for concurrent reads and writes (PRAGMA journal_mode=WAL)
3. A batch of 1000–5000 rows is the sweet spot
4. :memory: for tests and throwaway data
5. VACUUM regularly to keep the file size down

---

Troubleshooting

"database is locked"

Cause: concurrent write access.
Fix 1: use WAL mode (PRAGMA journal_mode=WAL)
Fix 2: retry with exponential backoff
Fix 3: reduce the number of concurrent writers

"no such table"

Fix: the adapter creates tables automatically on import.
Check that the TDTP packet carries a valid schema.

A large import is slow

Fix 1: use transactions — batching is automatic
Fix 2: drop the indexes before importing
Fix 3: PRAGMA synchronous=OFF — for an initial bulk load only

The database file is large

Fix: run VACUUM to defragment it

---

Compatibility

• SQLite: 3.x, any version
• OS: Linux, Windows, macOS, BSD — pure Go runs everywhere
• Architecture: amd64, arm64, 386, arm — all supported
• Docker: fully supported, with no cgo dependencies

---

Against the other driver

	modernc.org/sqlite (pure Go)	mattn/go-sqlite3 (cgo)
Needs cgo	no	yes
Cross-compiling	easy	awkward
Docker image	small	large, needs build tools
Build speed	fast	slow, there is C to compile
Throughput	about 90% of cgo	100%
Maintenance	✅ Pure Go	⚠️ C dependencies

In short: the pure Go driver is the better choice in almost every case.

---

See also

• Adapter interface — the interface every adapter implements
• TDTP specification — the protocol
• PostgreSQL adapter
• MySQL adapter
• MS SQL Server adapter
• examples/01-basic-export — the simplest worked example

---

Licence

MIT

---

Version: 1.0 Driver: modernc.org/sqlite (Pure Go) Last updated: 2025-12-08