📚 StoneJS Developer Guide

Complete reference for building modern web applications with the StoneJS Framework

🗄️ Database

Database Support

StoneJS includes built-in support for PostgreSQL and MySQL with automatic connection pooling and query management.

Database Methods

Method Description Returns
query(sql, params) Execute single query with parameterized values Array of row objects
query([queries]) Execute multiple queries on same connection Array of result arrays
secureQuery(sql, params, var, val) Execute with single RLS session variable Array of row objects
secureQuery(sql, params, {vars}) Execute with multiple RLS session variables Array of row objects
getConnection() Get raw connection from pool (advanced) Connection object

Configuration

Configure database connection in your .env file:

Environment Variable Description Example
DB_TYPE Database type (required) postgresql or mysql
DB_HOST Database server hostname localhost
DB_PORT Database server port 5432 (PostgreSQL), 3306 (MySQL)
DB_USER Database username myuser
DB_PASSWORD Database password secret123
DB_NAME Database name myapp
DB_SSL Enable SSL connection (optional) true or false

Query Types

StoneJS supports all standard SQL operations with parameterized queries to prevent SQL injection:

Operation Example Returns
SELECT await $db.query('SELECT * FROM users WHERE id = $1', [userId]) Array of row objects
INSERT await $db.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id', ['John', '[email protected]']) Array with inserted row(s)
UPDATE await $db.query('UPDATE users SET name = $1 WHERE id = $2', ['Jane', userId]) Array with updated row(s)
DELETE await $db.query('DELETE FROM users WHERE id = $1', [userId]) Array with deleted row(s)

⚠️ SQL Injection Prevention: Always use parameterized queries with $1, $2, etc. placeholders and pass values in the params array. Never concatenate user input directly into SQL strings.

Example: Complete CRUD Operations

<%%js> // CREATE - Insert with RETURNING const result = await $db.query( 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id', ['John Doe', '[email protected]'] ); const newId = result[0].id; // READ - Select with filter const users = await $db.query( 'SELECT * FROM users WHERE active = $1', [true] ); // UPDATE - Modify record await $db.query( 'UPDATE users SET name = $1, updated_at = NOW() WHERE id = $2', ['Jane Doe', userId] ); // DELETE - Remove record await $db.query('DELETE FROM users WHERE id = $1', [userId]);

Multiple Queries (New!)

Execute multiple independent queries in a single call. All queries run on the same connection, improving performance.

💡 Benefits: Multiple queries use the same connection from the pool, reducing connection overhead. Perfect for dashboard pages that need data from multiple tables.

Syntax

<%%js> // Pass array of queries (with or without params) 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 products' // Simple query without params ]); // Access results by index const activeUsers = results[0]; // Array of user objects const pendingOrders = results[1]; // Array of order objects const productCount = results[2][0].count; // Single count value

Transactions

Wrap related operations in a transaction to ensure atomicity. All operations succeed together or fail together.

✅ Automatic Rollback: If any query in the transaction array throws an error, all changes are automatically rolled back. You don't need to explicitly call ROLLBACK.

Transaction Syntax

<%%js> try { const results = await $db.query([ 'BEGIN', { sql: 'UPDATE accounts SET balance = balance - $1 WHERE id = $2', params: [100, fromAccountId] }, { sql: 'UPDATE accounts SET balance = balance + $1 WHERE id = $2', params: [100, toAccountId] }, { sql: 'INSERT INTO transactions (from_id, to_id, amount) VALUES ($1, $2, $3)', params: [fromAccountId, toAccountId, 100] }, 'COMMIT' ]); // Transaction successful - all changes committed $context.set('success', 'Transfer completed'); } catch (err) { // Automatically rolled back on error console.error('Transaction failed:', err); $context.set('error', 'Transfer failed'); }

⚠️ Important: Use transactions for operations that must succeed or fail as a unit (e.g., financial transfers, inventory updates, multi-table inserts).

Row-Level Security (RLS)

For PostgreSQL tables with Row-Level Security policies, use secureQuery() to automatically set session variables before executing queries.

💡 What is RLS? PostgreSQL Row-Level Security allows you to restrict which rows users can access based on their session. StoneJS makes this easy by automatically setting session variables for you.

secureQuery() Variants

Method Signature Use Case
secureQuery(sql, params, varName, varValue) Set single session variable (e.g., account code)
secureQuery(sql, params, varsObject) Set multiple session variables (e.g., user ID, role, tenant)

Example: Single Session Variable

<%%js> // Set session.accountcode, then query const orders = await $db.secureQuery( 'SELECT * FROM orders WHERE status = $1', ['pending'], 'session.accountcode', $session.accountcode ); // RLS policy filters results by accountcode

Example: Multiple Session Variables

<%%js> // Set multiple session variables for complex RLS const data = await $db.secureQuery( 'SELECT * FROM sensitive_data', [], { 'session.accountcode': $session.accountcode, 'session.userid': $session.userId, 'session.role': $session.userRole } ); // RLS policy can use all three session variables

✅ Automatic Cleanup: Session variables are automatically reset after the query completes, ensuring isolation between requests.

Connection Pooling

Connection pooling is handled automatically by StoneJS. Connections are reused efficiently without manual management.

Setting Default Value Description
Min connections 2 Minimum connections kept alive in the pool
Max connections 10 Maximum concurrent connections allowed
Idle timeout 30 seconds Time before idle connections are closed

💡 Advanced Usage: For manual connection management (advanced use cases), use await $db.getConnection() to get a pooled connection directly.

Error Handling

Always wrap database operations in try/catch blocks to handle errors gracefully:

<%%js> try { const users = await $db.query('SELECT * FROM users WHERE active = $1', [true]); $context.set('users', users); } catch (err) { console.error('Database error:', err); $context.set('error', 'Failed to load users'); // User sees friendly error message instead of crash }

⚠️ Production Tip: Log detailed errors to console/logs but show user-friendly messages to visitors. Never expose SQL queries or database details to users.

Best Practices

  • Always use parameterized queries - Prevents SQL injection
  • Use secureQuery() for RLS tables - Automatic session variable management
  • Use transactions for related operations - Ensures data consistency
  • Handle errors gracefully - Always wrap in try/catch
  • Use array syntax for multiple queries - Runs on same connection
  • Cache expensive queries - Combine with $cache.remember()

Caching → Live Database Demo

Need more help?

View All Demos Getting Started GitHub Repository