PostgreSQL Connector

postgres://[username[:password]@]host[:port]/database[?options]

# Standard connection
postgres://postgres:password@localhost:5432/mydb

# With SSL enabled
postgres://user:[email protected]:5432/production?sslmode=require

# Custom port
postgres://user:pass@localhost:5433/mydb

# No SSL (development only)
postgres://user:pass@localhost:5432/db?sslmode=disable

# SSL without certificate verification (recommended for most use cases)
postgres://user:pass@host:5432/db?sslmode=require

# SSL with certificate verification
postgres://user:pass@host:5432/db?sslmode=verify-full

[[sources]]
id = "postgres_db"
dsn = "postgres://user:pass@host:5432/db"
connection_timeout = 30  # Seconds to establish connection
query_timeout = 60       # Seconds for query execution

[[sources]]
id = "readonly_pg"
dsn = "postgres://user:pass@host:5432/db"
readonly = true  # Sets default_transaction_read_only=on

[[sources]]
id = "pg_app"
dsn = "postgres://user:pass@host:5432/db"
search_path = "myapp,public"  # First schema is default for discovery

SELECT schema_name 
FROM information_schema.schemata 
WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schema_name;

// Default schema (public or search_path[0])
await connector.getTables();

// Specific schema
await connector.getTables('myschema');

COMMENT ON COLUMN users.email IS 'User email address';

const schema = await connector.getTableSchema('users');
// Returns: [
//   {
//     column_name: 'email',
//     data_type: 'varchar',
//     is_nullable: 'NO',
//     column_default: null,
//     description: 'User email address'
//   }
// ]

const indexes = await connector.getTableIndexes('users', 'public');
// Returns: [
//   {
//     index_name: 'users_pkey',
//     column_names: ['id'],
//     is_unique: true,
//     is_primary: true
//   },
//   {
//     index_name: 'idx_users_email',
//     column_names: ['email'],
//     is_unique: true,
//     is_primary: false
//   }
// ]

// Get all routines
await connector.getStoredProcedures('public');

// Only functions
await connector.getStoredProcedures('public', 'function');

// Only procedures
await connector.getStoredProcedures('public', 'procedure');

const proc = await connector.getStoredProcedureDetail('my_function', 'public');
// Returns: {
//   procedure_name: 'my_function',
//   procedure_type: 'function',
//   language: 'plpgsql',
//   parameter_list: 'user_id IN integer',
//   return_type: 'void',
//   definition: 'CREATE OR REPLACE FUNCTION...'
// }

const rowCount = await connector.getTableRowCount('large_table', 'public');
// Fast estimate from catalog statistics

COMMENT ON TABLE users IS 'Application user accounts';

const comment = await connector.getTableComment('users', 'public');
// Returns: 'Application user accounts'

await connector.executeSQL(
  "SELECT * FROM users WHERE email = $1 AND active = $2",
  { maxRows: 100 },
  ['[email protected]', true]
);

await connector.executeSQL(`
  BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
`, { maxRows: undefined });

await connector.executeSQL(
  "SELECT * FROM large_table",
  { maxRows: 100 }
);
// Executes: SELECT * FROM large_table LIMIT 100

# Environment variable
export DSN="postgres://postgres:password@localhost:5432/mydb"

# Or in TOML
[[sources]]
id = "local"
dsn = "postgres://postgres:password@localhost:5432/mydb"

[[sources]]
id = "cloud_pg"
dsn = "postgres://username:[email protected]:5432/production?sslmode=require"
connection_timeout = 30
query_timeout = 60

[[sources]]
id = "remote_pg"
dsn = "postgres://dbuser:dbpass@internal-db:5432/mydb"
ssh_host = "bastion.example.com"
ssh_user = "admin"
ssh_key = "~/.ssh/id_rsa"

[[sources]]
id = "multi_schema"
dsn = "postgres://user:pass@localhost:5432/db"
search_path = "app_schema,audit_schema,public"

-- Uses search_path to find tables
SELECT * FROM users;  -- Finds app_schema.users

-- Explicit schema reference
SELECT * FROM audit_schema.logs;

[[sources]]
id = "analytics"
dsn = "postgres://readonly_user:[email protected]:5432/warehouse"
readonly = true  # Enforces read-only at session level
query_timeout = 300  # 5 minutes for long analytics queries

-- Query JSONB columns
SELECT data->>'name' as name, data->'tags' as tags
FROM events
WHERE data @> '{"type": "purchase"}'::jsonb;

-- Using tsvector for full-text search
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Calculate running totals
SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions
ORDER BY date;

WITH recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT c.name, ro.order_count
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id
WHERE ro.order_count > 5;

postgres://user:pass@host:5432/db?sslmode=require