pgqt acts as a middleware server that translates the PostgreSQL wire protocol into SQLite operations. It provides:

• Full PostgreSQL Compatibility: Connect using psql, pgAdmin, DBeaver, or any PostgreSQL driver
• Type Preservation: Original PostgreSQL types (SERIAL, VARCHAR, TIMESTAMPTZ, etc.) are stored in a shadow catalog for reversible migrations
• SQL Transpilation: PostgreSQL-specific syntax is automatically rewritten for SQLite compatibility
• ORM Support: Works with Prisma, TypeORM, Drizzle, and other modern ORMs
• Schema Support: Full namespace support using SQLite ATTACH DATABASE
• Role-Based Access Control (RBAC): PostgreSQL-compatible users, roles, and permission management
• Row-Level Security (RLS): Fine-grained access control at the row level
• Full-Text Search (FTS): PostgreSQL-compatible full-text search using to_tsvector, to_tsquery, and the @@ match operator
• Range Types: PostgreSQL range types (int4range, daterange, etc.) with canonicalization and overlap operators
• Vector Search: pgvector-compatible vector similarity search for AI/ML applications
• Geometric Types: 2D geometric data types (point, box, circle, line, lseg, path, polygon) with spatial operators and distance functions.
• Trigger Support: PostgreSQL-compatible triggers using PL/pgSQL logic. See docs/TRIGGERS.md.
• Window Functions: Full support for all PostgreSQL window functions (row_number, rank, lag, lead, etc.) with frame specifications
• LATERAL Joins: Supported for table-valued functions (like jsonb_each). See docs/LATERAL.md.
• COPY Command: Full support for COPY FROM STDIN and COPY TO STDOUT in TEXT, CSV, and BINARY formats with character encoding support (UTF8, LATIN1, WINDOWS-1252, EUC_JP, etc.) for efficient data transfer. See docs/COPY.md and docs/copy-encoding.md.
• Enum Type Support: PostgreSQL enum types via CREATE TYPE ... AS ENUM, stored as TEXT with CHECK constraints. See docs/ENUMS.md.
• Session Configuration: Support for SET, SHOW, set_config(), and current_setting() for per-session configuration. See docs/SETTINGS.md.
• COMMENT ON Support: Metadata persistence for comments on tables, columns, and other database objects.
• Performance Tuning: SQLite PRAGMA configuration, connection pooling, caching, and memory management. See docs/performance-tuning.md.

# Clone and build
git clone https://github.com/yourusername/pgqt
cd pgqt

# Build with TLS support (default, ~12MB)
cargo build --release

# Or build smaller binary without TLS (~9.5MB)
cargo build --release --no-default-features --features plpgsql

# Or use the build scripts
./build-release.sh        # With TLS
./build-release-small.sh  # Without TLS

# Or install via cargo
cargo install pgqt

Note: See docs/build-options.md for detailed build configuration options.

# Start with defaults (test.db on port 5432)
./target/release/pgqt

# Or specify custom database and port
PGQT_DB=myapp.db PGQT_PORT=5433 ./target/release/pgqt

# Or use command-line options
./target/release/pgqt --database myapp.db --port 5433

# With output logging to files
./target/release/pgqt -d myapp.db -o server.log -e errors.log

# Using psql
psql -h 127.0.0.1 -p 5432 -U postgres

# Using connection string
postgresql://postgres@127.0.0.1:5432/test.db

For complete type documentation, see docs/DATA_TYPES.md.

The proxy automatically rewrites PostgreSQL-specific syntax:

-- Type casts
SELECT '1'::int;           -- → SELECT CAST('1' AS INTEGER)

-- Functions
SELECT now();              -- → SELECT datetime('now')

-- Schema references (public stripped, others preserved)
SELECT * FROM public.users;      -- → SELECT * FROM users
SELECT * FROM inventory.products; -- → SELECT * FROM inventory.products

-- Operators
WHERE name ~~ 'alice%';    -- → WHERE name LIKE 'alice%'

pgqt implements PostgreSQL schema support using SQLite's ATTACH DATABASE feature. Each schema maps to a separate SQLite database file.

-- Create a new schema
CREATE SCHEMA inventory;

-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS analytics;

-- Create table in a specific schema
CREATE TABLE inventory.products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price REAL
);

-- Query with schema prefix
SELECT * FROM inventory.products;

-- Cross-schema joins
SELECT p.name, u.email
FROM inventory.products p
JOIN public.users u ON p.user_id = u.id;

-- Show current search path
SHOW search_path;

-- Set search path
SET search_path TO inventory, public;

-- List all schemas
SELECT nspname FROM pg_namespace;

For complete documentation, see docs/SCHEMAS.md.

pgqt implements PostgreSQL-compatible role-based access control, allowing you to manage users, roles, and permissions:

