SQL datasource

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.

What it costs at boot

Introspection reads the whole schema on every boot:

Every table is read, whatever include and exclude say. Those only hide collections after the scan.
Each table costs two catalog queries, queued behind the connection pool (five connections unless you set pool).
Model building then runs on the event loop of the process hosting the agent.

On a large schema, every boot and redeploy puts a burst of load on your production database. Caching the introspection removes it. Until you do, bring new back-ends up outside busy hours.

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

Generate the introspection with introspect(), commit the file as a cache, and pass it to createSqlDataSource to skip the scan at boot. Your runtime credentials then no longer need information_schema access. It’s worth doing on a wide schema or with frequent deploys. On a small schema, the default is fine.

Generate the file

Run this on every migration, in the same CI step. It needs no time window:

// scripts/introspect.mjs
import { introspect } from '@forestadmin/datasource-sql';
import { writeFileSync } from 'fs';

// Same connection options as your createSqlDataSource call, schema included.
const introspection = await introspect(process.env.DATABASE_URL);

writeFileSync('./introspection.json', JSON.stringify(introspection, null, 2));

Boot from it

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

const introspection = JSON.parse(readFileSync('./introspection.json', 'utf8'));

agent.addDataSource(
  createSqlDataSource(process.env.DATABASE_URL, { introspection })
);

Nothing checks the file against your database. After a migration, a new column stays hidden and a dropped one is still exposed until you regenerate. A schema drift check won’t catch it either, since it reads the same file.

introspection goes in the second argument. The schema key in the first selects the Postgres schema (public by default).

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.

Overview

Connect

Process

Build

Execute

Collaborate

Manage

Embed

Control

Project Settings

Basic usage

Automatic schema introspection

What it costs at boot

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

​What it costs at boot

​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

What it costs at boot

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