go-postgres

A lightweight, pure Go database/sql driver that accepts PostgreSQL SQL syntax but executes against SQLite under the hood via ncruces/go-sqlite3. This lets Go applications written for PostgreSQL run against a local SQLite file -- ideal for testing, embedded use, CLI tools, and development environments. Files remain SQLite-compatible.

The driver registers as "pglike" to avoid conflicts with existing PG drivers (lib/pq, pgx).

Installation

go get git.bytestone.uk/hum3/go-postgres

Quick Start

package main

import (
    "database/sql"
    "fmt"
    _ "git.bytestone.uk/hum3/go-postgres"
)

func main() {
    db, _ := sql.Open("pglike", "example.db")
    defer db.Close()

    db.Exec(`CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) UNIQUE,
        active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT NOW()
    )`)

    db.Exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com")

    rows, _ := db.Query("SELECT id, name, active FROM users WHERE active = TRUE")
    defer rows.Close()
    for rows.Next() {
        var id int64
        var name string
        var active int64
        rows.Scan(&id, &name, &active)
        fmt.Printf("id=%d name=%s active=%d\n", id, name, active)
    }
}

Architecture

User Go Code
    |  sql.Open("pglike", "myapp.db")
    v
database/sql
    |
    v
go-postgres driver (this project)
    |  1. Translate PG SQL -> SQLite SQL
    |  2. Register PG-compatible functions
    |  3. Delegate to SQLite engine
    v
ncruces/go-sqlite3 (SQLite via wasm2go — no CGo)
    |
    v
SQLite database file

DSN Formats

The driver accepts several DSN formats:

Format	Example	Behaviour
SQLite file path	myapp.db	Opens the file directly
SQLite URI	file:myapp.db?_pragma=foreign_keys(1)	Passed through to SQLite
In-memory	:memory:	SQLite in-memory database (pooling handled automatically)
PostgreSQL URL	postgres://user:pass@localhost/myapp	Extracts myapp as filename myapp.db
PG key=value	host=localhost dbname=myapp	Extracts myapp as filename myapp.db

DDL Type Mappings

PostgreSQL	SQLite
SERIAL / BIGSERIAL / SMALLSERIAL	INTEGER PRIMARY KEY AUTOINCREMENT
BOOLEAN / BOOL	INTEGER
VARCHAR(n) / CHARACTER VARYING(n)	TEXT
CHAR(n) / CHARACTER(n)	TEXT
TIMESTAMP / TIMESTAMP WITH TIME ZONE / TIMESTAMPTZ	TEXT
DATE	TEXT
TIME / TIME WITH TIME ZONE / TIMETZ	TEXT
UUID	TEXT
BYTEA	BLOB
JSON / JSONB	TEXT
SMALLINT / INT2	INTEGER
INTEGER / INT / INT4	INTEGER
BIGINT / INT8	INTEGER
REAL / FLOAT4	REAL
DOUBLE PRECISION / FLOAT8	REAL
NUMERIC(p,s) / DECIMAL(p,s)	TEXT
TEXT	TEXT
INTERVAL	TEXT

Expression Translations

PostgreSQL	SQLite
expr::type	CAST(expr AS mapped_type)
ILIKE	LIKE
TRUE	1
FALSE	0
E'escape\nstring'	'escape' || char(10) || 'string'
expr IS TRUE	expr = 1
expr IS FALSE	expr = 0
expr IS NOT TRUE	expr != 1
expr IS NOT FALSE	expr != 0
$1, $2, ...	?
DEFAULT NOW()	DEFAULT (datetime('now'))

Function Translations

PostgreSQL	SQLite
NOW()	datetime('now')
CURRENT_DATE	date('now')
CURRENT_TIME	time('now')
CURRENT_TIMESTAMP	datetime('now')
date_trunc('day', expr)	date(expr)
date_trunc('hour', expr)	strftime('%Y-%m-%d %H:00:00', expr)
date_trunc('minute', expr)	strftime('%Y-%m-%d %H:%M:00', expr)
date_trunc('month', expr)	strftime('%Y-%m-01', expr)
date_trunc('year', expr)	strftime('%Y-01-01', expr)
EXTRACT(field FROM expr)	CAST(strftime(fmt, expr) AS INTEGER)
date_part('field', expr)	CAST(strftime(fmt, expr) AS INTEGER)
left(str, n)	substr(str, 1, n)
right(str, n)	substr(str, -n)
concat(a, b, ...)	(COALESCE(a,'') || COALESCE(b,'') || ...)
string_agg(expr, sep)	group_concat(expr, sep)
array_agg(expr)	json_group_array(expr)
to_char(ts, fmt)	strftime(mapped_fmt, ts)