-- Create a basic role
CREATE ROLE app_user WITH LOGIN;

-- Create a superuser role
CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE;

-- Create a role with password
CREATE ROLE readonly WITH LOGIN PASSWORD 'secure_password';

-- Grant table-level privileges
GRANT SELECT ON users TO readonly;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT ALL PRIVILEGES ON products TO admin;

-- Grant schema-level privileges
GRANT USAGE, CREATE ON SCHEMA inventory TO app_user;
GRANT USAGE ON SCHEMA public TO readonly;

-- Grant function-level privileges
GRANT EXECUTE ON FUNCTION add_numbers(int, int) TO app_user;

-- Grant role membership (role inheritance)
GRANT app_user TO readonly;
GRANT admin TO app_user;

-- Automatically grant SELECT on all future tables in a schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Automatically grant EXECUTE on all future functions
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO app_user;

-- Revoke specific privileges
REVOKE DELETE ON orders FROM app_user;
REVOKE INSERT ON users FROM readonly;

-- Revoke role membership
REVOKE admin FROM app_user;

-- Switch to a different role (if you have membership)
SET ROLE app_user;

-- View current role
SELECT current_user;

-- Reset to original login role
SET ROLE NONE;

The proxy enforces permissions on all DML and DDL operations:

• SELECT: Requires SELECT privilege on table and USAGE on schema
• INSERT: Requires INSERT privilege on table and USAGE on schema
• UPDATE: Requires UPDATE privilege on table and USAGE on schema
• DELETE: Requires DELETE privilege on table and USAGE on schema
• TRUNCATE: Requires table ownership or TRUNCATE privilege
• EXECUTE: Requires EXECUTE privilege on function
• DDL: Requires superuser, schema CREATE privilege, or object ownership

Permission Resolution:

1. Superusers bypass all permission checks
2. Object owners (and schema owners for tables/functions) have implicit all privileges
3. Privileges are inherited through role membership
4. PUBLIC grants apply to all roles
5. Default privileges are applied during object creation

Query PostgreSQL-compatible system catalogs:

-- List all roles
SELECT * FROM pg_roles;

-- View role memberships
SELECT * FROM pg_auth_members;

-- Check table permissions
SELECT * FROM has_table_privilege('app_user', 'users', 'SELECT');

-- Check schema permissions
SELECT * FROM has_schema_privilege('app_user', 'public', 'USAGE');

-- Check function permissions
SELECT * FROM has_function_privilege('app_user', 'add_numbers(int,int)', 'EXECUTE');

-- View table ownership
SELECT relname, rolname as owner
FROM pg_class c
JOIN pg_roles r ON c.relowner = r.oid;

-- 1. Create roles
CREATE ROLE admin WITH SUPERUSER;
CREATE ROLE app_user WITH LOGIN;
CREATE ROLE readonly WITH LOGIN;

-- 2. Create tables (admin owns them)
CREATE TABLE users (id SERIAL, name TEXT);
CREATE TABLE orders (id SERIAL, user_id INT, total REAL);

-- 3. Grant permissions
GRANT SELECT ON users TO readonly;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT ALL ON users TO app_user;

-- 4. Test permissions
-- As readonly: SELECT works, INSERT fails
SET ROLE readonly;
SELECT * FROM users;           -- ✅ Success
INSERT INTO users VALUES (1);  -- ❌ Permission denied

-- As app_user: Can modify users and orders
SET ROLE app_user;
INSERT INTO users VALUES (1, 'Alice');  -- ✅ Success
INSERT INTO orders VALUES (1, 1, 99.99); -- ✅ Success
DELETE FROM users WHERE id = 1;         -- ❌ Permission denied

pgqt supports multiple authentication modes for different security requirements:

By default, pgqt requires valid credentials:

# Start with password authentication (default)
pgqt --database myapp.db

Users must exist in the pg_authid catalog and provide correct passwords:

-- Create a user with password
CREATE USER app_user WITH PASSWORD 'secure_password';

For development or trusted environments, disable authentication entirely:

# Accept any connection without authentication
pgqt --database myapp.db --trust-mode

⚠️ Warning: Trust mode allows any client to connect as any user without credentials. Never use in production.

For development convenience, pgqt can automatically create users on first connection:

# Auto-create users that don't exist
pgqt --database myapp.db --auto-create-users

When enabled:

• Any username can connect (no prior CREATE USER needed)
• Auto-created users get full superuser privileges
• No password is set for auto-created users
• Users can authenticate with any password (or no password)

⚠️ Security Warning: This mode is insecure and intended for development only. Auto-created users have unrestricted access to the database.

Example: Development Setup

# Use auto-create-users for rapid development
pgqt --database dev.db --auto-create-users --debug

# Then connect with any username
psql -h 127.0.0.1 -p 5432 -U any_username

