Creating database tables

A custom integration integration can use scaffolding to create database tables on behalf of the user. This topic explains how to programmatically create database tables and organize them into folders.

Prerequisites

  • You're familiar with database tables.
  • You know how to create a scaffold.

Step 1: Create a folder

To create a folder, use the createFolder method:

function run(config, project, context) {
  // Create a folder
  project.dataTableManager.createFolder("My Folder");
}

The method accepts a folder name as its only argument.

Alternatively, use the getFolders method to get the names of existing folders:

function run(config, project, context) {
  const folders = project.dataTableManager.getFolders();

  if (folders.length < 1) {
    context.logger.error("No folders found");
    return;
  }

  const [firstFolderName] = folders;
}

Step 2: Create a database table

To create a database table, use the createTable method:

function run(config, project, context) {
  // Create a folder
  project.dataTableManager.createFolder("My Folder");

  // Create a database table
  project.dataTableManager.createTable("My Folder", {
    name: "My Data Table",
    columns: [],
  });
}

This method accepts two arguments:

  • The name of a folder
  • A DataTable object

It's the DataTable object that configures the database table, including its name and columns. To learn more about the DataTable object, see DataTableManager.

Step 3: Add columns to the database table

A database table's columns (fields) define the shape of the data that can be inserted into the database table.

To create a column, add a Column object to the columns array:

project.dataTableManager.createTable("My Folder", {
  name: "My Data Table",
  columns: [
    {
      name: "title",
      nullable: false,
      type: "string",
    },
  ],
});

At a minimum, a Column object must have the following properties:

  • name - The name of the column
  • nullable - If true, the column can contain null values
  • type - The data type of the column

The following snippet demonstrates how to create a database table for blog posts:

project.dataTableManager.createTable("My Folder", {
  name: "Blog Posts",
  columns: [
    {
      name: "title",
      nullable: false,
      type: "string",
    },
    {
      name: "content",
      nullable: false,
      type: "string",
    },
    {
      name: "publishedAt",
      nullable: true,
      type: "datetime",
    },
  ],
});

To learn more about the Column object, see DataTableManager.