We shall be continuing from where we left off in the previous tutorial by building the database backend (using Postgresql) for the storefront backend. We shall start off by building migrations for the application. Migrations in Nest.js are code representations of changes that we wish to make to our database, which we can track via version control. But before we build the migraions, we shall start by doing a basic design of the database using SQLDBM. We will be using TypeORM, which is a well known Typescript ORM (Object Relational Mapper) that we will have to add to our project to assist in the creation of the migrations. We will also be adding the Nest.js configuration module to help us securely store our database credentials, as well as validate our environment variables.

Entity Relationship Diagram

SQLDBM is a cloud based database modelling tool that’s easy to use and free to use. Using some of the examples provided, I was able to design the Entity Relationship Diagram below:

SQLDBM ERD

We can now proceed to implement the above in our codebase.

Install Typeorm, plus additional dependencies

Navigate to the project directory.

cd ~/Projects/nest_projects/storefront-backend

Install typeorm, the nest.js typeorm integration (that allows you to use typeorm entities/repositories within nest.js modules), the node postgres library, reflect-metadata (which is a typeorm dependency), as well as the typescript definitions for node.js.

npm i @nestjs/typeorm typeorm@0.2 pg reflect-metadata @types/node --save

Install the nest.js config module

We will need to install the configuration package module for Nest.js, which will allow us to inject ConfigModule and ConfigService into our modules and services.

npm i @nestjs/config --save

Install the class-validator and class-transformer packages

These packages will be used to validate environment variables, as we shall see in a minute.

npm i class-transformer class-validator --save

Run the following commands to create the postgres user and database (as the postgres admin user)

sudo -u postgres psql

Inside the postgres console, run:

create user storefront with encrypted password 'your_database_password';
create database storefront with owner 'storefront';
grant all on database storefront to storefront;
alter user storefront with createdb;
\c storefront

Since we’ll be using UUIDs as our primary keys, we’ll need to load additional postgres extensions:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA public;

Exit the postgres console.

\q

Test that the database credentials work via the command line

psql -h localhost -U storefront 

Enter your database password:

Password for user storefront: 

Once you’ve successfully entered your password, you should see the following printed on your console:

psql (14.2 (Ubuntu 14.2-1.pgdg21.04+1), server 13.6 (Ubuntu 13.6-1.pgdg21.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

storefront=> 

Add TypeORM to the main application Module and configure environment variables

Modify your app module like so:

src/app.module.ts view raw
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { TypeOrmModule } from '@nestjs/typeorm';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import dbConfig from './common/config/db.config';
import { validate } from './common/config/env.validation';


@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
      load: [dbConfig],
      validate,
    }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: async (configService: ConfigService) => ({...configService.get('database')}),
      inject: [ConfigService],
    })
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Create an ormconfig.ts file in the main application root:

ormconfig.ts view raw
import { ConfigModule } from "@nestjs/config";
import dbConfig from "./src/common/config/db.config";

ConfigModule.forRoot({
  load: [dbConfig]
})

export default dbConfig()

Create a db.config file like so:

src/common/config/db.config.ts view raw
import { registerAs } from "@nestjs/config";

export default registerAs('database', () => {
  return {
    type: 'postgres',
    logging: process.env.TYPEORM_LOGGING,
    host: process.env.DB_MAIN_HOST,
    port: parseInt(process.env.DB_MAIN_PORT),
    username: process.env.DB_MAIN_USER,
    password: process.env.DB_MAIN_PASSWORD,
    database: process.env.DB_MAIN_DATABASE,
    autoLoadEntities: true,
    ssl: {
      rejectUnauthorized: false,
    },
    entities: [
      baseFolder() + 'modules/**/*.entity{.ts,.js}',
      baseFolder() + 'modules/**/*.view{.ts,.js}',
    ],
    migrations: [baseFolder() + 'migrations/**/*{.ts,.js}'],
      cli: {
        migrationsDir: baseFolder() + '/migrations',
      },
  }
})

function baseFolder(): string {
  const regex = /common+(\/|\\)+config/gi;
  return __dirname.replace(regex, '');
}