pgqt implements PostgreSQL-compatible Row-Level Security (RLS), enabling fine-grained access control at the row level based on the current user or session context.

-- Enable RLS on a table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owners too
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- Users can only see their own documents
CREATE POLICY user_select ON documents
  FOR SELECT
  USING (owner = current_user());

-- Users can only insert documents they own
CREATE POLICY user_insert ON documents
  FOR INSERT
  WITH CHECK (owner = current_user());

-- Admin role can see all documents
CREATE POLICY admin_full ON documents
  TO admin
  USING (true);

• PERMISSIVE (default): Multiple policies are combined with OR logic
• RESTRICTIVE: Multiple policies are combined with AND logic (for mandatory filters)

-- PERMISSIVE: User sees rows matching either condition
CREATE POLICY policy1 ON table1 AS PERMISSIVE FOR SELECT USING (col1 = 'a');
CREATE POLICY policy2 ON table1 AS PERMISSIVE FOR SELECT USING (col2 = 'b');
-- Result: (col1 = 'a') OR (col2 = 'b')

-- RESTRICTIVE: User must satisfy ALL conditions
CREATE POLICY policy3 ON table2 AS RESTRICTIVE FOR SELECT USING (status = 'active');
CREATE POLICY policy4 ON table2 AS RESTRICTIVE FOR SELECT USING (dept = 'sales');
-- Result: (status = 'active') AND (dept = 'sales')

For complete documentation, see docs/RLS.md.

All original PostgreSQL type information is stored in the __pg_meta__ table:

-- Query the shadow catalog
SELECT * FROM __pg_meta__ WHERE table_name = 'users';
-- Returns: column_name, original_type (VARCHAR(100)), constraints, etc.

This enables 100% reversible migrations back to PostgreSQL.

pgqt provides comprehensive PostgreSQL-compatible system catalog views for full ORM support:

-- List all tables (like \dt in psql)
SELECT * FROM pg_tables WHERE schemaname = 'public';

-- List all columns for a table
SELECT a.attname, t.typname, a.attnotnull
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'my_table';

-- List all indexes
SELECT * FROM pg_indexes WHERE tablename = 'my_table';

-- Check database version
SELECT setting FROM pg_settings WHERE name = 'server_version';

Supported Catalog Views:

For complete documentation, see docs/PG_CATALOG.md.

PGQT includes built-in Prometheus-compatible metrics for monitoring query performance and system health.

• Prometheus Metrics Endpoint (/metrics) - Exports standard Prometheus metrics
• Health Check (/health) - JSON health status endpoint
• Web Dashboard (/) - Built-in dashboard (with web-config feature)
• System Metrics - CPU, memory, and disk usage (with system-metrics feature)

# Minimal build (no observability)
cargo build --release --no-default-features --features plpgsql

# With Prometheus metrics
cargo build --release --features plpgsql,tls,metrics

# With system metrics (CPU, memory, disk)
cargo build --release --features plpgsql,tls,system-metrics

# Full observability stack (metrics + web dashboard)
cargo build --release --features plpgsql,tls,web-config

# Enable metrics on port 9090 (default)
./pgqt --metrics-enabled

# Use custom port
./pgqt --metrics-enabled --metrics-port 8080

# Or via environment variables
PGQT_METRICS_ENABLED=1 PGQT_METRICS_PORT=9090 ./pgqt

With system-metrics feature:

┌─────────────────┐     ┌──────────────────┐     ┌─────────────────┐
│  PostgreSQL     │────▶│   **`pgqt`**   │────▶│   SQLite        │
│  Client (psql)  │     │   (Rust/Tokio)   │     │   Database      │
└─────────────────┘     └──────────────────┘     └─────────────────┘
                               │
                               ▼
                        ┌──────────────────┐
                        │  Shadow Catalog  │
                        │  (__pg_meta__)   │
                        └──────────────────┘

1. Wire Protocol Handler (pgwire): Handles PostgreSQL v3 protocol
2. AST Transpiler (pg_query): Parses and rewrites SQL using PostgreSQL 17 parser
3. Type Registry: Maps PostgreSQL types to SQLite with metadata preservation
4. Query Executor: Executes against SQLite with result streaming

pgqt [OPTIONS]

Options:
  -c, --config <CONFIG>          Path to JSON configuration file
  -H, --host <HOST>              Host address to listen on [default: 127.0.0.1]
  -p, --port <PORT>              Port to listen on [default: 5432]
  -d, --database <DATABASE>      Path to the SQLite database file [default: test.db]
  -o, --output <OUTPUT>          Where to send server output [default: STDOUT]
  -e, --error-output <ERROR>     Where to send error output [default: <db>.error.log]
  -D, --debug                    Enable debug output
      --trust-mode               Disable password authentication (trust mode)
      --auto-create-users        Auto-create users that don't exist (insecure, for development only)
  -h, --help                     Print help
  -V, --version                  Print version

