Setting up a Schema in Express and Sequelize

When deploying your portfolio projects on Render.com, you can take advantage of the One Database with Multiple Schemas to Many Applications architecture pattern. This lets you host multiple applications on Render’s free tier, each in its own schema. By making a few small changes to your configuration, models, migrations, and seeders, you can seamlessly target a custom schema for your production environment.

In this tutorial, you'll learn how to:

These steps ensure that your tables live inside a schema of your choice instead of the default public schema. Let’s walk through each step with an existing Express and Sequelize application.

Step 1: Create a Setup Script That Creates a Schema

First, add a new environment variable called SCHEMA in your .env file. Make sure it’s in snake_case: 

SCHEMA=<schema_name>  # must be in snake case, e.g. api_project

Next, create a script called psql-setup-script.js inside your backend directory (or wherever you keep your server-side code). Add the following: 

// backend/psql-setup-script.js

const { sequelize } = require('./db/models');

sequelize.showAllSchemas({ logging: false }).then(async (data) => {
  if (!data.includes(process.env.SCHEMA)) {
    await sequelize.createSchema(process.env.SCHEMA);
  }
});

This script checks whether the SCHEMA value from your environment variable exists. If not, it creates the schema. It’s equivalent to the raw SQL command: 

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Finally, update your package.json (in the backend directory) to add a build script: 

// backend/package.json

"scripts": {
  // ...
  "build": "node psql-setup-script.js" // add this line
}

During deployment, running npm run build will execute the script and ensure your schema is created in the database.

Step 2: Define the Schema in the Config File

In your config/database.js (or config/config.json if that’s how your project is set up), edit the production configuration to include the schema: 

production: {
  // ...
  define: {
    schema: process.env.SCHEMA  // this points to your custom schema
  }
}

This tells Sequelize to prefix all table names with process.env.SCHEMA when in production.

Step 3: Define the Schema in Each Migration File

Now it’s time to ensure your migrations create or modify tables inside the specified schema. The approach differs slightly depending on whether you’re creating or altering a table.

Create Table Migrations

In each create table migration file, add an options object, and pass it to the createTable and dropTable functions: 

'use strict';

let options = {};
if (process.env.NODE_ENV === 'production') {
  options.schema = process.env.SCHEMA;
}

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('table-name', {
      // define columns here
    }, options); // pass options here
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('table-name', options); // also pass options
  }
};

Make sure you do this in every migration that creates a table.

Alter Table Migrations

In migrations that alter existing tables (e.g., add/remove columns), you need to specify both the table name and the schema inside the options. Then pass the options object to the method (like addColumn) as the first argument. 

// at the top of your file
let options = {};
options.tableName = 'TableName'; // the table you're altering

if (process.env.NODE_ENV === 'production') {
  options.schema = process.env.SCHEMA;
}

module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.addColumn(options, 'firstName', {
      type: Sequelize.STRING(30),
      allowNull: false,
    });
    await queryInterface.addColumn(options, 'lastName', {
      type: Sequelize.STRING(30),
      allowNull: false,
    });
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.removeColumn(options, 'firstName');
    await queryInterface.removeColumn(options, 'lastName');
  }
};

This way, Sequelize knows which schema to look in for the table you’re altering.

Step 4: Define the Schema in Each Seeder File

Seeder files also need a similar change, letting Sequelize know which schema (and table) to insert data into. At the top of each seeder, define the options object: 

'use strict';

let options = {};
if (process.env.NODE_ENV === 'production') {
  options.schema = process.env.SCHEMA;
}

module.exports = {
  up: (queryInterface, Sequelize) => {
    options.tableName = 'TableName'; // your table name here
    return queryInterface.bulkInsert(options, [
      // seeder data...
    ]);
  },

  down: (queryInterface, Sequelize) => {
    options.tableName = 'TableName'; 
    return queryInterface.bulkDelete(options);
  }
};

In the up function, we pass options (with tableName and schema) to bulkInsert. In the down function, we do the same for bulkDelete.

Note: If your seeder uses Model.bulkCreate instead of queryInterface calls, you don’t need these schema adjustments in the seeder, because the model already knows its schema.

What You've Learned

By making these changes, you instruct Sequelize to place (and look for) all your tables in a specific schema when running in the production environment. This four-step process—(1) creating a setup script, (2) editing config, (3) altering migrations, and (4) updating seeders—lets you:

  1. Automatically create your schema if it doesn’t exist.
  2. Set a global schema in config/database.js or config.json.
  3. Ensure your migration files target the correct schema when creating or altering tables.
  4. Ensure your seeder files point to the right schema when inserting or deleting data.

Once configured, your application behaves normally—you continue to write Sequelize code as usual, but under the hood, all SQL commands are prefixed with your custom schema in production. This completes the transition to the One Database with Multiple Schemas pattern, letting you host multiple applications in a single Postgres instance on Render.com.