postgres

PostgreSQL Adapter - TDTP Framework

The PostgreSQL adapter: two-way integration with PostgreSQL 12 and later.

Status

In development (v0.9)

Done:

• types.go — PostgreSQL to TDTP type mapping
• adapter.go — connection through a pgx pool
• export.go — table export (in progress)
• import.go — data import (in progress)
• integration_test.go — integration tests (in progress)

What it does

Data types

Standard types:

PostgreSQL          TDTP            Back again
─────────────────────────────────────────────
INTEGER             INTEGER         INTEGER
BIGINT              INTEGER         BIGINT
NUMERIC(18,2)       DECIMAL         NUMERIC(18,2)
VARCHAR(100)        TEXT            VARCHAR(100)
TEXT                TEXT            TEXT
BOOLEAN             BOOLEAN         BOOLEAN
DATE                DATE            DATE
TIMESTAMP           TIMESTAMP       TIMESTAMP
BYTEA               BLOB            BYTEA

PostgreSQL-specific types, carried through subtype:

PostgreSQL          TDTP                        Back again
───────────────────────────────────────────────────────────
UUID                TEXT (subtype="uuid")       UUID
JSONB               TEXT (subtype="jsonb")      JSONB
JSON                TEXT (subtype="json")       JSON
INET                TEXT (subtype="inet")       INET
CIDR                TEXT (subtype="cidr")       CIDR
MACADDR             TEXT (subtype="macaddr")    MACADDR
INTEGER[]           TEXT (subtype="array")      INTEGER[]
TIMESTAMPTZ         TIMESTAMP (subtype="tz")    TIMESTAMPTZ
SERIAL              INTEGER (subtype="serial")  SERIAL

Migration scenarios

SQLite → PostgreSQL:

source := sqlite.NewAdapter("app.db")
target := postgres.NewAdapter("postgresql://user:pass@localhost/db")

packets, _ := source.ExportTable("Users")
target.ImportPackets(packets, postgres.StrategyReplace)
// Types are converted for you

PostgreSQL → SQLite (downgrade):

source := postgres.NewAdapter("postgresql://...")
target := sqlite.NewAdapter("backup.db")

// UUID and JSONB become TEXT on export
packets, _ := source.ExportTable("Users")
target.ImportPackets(packets, sqlite.StrategyReplace)

Usage

Connecting

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

// The ordinary case (schema: public)
adapter, err := postgres.NewAdapter(
    "postgresql://tdtp_user:password@localhost:5432/tdtp_test"
)
defer adapter.Close()

// Naming a schema
adapter, err := postgres.NewAdapterWithSchema(
    "postgresql://...",
    "myschema",
)

Export (TODO)

// A full export
packets, err := adapter.ExportTable("Users")

// With a filter
translator := tdtql.NewTranslator()
query, _ := translator.Translate("SELECT * FROM Users WHERE active = true")
packets, err := adapter.ExportTableWithQuery("Users", query, "App", "Server")

Import (TODO)

// Import, replacing
err := adapter.ImportPacket(packet, postgres.StrategyReplace)

// Import, ignoring duplicates
err := adapter.ImportPacket(packet, postgres.StrategyIgnore)

// Several packets, imported atomically
err := adapter.ImportPackets(packets, postgres.StrategyReplace)

Installing dependencies

go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool

Testing

PostgreSQL in Docker (already running)

# Check the container
docker ps | grep postgres

# Connect, to confirm
docker exec -it tdtp-postgres psql -U tdtp_user -d tdtp_test

Connection string for tests:

postgresql://tdtp_user:tdtp_dev_pass_2025@localhost:5432/tdtp_test

Running the tests, once they exist

cd pkg/adapters/postgres
go test -v
go test -bench=. -benchmem

Implementation notes

Connection Pooling

• MaxConns: 10 — the connection ceiling
• MinConns: 2 — kept warm
• Driver: pgx/v5 — current and fast

Working with schemas

// List every schema
schemas, _ := adapter.GetSchemas()

// Switch schema
adapter.SetSchema("custom_schema")

// Check for a table in the current schema
exists, _ := adapter.TableExists("Users")

Identifier handling

PostgreSQL is case-sensitive for quoted identifiers:

// Quoted for you
QuoteIdentifier("User")   → "User"
QuoteIdentifier("user")   → user
QuoteIdentifier("order")  → "order" (reserved word)

Roadmap

v0.9 (current)

• export.go — read the schema through information_schema
• export.go — export data, every type supported
• import.go - COPY-based bulk insert
• import.go — handle sequences
• integration_test.go — full coverage
• Array support (PostgreSQL-specific)
• Benchmark PostgreSQL vs SQLite

v1.0 (planned)

• Partitioned tables
• Foreign keys and constraints
• Schema migration (ALTER TABLE)
• Incremental synchronisation

Worked examples

A migration involving PostgreSQL types

// A PostgreSQL table with UUID and JSONB
CREATE TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(100),
    metadata JSONB,
    ip INET
);

// Export through TDTP
packets, _ := pgAdapter.ExportTable("users")

// In the TDTP packet:
<Schema>
  <Field name="id" type="TEXT" subtype="uuid" key="true"/>
  <Field name="name" type="TEXT" length="100"/>
  <Field name="metadata" type="TEXT" length="-1" subtype="jsonb"/>
  <Field name="ip" type="TEXT" subtype="inet"/>
</Schema>

// Import back into PostgreSQL
pgAdapter2.ImportPacket(packet, postgres.StrategyReplace)
// → UUID, JSONB and INET are restored

Cross-platform migration

// PostgreSQL (source)
pgSrc, _ := postgres.NewAdapter("postgresql://src/db")
packets, _ := pgSrc.ExportTable("products")

// SQLite as the target — types downgrade automatically
sqliteTgt, _ := sqlite.NewAdapter("products.db")
sqliteTgt.ImportPackets(packets, sqlite.StrategyReplace)
// UUID → TEXT, JSONB → TEXT, etc.

Known limitations

1. Arrays: stored as TEXT with subtype="array"; the exact element type is not preserved
2. Composite types: unsupported (TODO v1.1)
3. Domains: treated as their base type
4. Enums: stored as TEXT (TODO v1.1)

The PostgreSQL adapter is the workhorse for enterprise migrations.

Part of TDTP Framework v0.9