The --output and --error-output options control where server messages are written:

Examples:

# Default: output to stdout, errors to myapp.db.error.log
pgqt --database myapp.db

# Log everything to files
pgqt -d myapp.db -o /var/log/pglite/server.log -e /var/log/pglite/errors.log

# Suppress server output, send errors to stderr
pgqt -d myapp.db -o NULL -e STDERR

# Swap output streams
pgqt -d myapp.db -o STDERR -e STDOUT

PGQT supports running multiple independent listeners on different ports, each with its own database and configuration. This is useful for multi-tenant setups or running multiple isolated databases.

Create a JSON configuration file (e.g., pgqt.json):

{
  "ports": [
    {
      "port": 5432,
      "host": "127.0.0.1",
      "database": "/var/lib/pgqt/tenant1.db",
      "output": "stdout",
      "error_output": "/var/log/pgqt/tenant1.error.log",
      "debug": false,
      "trust_mode": false
    },
    {
      "port": 5433,
      "host": "127.0.0.1",
      "database": "/var/lib/pgqt/tenant2.db",
      "output": "/var/log/pgqt/tenant2.log",
      "error_output": "/var/log/pgqt/tenant2.error.log",
      "debug": false,
      "trust_mode": false
    },
    {
      "port": 5434,
      "host": "0.0.0.0",
      "database": "/var/lib/pgqt/shared.db",
      "output": "null",
      "error_output": null,
      "debug": true,
      "trust_mode": true
    },
    {
      "port": 5435,
      "host": "127.0.0.1",
      "database": "/var/lib/pgqt/dev.db",
      "output": "stdout",
      "error_output": "/var/log/pgqt/dev.error.log",
      "debug": true,
      "trust_mode": false,
      "auto_create_users": true
    }
  ]
}

Then start PGQT with the configuration file:

# Specify config file explicitly
pgqt --config pgqt.json

# Or place pgqt.json in the same directory as the executable
# It will be auto-detected
./pgqt

1. If --config is specified, that file is used
2. Otherwise, if pgqt.json exists in the executable directory, it is used
3. Otherwise, CLI arguments are used for single-port mode

PGQT provides extensive performance tuning options to optimize SQLite behavior for your workload:

# SQLite PRAGMA configuration for write-heavy workloads
pgqt --database myapp.db \
  --journal-mode WAL \
  --synchronous NORMAL \
  --cache-size -64000

# Connection pooling for high concurrency
pgqt --database myapp.db \
  --use-pooling \
  --pool-size 16 \
  --max-connections 200

# Enable caching for read-heavy workloads
pgqt --database myapp.db \
  --transpile-cache-size 512 \
  --enable-result-cache \
  --result-cache-size 256

# Memory management for large datasets
pgqt --database myapp.db \
  --enable-buffer-pool \
  --buffer-pool-size 100 \
  --memory-monitoring \
  --auto-cleanup

# Unix socket for local connections (more efficient than TCP)
pgqt --database myapp.db --socket-dir /var/run/pgqt --no-tcp

# TLS/SSL encryption
pgqt --database myapp.db --ssl --ssl-cert server.crt --ssl-key server.key

Key Performance Options:

For complete documentation, see docs/performance-tuning.md.

use pglite_proxy::SqliteHandler;

let handler = SqliteHandler::new("myapp.db")?;
// Use with your own pgwire server setup

-- Create table with PostgreSQL types
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100),
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    metadata JSONB
);

-- Insert data
INSERT INTO users (email, name, metadata)
VALUES ('alice@example.com', 'Alice', '{"role": "admin"}');

-- Query with PostgreSQL syntax
SELECT * FROM users
WHERE email ~~ '%@example.com'
  AND created_at > now() - interval '1 day';

-- Update
UPDATE users SET active = false WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = "postgresql://postgres@127.0.0.1:5432/myapp.db"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  active    Boolean  @default(true)
  createdAt DateTime @default(now()) @map("created_at")
  metadata  Json?
}

// data-source.ts
import { DataSource } from 'typeorm'

export const AppDataSource = new DataSource({
	type: 'postgres',
	host: '127.0.0.1',
	port: 5432,
	username: 'postgres',
	database: 'myapp.db',
	entities: ['src/entity/**/*.ts'],
	synchronize: true,
})

# Export from PostgreSQL
pg_dump -h prod.db.com -U postgres myapp > myapp.sql

# Start proxy with new SQLite file
PGQT_DB=myapp.db ./pgqt

# Import (transpiles automatically)
psql -h 127.0.0.1 -p 5432 -U postgres < myapp.sql