Registered PG-Compatible Functions

These functions are registered as SQLite custom functions and can be called directly:

Function	Description
gen_random_uuid()	Returns a random UUID v4 string
md5(string)	Returns the hex-encoded MD5 hash
split_part(string, delimiter, field)	Returns the nth field (1-indexed)
pg_typeof(expr)	Returns the SQLite type name of the expression
current_schema()	Returns 'public'
current_database()	Returns 'main'

Catalog Views

PG-style catalog queries work out of the box. On every new connection, the driver installs TEMP VIEWs that expose the same shape as PostgreSQL's information_schema and pg_indexes, backed by SQLite's own catalog (sqlite_master, pragma_table_info, pragma_foreign_key_list, pragma_index_list/_info). The translator rewrites canonical PG references to the underlying view names so the same SQL runs on pglike and real Postgres.

Catalog reference	Notes
information_schema.tables	table_catalog, table_schema='public', table_name, table_type (BASE TABLE or VIEW)
information_schema.columns	column_name, data_type, is_nullable, ordinal_position (1-based), column_default
information_schema.table_constraints	PRIMARY KEY, FOREIGN KEY, UNIQUE rows; constraint names synthesised (<table>_pkey, <table>_fk_<id>)
information_schema.key_column_usage	One row per column in a PK or FK
information_schema.referential_constraints	FK metadata: update_rule, delete_rule
information_schema.constraint_column_usage	Columns referenced by a constraint (parent-side for FKs)
pg_indexes	schemaname='public', tablename, indexname, indexdef (raw CREATE INDEX text)
pg_index_columns	pglike-specific: index columns flat — indexname, column_name, ordinal_position, is_unique

Example — list tables and find the FK columns of a table:

db, _ := sql.Open("pglike", ":memory:")
db.Query(`SELECT table_name FROM information_schema.tables
          WHERE table_schema = current_schema()`)

db.Query(`SELECT kcu.column_name, ccu.table_name, ccu.column_name
          FROM information_schema.referential_constraints rc
          JOIN information_schema.key_column_usage kcu
            ON rc.constraint_name = kcu.constraint_name
          JOIN information_schema.constraint_column_usage ccu
            ON rc.unique_constraint_name = ccu.constraint_name
          WHERE kcu.table_name = $1`, "posts")

The same queries run against real Postgres without modification.

WASM Support

The driver works under GOOS=wasip1 GOARCH=wasm. The underlying SQLite engine (ncruces/go-sqlite3) embeds SQLite compiled to Go via wasm2go, so there is no CGo or runtime WASM interpreter dependency.

:memory: connection pooling

Go's database/sql connection pool can open multiple connections. With :memory:, each connection normally gets its own empty database. The driver handles this automatically:

• Native: creates a temp file so all pool connections share one database (full concurrency)
• WASM: ncruces WASM modules have isolated filesystems, so temp files can't be shared across connections. The driver detects this and falls back to a single shared connection with mutex serialization.

No user configuration is needed — sql.Open("pglike", ":memory:") works correctly in both environments.

Running tests under WASM

task test:wasm

File Structure

go-postgres/
  driver.go                 Driver, connector, DSN parsing, connection pooling
  driver_go18.go            Context-aware interfaces
  translate.go              Core tokenizer + translation pipeline
  translate_ddl.go          DDL type mappings (SERIAL, BOOLEAN, VARCHAR, etc.)
  translate_expr.go         Expression translations (::cast, ILIKE, TRUE/FALSE, E'strings')
  translate_func.go         Function translations (NOW, date_trunc, EXTRACT, etc.)
  translate_genseries.go    generate_series() → recursive CTE rewriting
  translate_interval.go     INTERVAL literal parsing and arithmetic
  translate_order.go        NULLS FIRST/LAST ordering support
  translate_sequence.go     CREATE/DROP SEQUENCE emulation
  translate_catalog.go      information_schema/pg_catalog reference rewriting
  catalog.go                TEMP VIEW DDLs for information_schema and pg_indexes
  pgfuncs.go                PG-compat functions registered in SQLite
  pgerror.go                PG SQLSTATE error code wrapping
  foreign_key_test.go       Foreign key constraint tests
  soak_test.go              Soak / stress tests
  driver_test.go            Integration tests (full SQL round-trips)
  translate_test.go         Unit tests for all translations
  wasm_test.go              WASM cross-compilation tests
  example/main.go           Usage example

Links

Documentation	https://h3-go-postgres.statichost.page/
Source (Codeberg)	https://git.bytestone.uk/hum3/go-postgres
Mirror (GitHub)	https://git.bytestone.uk/hum3/go-postgres

License

MIT