Database Integration Demo

This page demonstrates how to connect to PostgreSQL and query data using the $db object.

✓ Database Connected!

Database:	ddata
Host:	pgsql-prod01.postgres.database.azure.com:5432
Query Time:	99ms
Total Records:	400713

Query Results

Showing 1 - 10 of 400713 records

RID	Operation	Table Name	User ID	Description	Created
400718	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:33:33 am
400717	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:32:42 am
400716	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:12:05 am
400715	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:08:22 am
400714	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:08:03 am
400713	INSERT	chat_askai_queries	pgsql_access_rw	N/A	19/12/2025, 7:04:08 am
400712	INSERT	chat_askai_queries	pgsql_access_rw	N/A	15/12/2025, 12:16:57 pm
400711	INSERT	chat_askai_queries	pgsql_access_rw	N/A	15/12/2025, 11:09:25 am
400710	INSERT	chat_askai_queries	pgsql_access_rw	N/A	03/12/2025, 10:49:40 am
400709	INSERT	chat_askai_queries	pgsql_access_rw	N/A	03/12/2025, 10:48:23 am

Page 1 of 40072 Next →

Records per page:

Database API Reference

The $db object is available in all components and provides two main methods for database operations.

1. $db.query() - Standard Queries

Use for regular database operations without row-level security.

Single Query

// Query returns rows directly (no .rows wrapper)
const users = await $db.query('SELECT * FROM users LIMIT 10');

// With parameters (prevents SQL injection)
const user = await $db.query('SELECT * FROM users WHERE id = $1', [userId]);

Multiple Queries (Array Syntax)

Execute multiple queries on the same connection. Returns array of results.

const results = await $db.query([
  { sql: 'SELECT * FROM users WHERE active = $1', params: [true] },
  { sql: 'SELECT * FROM orders WHERE status = $1', params: ['pending'] },
  'SELECT COUNT(*) FROM audit_log'  // String format (no params)
]);

const activeUsers = results[0];
const pendingOrders = results[1];
const auditCount = results[2][0].count;

Transactions

Wrap operations in BEGIN/COMMIT. All succeed or all fail together.

try { const results = await $db.query([ 'BEGIN', { sql: 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id', params: ['John Doe', '[email protected]'] }, { sql: 'INSERT INTO audit_log (action, userid) VALUES ($1, $2)', params: ['user_created', 'admin'] }, 'COMMIT' ]); const newUserId = results[1][0].id; } catch (err) { // Transaction automatically rolled back on error console.error('Transaction failed:', err); }

2. $db.secureQuery() - Row-Level Security

Use for tables with PostgreSQL RLS enabled. Automatically manages session variables in a transaction.

Single Session Variable

const logs = await $db.secureQuery(
  'SELECT * FROM audit_log LIMIT 10',
  [],
  'session.accountcode',
  $session.accountcode
);

Multiple Session Variables

const logs = await $db.secureQuery(
  'SELECT * FROM audit_log WHERE userid = $1',
  ['user123'],
  {
    'session.accountcode': $session.accountcode,
    'session.userid': $session.userId,
    'session.role': $session.userRole
  }
);

Multiple Queries with RLS

const results = await $db.secureQuery(
  [
    { sql: 'SELECT * FROM orders WHERE status = $1', params: ['pending'] },
    { sql: 'SELECT * FROM invoices WHERE paid = $1', params: [false] },
    'SELECT * FROM customers'
  ],
  null,  // params not used when first arg is array
  { 'session.accountcode': $session.accountcode }
);

const pendingOrders = results[0];
const unpaidInvoices = results[1];
const customers = results[2];

How secureQuery() Works

Automatic Session Variable Management:

Gets a dedicated connection from the pool
Starts a transaction (BEGIN)
Sets session variable(s) with SET LOCAL
Executes your query/queries
Commits the transaction (automatically clears SET LOCAL variables)
Releases the connection back to the pool

Why this matters: Session variables are scoped to the transaction, preventing variable leakage between requests.

Setting up Row-Level Security in PostgreSQL

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

-- Create a policy using the session variable
CREATE POLICY account_isolation ON audit_log
  FOR ALL
  USING (
    accountcode = current_setting('session.accountcode', true)
  );

-- Grant access to your application role
GRANT SELECT, INSERT, UPDATE, DELETE ON audit_log TO app_role;

⚠️ Important: Regular $db.query() doesn't set session variables. Always use $db.secureQuery() for RLS-enabled tables, or RLS policies will block all rows.

Configuration

Database configuration is loaded from your .env file:

# PostgreSQL Configuration
DB_TYPE=postgresql
DB_HOST=localhost
DB_PORT=5432
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
DB_SSL=true  # Optional: Enable SSL connections

# Connection Pool Settings (optional)
# DB_POOL_MIN=2
# DB_POOL_MAX=10
# DB_POOL_IDLE_TIMEOUT=30000

Return Value Format

Important: StoneJS $db.query() returns rows directly, not wrapped in a .rows property like the native pg library.

Library	Return Format
`pg` (native)	`result.rows`
`$db.query()`	`result` (array directly)

Best Practices

Always use parameterized queries - Use $1, $2 placeholders to prevent SQL injection
Use secureQuery() for RLS tables - Automatically manages session variables
Use array syntax for related queries - Ensures they run on the same connection
Use transactions for atomic operations - Wrap in BEGIN/COMMIT
Handle errors gracefully - Use try/catch blocks
Combine with caching - Use $cache.remember() for expensive queries

Example: Caching Database Results

// Cache results for 5 minutes (300 seconds)
const logs = await $cache.remember('audit-logs-recent', 300, async () => {
  return await $db.query(
    'SELECT * FROM audit_log ORDER BY cdate DESC LIMIT 100'
  );
});

Quick Reference Table

Feature	$db.query()	$db.secureQuery()
Single query	✓ Yes	✓ Yes
Multiple queries	✓ Array syntax	✓ Array syntax
Transactions	✓ Manual BEGIN/COMMIT	✓ Automatic wrapper
Session variables	✗ No	✓ Automatic SET LOCAL
Use case	Standard queries	RLS-enabled tables
Connection handling	Automatic pool management	Dedicated connection per call

Session Stats: You've viewed this page 1 time(s).