# Verify shadow catalog preserved types
psql -h 127.0.0.1 -p 5432 -U postgres -c "SELECT * FROM __pg_meta__"

# Default build with TLS support (~12MB)
cargo build --release

# Smaller build without TLS (~9.5MB)
cargo build --release --no-default-features --features plpgsql

We provide convenience scripts for common build configurations:

# Standard release with TLS support
./build-release.sh

# Smaller release without TLS
./build-release-small.sh

# Build both variants
./build-both.sh

See docs/build-options.md for detailed build configuration options.

# Run unit tests
cargo test

# Run integration tests with psql
./scripts/integration_test.sh

src/
├── main.rs                    # TCP server entry point
├── lib.rs                     # Library exports
├── config.rs                  # Multi-port configuration management
├── handler/                   # PostgreSQL wire protocol handler
│   └── mod.rs                 # SqliteHandler, session management, custom functions
├── transpiler/                # SQL transpilation (PostgreSQL → SQLite)
│   ├── mod.rs                 # Public API: transpile(), TranspileResult
│   ├── context.rs             # TranspileContext and result types
│   ├── ddl.rs                 # CREATE, ALTER, DROP, GRANT, COPY statements
│   ├── dml.rs                 # SELECT, INSERT, UPDATE, DELETE statements
│   ├── expr.rs                # Expression and node reconstruction
│   ├── utils.rs               # Shared helper functions
│   └── window.rs              # Window function and frame support
├── catalog/                   # Shadow catalog (__pg_meta__) management
│   ├── mod.rs                 # Public API and shared types
│   ├── init.rs                # Catalog initialization and pg_types
│   ├── table.rs               # Table/column metadata storage
│   ├── function.rs            # UDF metadata storage
│   ├── rls.rs                 # RLS policy storage
│   └── system_views.rs        # pg_catalog view initialization
├── array.rs                   # PostgreSQL array functions and operators
├── copy.rs                    # COPY FROM/TO command support
├── distinct_on.rs             # DISTINCT ON polyfill (ROW_NUMBER window)
├── fts.rs                     # Full-text search (FTS5-backed)
├── functions.rs               # User-defined function execution
├── geo.rs                     # 2D geometric type support
├── plpgsql/                   # PL/pgSQL parser and Lua transpiler
│   ├── mod.rs                 # Public API
│   ├── ast.rs                 # PL/pgSQL AST types
│   ├── parser.rs              # PL/pgSQL parser
│   ├── runtime.rs             # Lua execution runtime
│   ├── sqlstate.rs            # SQLSTATE error codes
│   └── transpiler.rs          # PL/pgSQL → Lua transpiler
├── range.rs                   # PostgreSQL range type support
├── rls.rs                     # Row-Level Security (RLS)
├── rls_inject.rs              # RLS AST injection utilities
├── schema.rs                  # Schema/namespace (ATTACH DATABASE)
└── vector.rs                  # pgvector-compatible vector search

• Single-writer model: SQLite's concurrency model (no MVCC like PostgreSQL)

• Extensions: PostGIS, pgvector, etc. (use SpatiaLite, sqlite-vec instead)
• Advanced indexing: GIN, GiST indexes (use FTS5, R-Tree instead)
• Replication: Logical/physical replication (single-file database)

pgqt provides PostgreSQL-compatible full-text search functionality using SQLite's FTS5 extension:

-- Create a table with tsvector column
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR
);

-- Search using @@ operator
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgresql & database');

-- Search with ranking
SELECT title, ts_rank(search_vector, to_tsquery('postgresql')) as rank
FROM articles
WHERE search_vector @@ to_tsquery('postgresql')
ORDER BY rank DESC;

-- Web-style search (Google-like syntax)
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('postgresql OR mysql -oracle');

-- Highlight matching terms
SELECT ts_headline('english', body, to_tsquery('postgresql')) as highlighted
FROM articles;

Supported FTS Functions:

• to_tsvector([config,] text) - Convert text to tsvector
• to_tsquery([config,] text) - Convert text to tsquery
• plainto_tsquery([config,] text) - Plain text to tsquery
• phraseto_tsquery([config,] text) - Phrase to tsquery
• websearch_to_tsquery([config,] text) - Web-style query
• ts_rank(tsvector, tsquery) - Rank results
• ts_headline([config,] text, tsquery [, options]) - Highlight matches
• setweight(tsvector, char) - Set weight on vector
• strip(tsvector) - Remove positions

Supported Operators:

• @@ - Match operator
• &, |, ! - Boolean operators in tsquery
• <-> - Phrase search
• || - Concatenate tsvectors

For complete documentation, see docs/FTS.md.

pgqt provides PostgreSQL-compatible array support with full operator and function coverage:

-- Create table with array columns
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],
    category_ids INTEGER[]
);

