PostgreSQL Protocol Examples
This document provides examples for using the ChronDB PostgreSQL protocol interface with psql and JavaScript clients.
PostgreSQL Protocol Overview
ChronDB implements a subset of the PostgreSQL protocol, allowing you to connect using standard PostgreSQL clients and drivers. This makes it easy to integrate with existing applications that already use PostgreSQL or to leverage the SQL query capability.
The PostgreSQL protocol server can be configured in the config.edn file:
:servers {
:postgresql {
:enabled true
:host "0.0.0.0"
:port 5432
:username "chrondb"
:password "chrondb"
}
}Data Model Mapping
ChronDB maps its document structure to SQL tables based on document keys:
Document keys with the format
collection:idare mapped to tablesThe part before the colon becomes the table name
The part after the colon becomes the row's ID
Document fields become columns in the table
For example:
A document with key
user:1becomes a row in theusertable withid='1'A document with key
product:xyzbecomes a row in theproducttable withid='xyz'
Supported SQL Features
ChronDB supports the following SQL operations:
DML (Data Manipulation Language)
SELECT- Query documentsINSERT- Create documentsUPDATE- Update documentsDELETE- Delete documents
DDL (Data Definition Language)
Note: ChronDB is schemaless by default. CREATE TABLE is optional - you can insert data directly without defining a schema first.
CREATE TABLE- Create table with explicit schema (optional)CREATE TABLE IF NOT EXISTS- Create table only if it doesn't existDROP TABLE- Delete table schemaDROP TABLE IF EXISTS- Delete table only if it existsSHOW TABLES- List all tables (both schemaless and schema-defined)SHOW SCHEMAS- List all schemas (Git branches)DESCRIBE table/SHOW COLUMNS FROM table- Show table structure (infers from data if no schema)
Validation Schema DDL
ChronDB supports optional JSON Schema validation per namespace:
CREATE VALIDATION SCHEMA FOR namespace AS 'json-schema' MODE strict|warning- Create validation schemaDROP VALIDATION SCHEMA FOR namespace- Delete validation schemaSHOW VALIDATION SCHEMA FOR namespace- Get validation schemaSHOW VALIDATION SCHEMAS- List all validation schemas
Special Functions
ChronDB provides special SQL functions to access version control features:
chrondb_history(table_name, id)- Get document historychrondb_at(table_name, id, timestamp)- Get document at a point in timechrondb_diff(table_name, id, t1, t2)- Compare document versionschrondb_branch_list()- List brancheschrondb_branch_create(name)- Create a new branchchrondb_branch_checkout(name)- Switch to a branchchrondb_branch_merge(source, target)- Merge branches
Examples with psql
Connecting to ChronDB
Document Operations
Creating Documents (INSERT)
Querying Documents (SELECT)
Updating Documents (UPDATE)
Deleting Documents (DELETE)
Version Control Operations
DDL Operations
Schema Validation Operations
ChronDB supports optional JSON Schema validation per namespace. See the Schema Validation documentation for full details.
Validation Errors
When inserting invalid data with a strict schema:
Branch Operations
Advanced Queries
Full-Text Search with to_tsquery
ChronDB supports PostgreSQL-style full-text search using the familiar @@ operator and to_tsquery function syntax. This allows you to perform efficient text searches across your documents with a PostgreSQL-compatible syntax.
FTS Syntax
How It Works
When you use the field @@ to_tsquery('term') syntax:
ChronDB parses the query and recognizes it as a full-text search condition
The search term is normalized (lowercase, accent removal)
If the term is short (less than 4 characters), wildcards are automatically added
The query is then passed to the underlying index (Lucene) for efficient search
Results are filtered to only include matches from the specified collection/table
FTS Field Optimization
For better full-text search performance, you can create specialized FTS fields with the _fts suffix:
When an indexed field ends with _fts, ChronDB will use it specifically for full-text search operations.
Comparison with FTS_MATCH
ChronDB also supports the FTS_MATCH function for backward compatibility:
The to_tsquery approach is recommended as it:
Follows standard PostgreSQL syntax
Provides better compatibility with existing PostgreSQL tools
Supports the same normalization and text processing features
Index Implementation Details
Full-text search operations are powered by:
Lucene index for efficient text search (when enabled in configuration)
Automatic text normalization and accent handling
Wildcard prefix matching for better search results
Fallback to basic string matching with MemoryIndex implementation
For optimal performance:
Enable the Lucene index in your configuration
Use dedicated FTS fields with the
_ftssuffix for frequently searched contentUtilize more specific search terms to reduce result sets
Examples with JavaScript (node-postgres)
The following examples use the node-postgres package, a popular PostgreSQL client for Node.js.
Setting Up
Document Operations
Version Control Operations
Branch Operations
Complete Example: Customer Order System
Schema Validation with JavaScript
ChronDB supports optional JSON Schema validation per namespace. See the Schema Validation documentation for full details.
Last updated
Was this helpful?