Skip to main content

Using Domain Types in Sequelize

PostgreSQL domain types are user-defined data types that extend existing ones, allowing you to add constraints that restrict the values they can hold. Setting a field type as a domain type enables you to enforce data integrity and validation rules at the database level.

This guide explains how to define a model type as a domain type in your Sequelize model and configure the schema migration to manage both the domains and the Sequelize model as a single migration unit using Atlas.

Atlas Pro Feature

Atlas support for Domain Types is available exclusively to Pro users. To use this feature, run:

atlas login

Getting started with Atlas and Sequelize​

Before we continue to domain types, ensure you have installed the Atlas Sequelize Provider on your Sequelize project.

To set up, follow along the getting started guide for Sequelize and Atlas.

Composite Schema​

The Sequelize models are mostly used for defining tables and interacting with the database. Domain types, or any other database objects do not have representation in Sequelize models - a domain type can be defined once, and may be used multiple times in different columns and models.

In order to extend our PostgreSQL schema to include both custom domain types and our Sequelize types, we configure Atlas to read the state of the schema from a Composite Schema data source. Follow the steps below to configure this for your project:

1. Create a schema.sql that defines the necessary domain type. In the same way, you can configure the composite type in Atlas Schema HCL language:

schema.sql
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
    VALUE ~ '^\d{5}$'
    OR VALUE ~ '^\d{5}-\d{4}$'
);

2. In your Sequelize models, define a column that uses the domain type only in PostgreSQL dialect:

user.js
'use strict';
module.exports = function(sequelize, DataTypes) {
    const us_postal_code = {
        type: 'us_postal_code',
    };
    const User = sequelize.define('User', {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        postal_code: us_postal_code
    });
    return User;
};

3. In your atlas.hcl config file, add a composite_schema that includes both your custom types defined in schema.sql and your Sequelize models:

atlas.hcl
data "composite_schema" "app" {
  # Load custom types first.
  schema "public" {
    url = "file://schema.sql"
  }
  schema "public" {
    url = data.external_schema.sequelize.url
  }
}

env "local" {
  src = data.composite_schema.app.url
  dev = "docker://postgres/15/dev?search_path=public"
}

Usage​

After setting up our schema, we can get its representation using the atlas schema inspect command, generate migrations for it, apply them to a database, and more. Below are a few commands to get you started with Atlas:

Inspect the Schema​

The atlas schema inspect command is commonly used to inspect databases. However, we can also use it to inspect our composite_schema and print the SQL representation of it:

atlas schema inspect \
  --env local \
  --url env://src \
  --format '{{ sql . }}'

The command above prints the following SQL. Note, the us_postal_code domain type is defined in the schema before its usage in the postal_code field:

-- Create domain type "us_postal_code"
CREATE DOMAIN "us_postal_code" AS text CONSTRAINT "us_postal_code_check" CHECK ((VALUE ~ '^\d{5}$'::text) OR (VALUE ~ '^\d{5}-\d{4}$'::text));
-- Create "Users" table
CREATE TABLE "Users" ("id" serial NOT NULL, "name" character varying(255) NOT NULL, "postal_code" "us_postal_code" NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));

Generate Migrations For the Schema​

To generate a migration for the schema, run the following command:

atlas migrate diff \
  --env local

Note that a new migration file is created with the following content:

-- Create domain type "us_postal_code"
CREATE DOMAIN "us_postal_code" AS text CONSTRAINT "us_postal_code_check" CHECK ((VALUE ~ '^\d{5}$'::text) OR (VALUE ~ '^\d{5}-\d{4}$'::text));
-- Create "Users" table
CREATE TABLE "Users" ("id" serial NOT NULL, "name" character varying(255) NOT NULL, "postal_code" "us_postal_code" NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));

Apply the Migrations​

To apply the migration generated above to a database, run the following command:

atlas migrate apply \
  --env local \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable"
Apply the Schema Directly on the Database

Sometimes, there is a need to apply the schema directly to the database without generating a migration file. For example, when experimenting with schema changes, spinning up a database for testing, etc. In such cases, you can use the command below to apply the schema directly to the database:

atlas schema apply \
  --env local \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable"