SQL datasource - Forest Documentation

This datasource is only available for Node.js agents.

The SQL datasource connects directly to SQL databases with automatic schema introspection. Each database table or view maps to a Forest collection - tables offer full read-write capabilities while views are read-only.

Basic usage

import { createAgent } from '@forestadmin/agent';
import { createSqlDataSource } from '@forestadmin/datasource-sql';

const agent = createAgent(options);

agent.addDataSource(
  createSqlDataSource({
    uri: 'postgresql://user:pass@localhost:5432/mydb',
    sslMode: 'preferred'
  })
);

Automatic schema introspection

By default, the SQL datasource automatically discovers your database structure when the back-end starts. It extracts:

Tables and views - Each becomes a collection
Columns - With automatic type detection
Primary keys - For record identification
Foreign keys - Converted to relationships
Indexes - Used for query optimization

This requires database credentials with access to information_schema. Ownership roles are recommended.

Introspection is supported for PostgreSQL, MySQL, MariaDB, and Microsoft SQL Server.

Configuration options

Connection URI

The connection URI format varies by database:

createSqlDataSource({
  uri: 'postgresql://user:password@host:5432/database'
})

createSqlDataSource({
  uri: 'mysql://user:password@host:3306/database'
})

createSqlDataSource({
  uri: 'mssql://user:password@host:1433/database'
})

SSL configuration

Control SSL/TLS connection behavior:

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  sslMode: 'preferred' // or 'verify', 'required', 'disabled', 'manual'
})

SSL modes:

preferred - Use SSL if available, otherwise unencrypted (default)
required - Require SSL, fail if unavailable
verify - Require SSL with certificate verification
disabled - Never use SSL
manual - Custom SSL configuration (advanced)

Schema selection

Specify which database schema to use (PostgreSQL, SQL Server):

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  schema: 'my_schema' // default: 'public'
})

Connection pooling

Configure connection pool for optimal performance:

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  pool: {
    max: 20,        // Maximum connections
    min: 5,         // Minimum connections
    acquire: 30000, // Max time (ms) to get connection
    idle: 10000     // Close idle connections after 10s
  }
})

Read replicas

Distribute read operations across replica databases:

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  replication: {
    write: { host: 'primary.example.com' },
    read: [
      { host: 'replica1.example.com' },
      { host: 'replica2.example.com' }
    ]
  }
})

SSH tunnel

Connect through an SSH tunnel:

createSqlDataSource({
  uri: 'postgresql://user:pass@localhost:5432/db',
  ssh: {
    host: 'ssh-host.example.com',
    port: 22,
    username: 'ssh-user',
    privateKey: require('fs').readFileSync('/path/to/private-key')
  }
})

SOCKS5 proxy

Route connections through a SOCKS5 proxy:

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  proxySocks: {
    host: 'proxy.example.com',
    port: 1080,
    username: 'proxy-user',
    password: 'proxy-pass'
  }
})

Connection timeout

Set maximum time to establish connection:

createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  connectionTimeoutInMs: 5000 // 5 seconds
})

Soft-deleted records

Display records marked as deleted (soft deletes):

// Show soft-deleted for specific collections
createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  displaySoftDeleted: ['users', 'projects']
})

// Show soft-deleted for all collections
createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  displaySoftDeleted: true
})

This is useful when your application uses soft deletes (e.g., deleted_at column) and you want to manage deleted records in Forest.

Caching introspection

The schema introspection is JSON serializable. Cache it to a file:

import { createSqlDataSource } from '@forestadmin/datasource-sql';
import { writeFileSync, readFileSync } from 'fs';

// Option 1: Generate and cache schema
const dataSource = await createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db'
});

writeFileSync('./schema-cache.json', JSON.stringify(dataSource.schema));

// Option 2: Load from cache (faster startup, works offline)
const schema = JSON.parse(readFileSync('./schema-cache.json', 'utf8'));
const dataSource = createSqlDataSource({
  uri: 'postgresql://user:pass@host:5432/db',
  schema: schema
});

Benefits:

Faster back-end startup (no introspection delay)
Work offline or with restricted credentials
Separate introspection and runtime credentials
Version control your schema

Live Query support

Enable SQL-based reporting by setting a connection identifier:

createSqlDataSource({
  uri: process.env.DATABASE_URL,
  liveQueryConnections: 'main_database'
})

This allows authorized users to create Live Query charts and segments that execute custom SQL directly against your database.

Live Queries execute raw SQL. Ensure proper access controls and review queries before deploying to production.

Supported databases

Database	Versions	Driver Package	Status
PostgreSQL	10+	`pg` + `pg-hstore`	✅ Full support (recommended)
MySQL	5.7+	`mysql2`	✅ Production-ready
MariaDB	10+	`mariadb`	✅ Production-ready
SQL Server	2017+	`tedious`	✅ Enterprise support

Source code

This connector is open source. Browse the code or contribute on GitHub: @forestadmin/datasource-sql.

​Basic usage

​Automatic schema introspection

​Configuration options

​Connection URI

​SSL configuration

​Schema selection

​Connection pooling

​Read replicas

​SSH tunnel

​SOCKS5 proxy

​Connection timeout

​Soft-deleted records

​Caching introspection

​Live Query support

​Supported databases

​Source code

Basic usage

Automatic schema introspection

Configuration options

Connection URI

SSL configuration

Schema selection

Connection pooling

Read replicas

SSH tunnel

SOCKS5 proxy

Connection timeout

Soft-deleted records

Caching introspection

Live Query support

Supported databases

Source code