-- Insert with arrays
INSERT INTO products (name, tags, category_ids)
VALUES ('Widget', '{"featured","sale"}', '{1,5,10}');

-- Overlap: Find products with ANY of the given tags
SELECT * FROM products WHERE tags && '{"featured","new"}';

-- Contains: Find products with ALL of the given tags
SELECT * FROM products WHERE tags @> '{"featured","sale"}';

-- Contained by: Find products where tags are a subset
SELECT * FROM products WHERE tags <@ '{"featured","sale","new"}';

-- Array functions
SELECT array_append(tags, 'special') FROM products;
SELECT array_remove(category_ids, 5) FROM products;
SELECT cardinality(tags) FROM products;
SELECT array_to_string(tags, ', ') FROM products;

Supported Array Operators:

• && - Overlap (any element in common)
• @> - Contains (left contains all of right)
• <@ - Contained by (left is subset of right)

Supported Array Functions:

• array_append(arr, elem), array_prepend(elem, arr)
• array_cat(arr1, arr2), array_remove(arr, elem), array_replace(arr, old, new)
• array_length(arr, dim), array_lower(arr, dim), array_upper(arr, dim)
• array_ndims(arr), array_dims(arr), cardinality(arr)
• array_position(arr, elem [, start]), array_positions(arr, elem)
• array_to_string(arr, delim [, null_str]), string_to_array(text, delim [, null_str])
• array_fill(value, dims [, bounds]), trim_array(arr, n)

For complete documentation, see docs/ARRAYS.md.

pgqt provides PostgreSQL-compatible user-defined functions using CREATE FUNCTION:

-- Create a simple scalar function
CREATE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE sql
AS $$
    SELECT a + b
$$;

-- Call the function
SELECT add_numbers(5, 3);  -- Returns 8

-- Function with OUT parameters
CREATE FUNCTION get_user_info(user_id integer, OUT username text, OUT email text)
LANGUAGE sql
AS $$
    SELECT username, email FROM users WHERE id = user_id
$$;

-- Call it
SELECT * FROM get_user_info(1);

-- RETURNS TABLE function
CREATE FUNCTION get_active_users()
RETURNS TABLE(id integer, username text, email text)
LANGUAGE sql
AS $$
    SELECT id, username, email FROM users WHERE active = true
$$;

-- Call it
SELECT * FROM get_active_users();

-- STRICT function (returns NULL on NULL input)
CREATE FUNCTION square(x integer)
RETURNS integer
LANGUAGE sql
STRICT
AS $$
    SELECT x * x
$$;

-- This returns NULL (not an error)
SELECT square(NULL);

-- CREATE OR REPLACE
CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE sql
AS $$
    SELECT a + b + 1  -- Modified implementation
$$;

Supported Features:

• CREATE FUNCTION and CREATE OR REPLACE FUNCTION
• DROP FUNCTION
• Parameter modes: IN (default), OUT, INOUT
• Return types: scalar, SETOF, TABLE, VOID
• Function attributes: STRICT (RETURNS NULL ON NULL INPUT), IMMUTABLE, STABLE, VOLATILE
• SECURITY DEFINER / SECURITY INVOKER
• PARALLEL attributes

Function Attributes:

• STRICT: Returns NULL immediately if any input is NULL (no execution)
• IMMUTABLE: Always returns same result for same inputs (enables optimization)
• STABLE: Returns same result within a single transaction
• VOLATILE (default): Can return different results (e.g., random(), database writes)

For complete documentation, see docs/FUNCTIONS.md.

pgqt provides additional PostgreSQL-compatible built-in functions:

• power(a, b) - Raises a to the power of b (a^b)

  SELECT power(2, 3);  -- Returns 8.0
  SELECT power(4, 0.5);  -- Returns 2.0 (square root)

• split_part(string, delimiter, index) - Split string by delimiter and return nth part

  SELECT split_part('abc~def~ghi', '~', 2);  -- Returns 'def'
  SELECT split_part('abc~def~ghi', '~', -1);  -- Returns 'ghi' (last part)
  SELECT split_part('abc~def', '~', 5);  -- Returns '' (out of range)

• reverse(string) - Reverse a string

  SELECT reverse('abcde');  -- Returns 'edcba'
  SELECT reverse('hello world');  -- Returns 'dlrow olleh'

• left(string, n) / right(string, n) - Get leftmost/rightmost characters

  SELECT left('hello', 2);   -- Returns 'he' (first 2 chars)
  SELECT right('hello', 2);  -- Returns 'lo' (last 2 chars)
  SELECT left('hello', -2);  -- Returns 'hel' (all but last 2)
  SELECT right('hello', -2); -- Returns 'llo' (all but first 2)

