SQL (without ORM)

The SQL data source allows importing tables from SQL databases.

Each table in the database will be mapped to a collection in Forest Admin.

Installation

To make everything work as expected, you need to:

• install the package @forestadmin/datasource-sql.

• install the native drivers for the vendors you are aiming to connect to.

Vendor	Install extra package
MariaDB	mariadb
Microsoft SQL Server	tedious
MySQL	mysql2
Oracle	oracledb
PostgreSQL	pg + pg-hstore
SQLite	sqlite3

Usage

Get started by using the following code sample in your agent.

You can then tune the configuration to your needs.

const { createAgent } = require('@forestadmin/agent');
const { createSqlDataSource } = require('@forestadmin/datasource-sql');

// Create agent and import collections from SQL database
const agent = createAgent(options).addDataSource(
  createSqlDataSource({
    uri: 'postgres://user:pass@localhost:5432/myDatabase',
    sslMode: 'preferred',
  }),
);

Automatic schema discovery

By default, when using this data source, there is no need to provide a schema, as the agent will extract the list of tables, columns, and relations when the agent restarts.

However, to be able to work, the credentials which are used must be able to access the information_schema or the database. Using a role that has ownership of the tables you would like to use in ForestAdmin is highly recommended.

The introspection only supports tables from Microsoft SQL Server, PostgreSQL, MySQL, and MariaDB (views and materialized views will be ignored).

If you wish to use a different database, or have your SQL views and materialized views available in Forest Admin, you will need to provide the schema yourself.

Example: Caching the introspection

If your database schema does not change or if you want to skip the introspection step when running in production, you may want to cache it.

To do so, you can call the introspection function yourself and pass the result to the data source constructor.

Note that you will need to delete the cache file if you change the database structure.

const { createAgent } = require('@forestadmin/agent');
const { createSqlDataSource, introspect } = require('@forestadmin/datasource-sql');

// Options to connect to the db (see above).
const connectionOptions = { uri: 'postgres://user:pass@localhost:5432/myDatabase' };

let introspection;
try {
  // The introspection is JSON serializable. You can store it in a file.
  // Read it from the file if it exists.
  introspection = JSON.parse(fs.readFileSync('./my-database-introspection.json'));
} catch (e) {
  if (e.code === 'ENOENT') {
    // The file does not exist, we need to introspect the database.
    introspection = await introspect(connectionOptions);
    fs.writeFileSync('./my-database-introspection.json', JSON.stringify(introspection));
  } else {
    throw e;
  }
}

const agent = createAgent(options).addDataSource(
  createSqlDataSource(connectionOptions, { introspection }),
);

Example: Using a different account for the schema introspection and the agent

You may be running forest admin with database credentials that have restricted access to the database. In this case, you will need to provide a different set of credentials to the data source to be able to introspect the database.

const { createAgent } = require('@forestadmin/agent');
const { createSqlDataSource, introspect } = require('@forestadmin/datasource-sql');

// Options to connect to the db (see above).
const startupConnectionOptions = {
  uri: 'postgres://user-with-privileges@localhost:5432/myDatabase',
};
const runtimeConnectionOptions = { uri: 'postgres://normal-user@localhost:5432/myDatabase' };
const introspection = await introspect(startupConnectionOptions);

const agent = createAgent(options).addDataSource(
  createSqlDataSource(runtimeConnectionOptions, { introspection }),
);

Advanced configuration

Configuration can be as simple as passing a URI to the data source constructor, but more options are available:

const { createAgent } = require('@forestadmin/agent');
const { createSqlDataSource } = require('@forestadmin/datasource-sql');

// Create agent and import collections from SQL database
const agent = createAgent(options).addDataSource(createSqlDataSource({
  /** URI where the database can be reached */
  uri: 'postgres://user:pass@localhost:5432/myDatabase',

  /**
   * SSL mode to use when connecting to the database
   * Possible values: 'preferred', 'verify', 'required', 'disabled', 'manual'
   *
   * - 'preferred' will use SSL if the server supports it, but will fall back to an unencrypted connection otherwise.
   * - 'verify' will use SSL and verify the server certificate.
   * - 'required' will use SSL but won't verify the certificate.
   * - 'disabled' will use an unencrypted connection.
   * - 'manual' will rely on settings from dialectOptions (see below).
   */
  sslMode: 'preferred',

  /** The dialect of the database you are connecting to */
  dialect: 'postgres',

  /** The name of the database */
  database: 'myDatabase',

  /** The username which is used to authenticate against the database */
  username: 'user',

  /** The password which is used to authenticate against the database */
  password: 'pass',

  /** The host of the relational database */
  host: 'localhost',

  /** The port of the relational database */
  port: 5432

  /** If defined the connection will use the provided schema instead of the default ("public") */
  schema: 'public'

  /** Only for PostgreSQL: A flag that defines if `pg-native` shall be used */
  native: false,

  /** Only for SQLite: path where the database file is located */
  // storage: '/tmp/my-sqlite-database.db'

  /** Connection pool options */
  pool: {
    /** Maximum number of connection in pool */
    max: 5,

    /** Minimum number of connection in pool */
    min: 0,

    /** Maximum time, in milliseconds, that pool will try to get connection before throwing error */
    acquire: 30000,

    /** Maximum time, in milliseconds, that a connection can be idle before being released */
    idle: 10000,
  },

  /**
   * Use read / write replication.
   * @see https://sequelize.org/docs/v6/other-topics/read-replication/
   */
  replication: false,
}));

Note that under the hood, the data source uses Sequelize ↗ to connect to the database. So, you can pass any option that is supported by Sequelize.