Create an env.validation class to validate environment variables:

src/common/config/env.validation.ts view raw
import { plainToClass } from 'class-transformer';
import { IsBoolean, IsEnum, IsNumber, IsString, validateSync } from 'class-validator';

enum Environment {
  Development = "development",
  Production = "production",
  Test = "test",
  Provision = "provision",
}

class EnvironmentVariables {
  @IsEnum(Environment)
  NODE_ENV: Environment;

  @IsNumber()
  PORT: number;

  @IsString()
  DB_MAIN_HOST: string;

  @IsNumber()
  DB_MAIN_PORT: number;

  @IsString()
  DB_MAIN_USER: string;

  @IsString()
  DB_MAIN_PASSWORD: string;

  @IsString()
  DB_MAIN_DATABASE: string;

  @IsBoolean()
  TYPEORM_LOGGING: boolean;
}

export function validate(config: Record<string, unknown>) {
  const validatedConfig = plainToClass(
    EnvironmentVariables,
    config,
    { enableImplicitConversion: true },
  );
  const errors = validateSync(validatedConfig, { skipMissingProperties: false });

  if (errors.length > 0) {
    throw new Error(errors.toString());
  }
  return validatedConfig;
}

Lastly, create migration tasks in your package.json:

package.json view raw
{
  "name": "storefront-backend",
  "version": "0.0.1",
  "description": "",
  "author": "",
  "private": true,
  "license": "UNLICENSED",
  "scripts": {
    "prebuild": "rimraf dist",
    "build": "nest build",
    "format": "prettier --write \"src/**/*.ts\" \"test/**/*.ts\"",
    "start": "nest start",
    "start:dev": "nest start --watch",
    "start:debug": "nest start --debug --watch",
    "start:prod": "node dist/main",
    "lint": "eslint \"{src,apps,libs,test}/**/*.ts\" --fix",
    "test": "jest",
    "test:watch": "jest --watch",
    "test:cov": "jest --coverage",
    "test:debug": "node --inspect-brk -r tsconfig-paths/register -r ts-node/register node_modules/.bin/jest --runInBand",
    "test:e2e": "jest --config ./test/jest-e2e.json",
    "typeorm": "node --require ts-node/register ./node_modules/typeorm/cli.js",
    "entity:create": "typeorm entity:create -n",
    "migrations:create": "npm run typeorm -- migration:create -n",
    "migrations:run": "npm run typeorm -- migration:run",
    "migrations:revert": "npm run typeorm -- migration:revert"
  },
  "dependencies": {
    "@nestjs/common": "^8.4.3",
    "@nestjs/config": "^2.0.0",
    "@nestjs/core": "^8.4.3",

To create a new migration, we’ll have to enter on our terminal:

npm run migrations:create MigrationName

To run our migrations (once we’ve created them), all we need to do is enter the following on our terminal:

npm run migrations:run

To reverse a migration (by default the previously run migration):

npm run migrations:revert

Optionally, you can use the configservice on the main application entry point (main.ts) to hide the default application port:

src/main.ts view raw
import { ConfigService } from '@nestjs/config';
import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';

async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  const configService = app.get(ConfigService);
  const port = configService.get('PORT');
  await app.listen(port);
}
bootstrap();

Create a .env file at the application root. Mine looks like so:

NODE_ENV=development
PORT=3000

# Database Configuration
DB_MAIN_HOST=localhost
DB_MAIN_PORT=5432
DB_MAIN_USER=storefront
DB_MAIN_PASSWORD=your_database_password
DB_MAIN_DATABASE=storefront
TYPEORM_LOGGING=true

You should now be able to start the application successfully.

Create the migrations

npm run migrations:create CreateProducts

Fill in the products migration:

src/migrations/1648820162516-CreateProducts.ts view raw
import {MigrationInterface, QueryRunner, Table} from "typeorm";

export class CreateProducts1648820162516 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(new Table({
            name: 'products',
            columns: [
                {
                    name: 'id',
                    type: 'uuid',
                    isPrimary: true,
                    isGenerated: true,
                    default: 'uuid_generate_v4()',
                  },
                  {
                    name: 'name',
                    type: 'varchar',
                  },
                  {
                    name: 'unit_price',
                    type: 'decimal(12,2)',
                  },
                  {
                    name: 'is_discontinued',
                    type: 'boolean',
                  },
                  {
                    name: 'description',
                    type: 'text',
                  },
                  {
                    name: 'image',
                    type: 'varchar',
                  },
                  {
                    name: 'created_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
                  {
                    name: 'updated_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
            ]
        }));
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropTable('products');
    }

}
npm run migrations:create CreateOrders

Fill in the orders migration:

src/migrations/1648820154148-CreateOrders.ts view raw
import {MigrationInterface, QueryRunner, Table, TableForeignKey} from "typeorm";

export class CreateOrders1648820154148 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(new Table({
            name: 'orders',
            columns: [
                {
                    name: 'id',
                    type: 'uuid',
                    isPrimary: true,
                    isGenerated: true,
                    default: 'uuid_generate_v4()',
                  },
                  {
                    name: 'customer_id',
                    type: 'uuid',
                  },
                  {
                    name: 'total_amount',
                    type: 'decimal(12,2)',
                  },
                  {
                    name: 'created_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
                  {
                    name: 'updated_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
            ]
        }));

        await queryRunner.createForeignKey(
            'orders',
            new TableForeignKey({
              name: 'customer_id_fk',
              columnNames: ['customer_id'],
              referencedColumnNames: ['id'],
              referencedTableName: 'customers',
            }),
          );
    }

    public async down(queryRunner: QueryRunner): Promise<void> {

        await queryRunner.dropForeignKey(
            'orders',
            new TableForeignKey({
              name: 'customer_id_fk',
              columnNames: ['customer_id'],
              referencedColumnNames: ['id'],
              referencedTableName: 'customers',
            }),
          );

        await queryRunner.dropTable('orders')
    }

}
npm run migrations:create CreateCustomers

Fill in the customers migration:

src/migrations/1648820125567-CreateCustomers.ts view raw
import {MigrationInterface, QueryRunner, Table} from "typeorm";

export class CreateCustomers1648820125567 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(new Table({
            name: 'customers',
            columns: [
                {
                    name: 'id',
                    type: 'uuid',
                    isPrimary: true,
                    isGenerated: true,
                    default: 'uuid_generate_v4()',
                  },
                  {
                    name: 'name',
                    type: 'varchar',
                  },
                  {
                    name: 'email',
                    type: 'varchar',
                  },
                  {
                    name: 'phone_number',
                    type: 'varchar',
                  },
                  {
                    name: 'password_digest',
                    type: 'varchar',
                  },
                  {
                    name: 'created_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
                  {
                    name: 'updated_at',
                    type: 'timestamptz',
                    default: 'now()',
                  },
            ]
        }));
    }


    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropTable('customers')
    }

}
npm run migrations:create CreateOrderItems