• concat(...) - Concatenate all arguments, NULLs are ignored

  SELECT concat('a', 'b', 'c');      -- Returns 'abc'
  SELECT concat('hello', ' ', 'world');  -- Returns 'hello world'
  SELECT concat(1, 2, 3);            -- Returns '123' (numbers converted)

• date_trunc(field, timestamp) - Truncate timestamp to specified precision

  SELECT date_trunc('year', '2024-03-15 10:30:45');   -- Returns '2024-01-01 00:00:00'
  SELECT date_trunc('month', '2024-03-15 10:30:45');  -- Returns '2024-03-01 00:00:00'
  SELECT date_trunc('day', '2024-03-15 10:30:45');    -- Returns '2024-03-15 00:00:00'
  SELECT date_trunc('hour', '2024-03-15 10:30:45');   -- Returns '2024-03-15 10:00:00'

  Supported fields: millennium, century, decade, year, quarter, month, week, day, hour, minute, second

• date_part(field, timestamp) - Extract field from timestamp as double precision

  SELECT date_part('year', '2024-03-15 10:30:45');     -- Returns 2024.0
  SELECT date_part('month', '2024-03-15 10:30:45');    -- Returns 3.0
  SELECT date_part('day', '2024-03-15 10:30:45');      -- Returns 15.0
  SELECT date_part('hour', '2024-03-15 10:30:45');     -- Returns 10.0
  SELECT date_part('quarter', '2024-03-15 10:30:45');  -- Returns 1.0 (Q1)
  SELECT date_part('dow', '2024-03-15 10:30:45');      -- Returns 5.0 (Friday)
  SELECT date_part('doy', '2024-03-15 10:30:45');      -- Returns 75.0 (day of year)
  SELECT date_part('epoch', '2024-03-15 10:30:45');    -- Returns seconds since Unix epoch

  Supported fields: year, month, day, hour, minute, second, millisecond, microsecond, quarter, week, dow, isodow, doy, epoch, decade, century, millennium, julian

• date_bin(stride, source, origin) - Bin timestamp into intervals

  SELECT date_bin('15 minutes', '2024-03-15 10:23:45', '2000-01-01');  -- Returns '2024-03-15 10:15:00'
  SELECT date_bin('1 hour', '2024-03-15 10:23:45', '2000-01-01');      -- Returns '2024-03-15 10:00:00'
  SELECT date_bin('1 day', '2024-03-15 10:23:45', '2000-01-01');       -- Returns '2024-03-15 00:00:00'

  Supported stride units: microsecond(s), millisecond(s), second(s), minute(s), hour(s), day(s), week(s)

• to_date(text, format) - Convert string to date

  SELECT to_date('2024-03-15', 'YYYY-MM-DD');   -- Returns '2024-03-15'
  SELECT to_date('15/03/2024', 'DD/MM/YYYY');   -- Returns '2024-03-15'

  Supported format patterns: YYYY, YY, MM, DD, HH24, HH12, MI, SS, Mon, Month

• regexp_replace(string, pattern, replacement [, flags]) - Replace pattern matches

  SELECT regexp_replace('foobarbaz', 'b..', 'X');        -- Returns 'fooXbaz' (first match)
  SELECT regexp_replace('aaabbaa', 'b', 'X', 'g');       -- Returns 'aaaXXaa' (all matches)
  SELECT regexp_replace('FooBar', 'bar', 'baz', 'i');    -- Returns 'Foobaz' (case-insensitive)

• regexp_substr(string, pattern [, start [, flags]]) - Extract substring matching pattern

  SELECT regexp_substr('foobarbaz', 'b..');              -- Returns 'bar'
  SELECT regexp_substr('foobarbaz', 'b..', 4);           -- Returns 'bar' (start at position 4)

• regexp_instr(string, pattern [, start [, occurrence [, flags]]]) - Find position of pattern match

  SELECT regexp_instr('foobarbaz', 'bar');               -- Returns 4 (1-indexed)
  SELECT regexp_instr('ababab', 'ab', 1, 3);             -- Returns 5 (3rd occurrence)

• regexp_match(string, pattern [, flags]) - Return array of captured groups

  SELECT regexp_match('foobarbequebaz', '(bar)(beque)'); -- Returns '{bar,beque}'

• regexp_split_to_array(string, pattern [, flags]) - Split string by pattern

  SELECT regexp_split_to_array('hello world test', '\s+'); -- Returns '{hello,world,test}'

  Flags:

  • g - Global (replace all matches for regexp_replace)
  • i - Case-insensitive matching
  • m - Multiline mode
  • s - Dot matches newline
  • x - Extended syntax (ignore whitespace)

• array_agg(value) - Collect values into an array

  SELECT array_agg(x) FROM (VALUES (1), (2), (3)) AS t(x);  -- Returns '{1,2,3}'
  SELECT array_agg(name) FROM users GROUP BY department;     -- Returns arrays per group

