pg2sequelize

A command-line tool to convert PostgreSQL CREATE TABLE statements to Sequelize models and migrations.

Installation

# Install globally
npm install -g pg2sequelize

# Or locally in your project
npm install --save-dev pg2sequelize

Usage

pg2sequelize <sql-file>

#or with options
pg2sequelize <sql-file> [options]

Options

-c, --config : Path to the .sequelizerc config file (default: .sequelizerc).

Example:

pg2sequelize create-table.sql

#or with options
pg2sequelize create-table.sql -c ./config/.sequelizerc

This will:

1. Parse the PostgreSQL CREATE TABLE statement in the SQL file
2. Generate a Sequelize model file in the models directory
3. Generate a Sequelize migration file in the migrations directory

Features

• Supports multiple table definitions in a single SQL file
• Converts PostgreSQL column types to Sequelize data types
• Handles primary keys, auto-increment fields, and unique constraints
• Supports foreign keys with associations
• Ignores triggers
• Configurable output paths via .sequelizerc
• Formats output using Prettier

Configuration

The tool will automatically look for a .sequelizerc file in your project root to determine the correct paths for models and migrations. If not found, it uses the default models/ and migrations/ directories. You can specify custom paths for models and migrations using a .sequelizerc file. Example:

const path = require('path');

module.exports = {
    config: path.resolve('config', 'config.js'),
    'models-path': path.resolve(__dirname, 'db/models'),
    'seeders-path': path.resolve(__dirname, 'db/seeders'),
    'migrations-path': path.resolve(__dirname, 'db/migrations'),
};

Example

Given a PostgreSQL CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS public.users (
    id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    username character varying(50) COLLATE pg_catalog."default" NOT NULL,
    email character varying(100) COLLATE pg_catalog."default" UNIQUE,
    created_at timestamp without time zone,
    CONSTRAINT users_pkey PRIMARY KEY (id)
)

The tool will generate:

1. A Sequelize model file (models/User.js):

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    static associate(models) {
      // define associations here
    }
  }
  User.init({
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false
    },
    username: {
      type: DataTypes.STRING(50),
      allowNull: false
    },
    email: {
      type: DataTypes.STRING(100),
      unique: true
    },
    created_at: DataTypes.DATE
  }, {
    sequelize,
    modelName: 'User',
    tableName: 'users',
    timestamps: false
  });
  return User;
};

2. A Sequelize migration file (migrations/[timestamp]-create-users.js):

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      username: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      email: {
        type: Sequelize.STRING(100),
        unique: true
      },
      created_at: {
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  }
};

Handling Triggers

The tool now supports automatic generation of triggers from PostgreSQL CREATE TABLE statements.

Triggers are included as raw SQL queries in the generated migration files.

Supports creating, updating, and deleting triggers using Sequelize migrations.

To use these files in a Sequelize project:

Place the model file in your models directory Place the migration file in your migrations directory Run the migration with:

npx sequelize-cli db:migrate

License

MIT