Fill in the order items migration:

src/migrations/1649079742594-CreateOrderItems.ts view raw
import {
  MigrationInterface,
  QueryRunner,
  Table,
  TableForeignKey,
} from 'typeorm';

export class CreateOrderItems1649079742594 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'order_items',
        columns: [
          {
            name: 'order_id',
            type: 'uuid',
          },
          {
            name: 'product_id',
            type: 'uuid',
          },
          {
            name: 'unit_price',
            type: 'decimal(12,2)',
          },
          {
            name: 'quantity',
            type: 'integer',
          },
        ],
      }),
    );

    await queryRunner.createForeignKeys('order_items', [
      new TableForeignKey({
        name: 'order_id_fk',
        columnNames: ['order_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'orders',
      }),
      new TableForeignKey({
        name: 'product_id_fk',
        columnNames: ['product_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'products',
      }),
    ]);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropForeignKeys('order_items', [
      new TableForeignKey({
        name: 'order_id_fk',
        columnNames: ['order_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'orders',
      }),
      new TableForeignKey({
        name: 'product_id_fk',
        columnNames: ['product_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'products',
      }),
    ]);
    await queryRunner.dropTable('order_items');
  }
}

Run the Migrations

npm run migrations:run

With TypeORM logging enabled, you should see a very long log printed on the screen:


> storefront-backend@0.0.1 migrations:run
> npm run typeorm -- migration:run


> storefront-backend@0.0.1 typeorm
> node --require ts-node/register ./node_modules/typeorm/cli.js "migration:run"

query: SELECT * FROM current_schema()
query: SHOW server_version;
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'migrations'
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'typeorm_metadata'
query: SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC
0 migrations are already loaded in the database.
4 migrations were found in the source code.
4 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: CREATE TABLE "customers" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" varchar NOT NULL, "email" varchar NOT NULL, "phone_number" varchar NOT NULL, "password_digest" varchar NOT NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "updated_at" timestamptz NOT NULL DEFAULT now(), CONSTRAINT "PK_133ec679a801fab5e070f73d3ea" PRIMARY KEY ("id"))
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1648820125567,"CreateCustomers1648820125567"]
Migration CreateCustomers1648820125567 has been executed successfully.
query: CREATE TABLE "orders" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "customer_id" uuid NOT NULL, "total_amount" decimal(12,2) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "updated_at" timestamptz NOT NULL DEFAULT now(), CONSTRAINT "PK_710e2d4957aa5878dfe94e4ac2f" PRIMARY KEY ("id"))
query: SELECT * FROM current_schema()
query: SELECT * FROM current_database()
query: SELECT "table_schema", "table_name" FROM "information_schema"."tables" WHERE ("table_schema" = 'public' AND "table_name" = 'orders')
query: SELECT TRUE FROM information_schema.columns WHERE table_name = 'pg_class' and column_name = 'relispartition'
query: SELECT columns.*, pg_catalog.col_description(('"' || table_catalog || '"."' || table_schema || '"."' || table_name || '"')::regclass::oid, ordinal_position) AS description, ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype", pg_catalog.format_type("col_attr"."atttypid", "col_attr"."atttypmod") AS "format_type" FROM "information_schema"."columns" LEFT JOIN "pg_catalog"."pg_attribute" AS "col_attr" ON "col_attr"."attname" = "columns"."column_name" AND "col_attr"."attrelid" = ( SELECT "cls"."oid" FROM "pg_catalog"."pg_class" AS "cls" LEFT JOIN "pg_catalog"."pg_namespace" AS "ns" ON "ns"."oid" = "cls"."relnamespace" WHERE "cls"."relname" = "columns"."table_name" AND "ns"."nspname" = "columns"."table_schema" ) WHERE ("table_schema" = 'public' AND "table_name" = 'orders')
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "cnst"."conname" AS "constraint_name", pg_get_constraintdef("cnst"."oid") AS "expression", CASE "cnst"."contype" WHEN 'p' THEN 'PRIMARY' WHEN 'u' THEN 'UNIQUE' WHEN 'c' THEN 'CHECK' WHEN 'x' THEN 'EXCLUDE' END AS "constraint_type", "a"."attname" AS "column_name" FROM "pg_constraint" "cnst" INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid" INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace" LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid" AND "a"."attnum" = ANY ("cnst"."conkey") WHERE "t"."relkind" IN ('r', 'p') AND (("ns"."nspname" = 'public' AND "t"."relname" = 'orders'))
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "i"."relname" AS "constraint_name", "a"."attname" AS "column_name", CASE "ix"."indisunique" WHEN 't' THEN 'TRUE' ELSE'FALSE' END AS "is_unique", pg_get_expr("ix"."indpred", "ix"."indrelid") AS "condition", "types"."typname" AS "type_name" FROM "pg_class" "t" INNER JOIN "pg_index" "ix" ON "ix"."indrelid" = "t"."oid" INNER JOIN "pg_attribute" "a" ON "a"."attrelid" = "t"."oid"  AND "a"."attnum" = ANY ("ix"."indkey") INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "t"."relnamespace" INNER JOIN "pg_class" "i" ON "i"."oid" = "ix"."indexrelid" INNER JOIN "pg_type" "types" ON "types"."oid" = "a"."atttypid" LEFT JOIN "pg_constraint" "cnst" ON "cnst"."conname" = "i"."relname" WHERE "t"."relkind" IN ('r', 'p') AND "cnst"."contype" IS NULL AND (("ns"."nspname" = 'public' AND "t"."relname" = 'orders'))
query: SELECT "con"."conname" AS "constraint_name", "con"."nspname" AS "table_schema", "con"."relname" AS "table_name", "att2"."attname" AS "column_name", "ns"."nspname" AS "referenced_table_schema", "cl"."relname" AS "referenced_table_name", "att"."attname" AS "referenced_column_name", "con"."confdeltype" AS "on_delete", "con"."confupdtype" AS "on_update", "con"."condeferrable" AS "deferrable", "con"."condeferred" AS "deferred" FROM ( SELECT UNNEST ("con1"."conkey") AS "parent", UNNEST ("con1"."confkey") AS "child", "con1"."confrelid", "con1"."conrelid", "con1"."conname", "con1"."contype", "ns"."nspname", "cl"."relname", "con1"."condeferrable", CASE WHEN "con1"."condeferred" THEN 'INITIALLY DEFERRED' ELSE 'INITIALLY IMMEDIATE' END as condeferred, CASE "con1"."confdeltype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confdeltype", CASE "con1"."confupdtype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confupdtype" FROM "pg_class" "cl" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid" WHERE "con1"."contype" = 'f' AND (("ns"."nspname" = 'public' AND "cl"."relname" = 'orders')) ) "con" INNER JOIN "pg_attribute" "att" ON "att"."attrelid" = "con"."confrelid" AND "att"."attnum" = "con"."child" INNER JOIN "pg_class" "cl" ON "cl"."oid" = "con"."confrelid"  AND "cl"."relispartition" = 'f'INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_attribute" "att2" ON "att2"."attrelid" = "con"."conrelid" AND "att2"."attnum" = "con"."parent"
query: ALTER TABLE "orders" ADD CONSTRAINT "customer_id_fk" FOREIGN KEY ("customer_id") REFERENCES "customers"("id")
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1648820154148,"CreateOrders1648820154148"]
Migration CreateOrders1648820154148 has been executed successfully.
query: CREATE TABLE "products" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" varchar NOT NULL, "unit_price" decimal(12,2) NOT NULL, "is_discontinued" boolean NOT NULL, "description" text NOT NULL, "image" varchar NOT NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "updated_at" timestamptz NOT NULL DEFAULT now(), CONSTRAINT "PK_0806c755e0aca124e67c0cf6d7d" PRIMARY KEY ("id"))
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1648820162516,"CreateProducts1648820162516"]
Migration CreateProducts1648820162516 has been executed successfully.
query: CREATE TABLE "order_items" ("customer_id" uuid NOT NULL, "product_id" uuid NOT NULL, "unit_price" decimal(12,2) NOT NULL, "quantity" integer NOT NULL)
query: SELECT * FROM current_schema()
query: SELECT * FROM current_database()
query: SELECT "table_schema", "table_name" FROM "information_schema"."tables" WHERE ("table_schema" = 'public' AND "table_name" = 'order_items')
query: SELECT TRUE FROM information_schema.columns WHERE table_name = 'pg_class' and column_name = 'relispartition'
query: SELECT columns.*, pg_catalog.col_description(('"' || table_catalog || '"."' || table_schema || '"."' || table_name || '"')::regclass::oid, ordinal_position) AS description, ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype", pg_catalog.format_type("col_attr"."atttypid", "col_attr"."atttypmod") AS "format_type" FROM "information_schema"."columns" LEFT JOIN "pg_catalog"."pg_attribute" AS "col_attr" ON "col_attr"."attname" = "columns"."column_name" AND "col_attr"."attrelid" = ( SELECT "cls"."oid" FROM "pg_catalog"."pg_class" AS "cls" LEFT JOIN "pg_catalog"."pg_namespace" AS "ns" ON "ns"."oid" = "cls"."relnamespace" WHERE "cls"."relname" = "columns"."table_name" AND "ns"."nspname" = "columns"."table_schema" ) WHERE ("table_schema" = 'public' AND "table_name" = 'order_items')
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "cnst"."conname" AS "constraint_name", pg_get_constraintdef("cnst"."oid") AS "expression", CASE "cnst"."contype" WHEN 'p' THEN 'PRIMARY' WHEN 'u' THEN 'UNIQUE' WHEN 'c' THEN 'CHECK' WHEN 'x' THEN 'EXCLUDE' END AS "constraint_type", "a"."attname" AS "column_name" FROM "pg_constraint" "cnst" INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid" INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace" LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid" AND "a"."attnum" = ANY ("cnst"."conkey") WHERE "t"."relkind" IN ('r', 'p') AND (("ns"."nspname" = 'public' AND "t"."relname" = 'order_items'))
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "i"."relname" AS "constraint_name", "a"."attname" AS "column_name", CASE "ix"."indisunique" WHEN 't' THEN 'TRUE' ELSE'FALSE' END AS "is_unique", pg_get_expr("ix"."indpred", "ix"."indrelid") AS "condition", "types"."typname" AS "type_name" FROM "pg_class" "t" INNER JOIN "pg_index" "ix" ON "ix"."indrelid" = "t"."oid" INNER JOIN "pg_attribute" "a" ON "a"."attrelid" = "t"."oid"  AND "a"."attnum" = ANY ("ix"."indkey") INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "t"."relnamespace" INNER JOIN "pg_class" "i" ON "i"."oid" = "ix"."indexrelid" INNER JOIN "pg_type" "types" ON "types"."oid" = "a"."atttypid" LEFT JOIN "pg_constraint" "cnst" ON "cnst"."conname" = "i"."relname" WHERE "t"."relkind" IN ('r', 'p') AND "cnst"."contype" IS NULL AND (("ns"."nspname" = 'public' AND "t"."relname" = 'order_items'))
query: SELECT "con"."conname" AS "constraint_name", "con"."nspname" AS "table_schema", "con"."relname" AS "table_name", "att2"."attname" AS "column_name", "ns"."nspname" AS "referenced_table_schema", "cl"."relname" AS "referenced_table_name", "att"."attname" AS "referenced_column_name", "con"."confdeltype" AS "on_delete", "con"."confupdtype" AS "on_update", "con"."condeferrable" AS "deferrable", "con"."condeferred" AS "deferred" FROM ( SELECT UNNEST ("con1"."conkey") AS "parent", UNNEST ("con1"."confkey") AS "child", "con1"."confrelid", "con1"."conrelid", "con1"."conname", "con1"."contype", "ns"."nspname", "cl"."relname", "con1"."condeferrable", CASE WHEN "con1"."condeferred" THEN 'INITIALLY DEFERRED' ELSE 'INITIALLY IMMEDIATE' END as condeferred, CASE "con1"."confdeltype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confdeltype", CASE "con1"."confupdtype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confupdtype" FROM "pg_class" "cl" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid" WHERE "con1"."contype" = 'f' AND (("ns"."nspname" = 'public' AND "cl"."relname" = 'order_items')) ) "con" INNER JOIN "pg_attribute" "att" ON "att"."attrelid" = "con"."confrelid" AND "att"."attnum" = "con"."child" INNER JOIN "pg_class" "cl" ON "cl"."oid" = "con"."confrelid"  AND "cl"."relispartition" = 'f'INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_attribute" "att2" ON "att2"."attrelid" = "con"."conrelid" AND "att2"."attnum" = "con"."parent"
query: ALTER TABLE "order_items" ADD CONSTRAINT "order_id_fk" FOREIGN KEY ("order_id") REFERENCES "orders"("id")
query: ALTER TABLE "order_items" ADD CONSTRAINT "product_id_fk" FOREIGN KEY ("product_id") REFERENCES "products"("id")
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1649079742594,"CreateOrderItems1649079742594"]
Migration CreateOrderItems1649079742594 has been executed successfully.
query: COMMIT

According to the above log, all our pending migrations have executed successfully.

SOURCES