pgqt provides PostgreSQL pgvector-compatible vector search for similarity searches on embeddings:

-- Create table with vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- Insert with embedding
INSERT INTO documents (content, embedding)
VALUES ('Hello world', '[0.1, 0.2, 0.3, ...]');

-- Find similar documents using cosine distance
SELECT id, content, cosine_distance(embedding, '[0.12, 0.22, ...]') AS distance
FROM documents
ORDER BY distance
LIMIT 5;

Supported Distance Functions:

• l2_distance(a, b) / vector_l2_distance(a, b) - L2 (Euclidean) distance
• cosine_distance(a, b) / vector_cosine_distance(a, b) - Cosine distance
• inner_product(a, b) / vector_inner_product(a, b) - Inner product
• l1_distance(a, b) / vector_l1_distance(a, b) - L1 (Manhattan) distance

Supported Operators:

• <-> - L2 distance
• <=> - Cosine distance
• <#> - Inner product
• <+> - L1 distance

Utility Functions:

• vector_dims(vector) - Get number of dimensions
• l2_norm(vector) - Calculate L2 norm
• l2_normalize(vector) - Normalize to unit vector
• subvector(vector, start, len) - Extract subvector
• vector_add(a, b) - Add vectors element-wise
• vector_sub(a, b) - Subtract vectors element-wise

For complete documentation, see docs/VECTOR.md.

pgqt provides full PostgreSQL-compatible window function support:

-- Ranking within groups
SELECT department, name, salary,
       rank() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Running total
SELECT order_date, amount,
       sum(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders;

-- Comparing with previous row
SELECT date, revenue,
       revenue - lag(revenue) OVER (ORDER BY date) as daily_change
FROM daily_stats;

-- Moving average
SELECT date, price,
       avg(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg
FROM stocks;

Supported Window Functions:

• row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), ntile(n)
• lag(value [, offset [, default]]), lead(value [, offset [, default]])
• first_value(value), last_value(value), nth_value(value, n)

Aggregate Functions as Window Functions:

• sum(), avg(), count(), min(), max(), and all other aggregate functions

Frame Specifications:

• ROWS, RANGE, GROUPS frame modes
• UNBOUNDED PRECEDING/FOLLOWING, CURRENT ROW, offset PRECEDING/FOLLOWING bounds
• Full BETWEEN ... AND ... syntax

For complete documentation, see docs/WINDOW.md.

pgqt provides PostgreSQL-compatible DISTINCT ON support using ROW_NUMBER() window function polyfill:

-- Get the latest order for each customer
SELECT DISTINCT ON (customer_id) customer_id, order_date, amount
FROM orders
ORDER BY customer_id, order_date DESC;

-- Get the highest paid employee in each department/role
SELECT DISTINCT ON (department, role) department, role, name, salary
FROM employees
ORDER BY department, role, salary DESC;

Supported DISTINCT ON Features:

• Single and multiple column expressions
• Expression-based DISTINCT ON (e.g., DISTINCT ON (DATE(created_at)))
• ORDER BY with different sort columns for tie-breaking
• LIMIT and OFFSET support
• WHERE clause filtering
• JOIN support

Transformation: SELECT DISTINCT ON (a) x, y FROM t ORDER BY a, b is transformed to:

SELECT x, y FROM (
    SELECT x, y, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a, b) as __rn
    FROM t
) AS __distinct_on_sub
WHERE __rn = 1
ORDER BY a, b

For complete documentation, see docs/DISTINCT_ON.md.

For complete documentation, see docs/VECTOR.md.

• Users & Permissions (RBAC) - Role-based access control with GRANT/REVOKE
• Schemas (Namespaces) - Full schema support using SQLite ATTACH DATABASE
• Window Functions - Full support for all PostgreSQL window functions with frame specifications
• DISTINCT ON polyfill using window functions
• PL/pgSQL procedural blocks via Lua runtime
• Row-Level Security (RLS) emulation
• Full-Text Search (FTS) - PostgreSQL-compatible FTS using FTS5
• Trigger Support - PostgreSQL-compatible BEFORE/AFTER triggers using PL/pgSQL
• Enum Type Support - CREATE TYPE ... AS ENUM support
• Session Configuration - SET and set_config support
• Improved LATERAL Joins - Table-valued functions in LATERAL joins
• COMMENT ON Storage - Metadata persistence for comments

• Vector Search - pgvector-compatible vector search for embeddings
• Geometric Types - 2D geometric data types (point, box, circle, line, lseg, path, polygon) with spatial operators and distance functions.
• Connection Pooling - SQLite connection pooling with automatic checkout/return, max connections, and transaction rollback on return

• Load balancing (for multi-database distributed setups)

MIT License - See LICENSE file for details.

Contributions welcome! Please read CONTRIBUTING.md for guidelines.