Sequelize schema for PostgreSQL: How to accurately define a schema in a model?

Question

I searched throughout the net and have not been able to determine how to add a schema to this sequelize model below. The following code does not kick back errors, however when I inspect the postgres DB, the only schema is the default one for public.

// The model definition is done in /path/to/models/project.js
module.exports = function(sequelize, DataTypes) {
  return sequelize.define("project", {
    name: DataTypes.STRING,
    description: DataTypes.TEXT,
  },
    define: {
        schema: "prefix"
    },
    classMethods: {
      method1: function() {},
      method2: function() {}
  },
    instanceMethods: {
      method3: function() {}
  })

How should the script be revised to accurately define a schema?

EDIT

In my case, the final answer was

 database_name.sequelize.createSchema('prefix').then(() => {...});

in my ./models/index.js file the database object is as follows:

database_name = {
    Sequelize: Sequelize,
    sequelize: sq,
    table_1: sq.import(__dirname + '/file_folder')
 };

module.exports = database_name;

piotrbienias · Accepted Answer · 2017-02-28T20:22:29.807

22

Your model definition should look as follows

module.exports = function(sequelize, DataTypes) {

    return sequelize.define("project", {
        name: DataTypes.STRING,
        description: DataTypes.TEXT,
    }, {
        schema: 'prefix',
        classMethods: {
            method1: function() {},
            method2: function() {}
        },
        instanceMethods: {
            method3: function() {}
        }
    }
}

According to the documentation of options object in sequelize.define method, it can have attribute called schema.

EDIT - Creating schema programatically

In order to create a new schema (only for PostgreSQL!), you can use the sequelize.createSchema() method:

sequelize.createSchema('prefix').then(() => {
    // new schema is created
});

Above creates given SQL

CREATE SCHEMA prefix;

In order to use this schema in model definitions, you need to create the schema before synchronising any model into the database - it could be run before sequelize.sync() or, if you use migrations, as a first migration file.

edited Feb 28 '17 at 20:22

answered Feb 28 '17 at 10:02

piotrbienias

7,201
1
28
33

This does not work. It kicks back "schema does not exist" – Val Feb 28 '17 at 17:42
Do you use `sequelize.sync()` to synchronize the database or you use migrations? – piotrbienias Feb 28 '17 at 17:50
Because you need to create a schema before defining models in it – piotrbienias Feb 28 '17 at 19:02
I don't use migrations yet. Yes I use sequelize.sync() to synchronize the DB. Can I define it programmatically without going doing it through the CLI? – Val Feb 28 '17 at 20:08
I have edited the answer to include the schema creation part – piotrbienias Feb 28 '17 at 20:22
In my specific case, I had preformed the following: database_name.sequelize.createSchema('prefix').then(() => {...}); This was based on how I define the database in the index.js file. – Val Mar 01 '17 at 04:24

score 2 · Answer 2 · answered Feb 28 '17 at 00:15

I think you need to define the schema in the create table migration file like so:

queryInterface.createTable(
  'nameOfTheNewTable',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    createdAt: {
      type: Sequelize.DATE
    },
    updatedAt: {
      type: Sequelize.DATE
    },
    attr1: Sequelize.STRING,
    attr2: Sequelize.INTEGER,
    attr3: {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
      allowNull: false
    },
    //foreign key usage
    attr4: {
        type: Sequelize.INTEGER,
        references: {
            model: 'another_table_name',
            key: 'id'
        },
        onUpdate: 'cascade',
        onDelete: 'cascade'
    }
  },
  {
    engine: 'MYISAM',                     // default: 'InnoDB'
    charset: 'latin1',                    // default: null
    schema: 'prefix'                      // default: public, PostgreSQL only.
  }

This will change the default schema. The question is specific to changing the schema for a specific model. — Val, Feb 28 '17 at 01:56

score 1 · Answer 3 · answered Nov 29 '17 at 23:41

This code works with "sequelize": "^4.23.2","pg": "^7.4.0", "pg-hstore": "^2.3.2",

const User = sequelize.define('people', {
        uuid: {
            type: Sequelize.UUID,
            defaultValue: Sequelize.UUIDV1,
            primaryKey: true
        },
        username: Sequelize.STRING,
        email: Sequelize.STRING,
        birthday: Sequelize.DATE
    }, {
            schema: 'public',
        });

    sequelize.sync()
        .then(() => User.create({
            username: 'MartialDoane',
            email: 'martial-doane@gmail.com',
            birthday: new Date(1977, 6, 11)
        }))
        .then(jane => {
            console.log(jane.toJSON());

            res.send(jane);
            res.status(200);
        });

That will create the table in schema public and not my default schema.

this is not working for me.The DB have table under schema called public so I have added {schema: 'public'} in the model but the find query returns empty object — Ujjual, Apr 20 '20 at 13:57

score 0 · Answer 4 · answered Mar 23 '22 at 12:06

Try this to create schema Programmatically before creating models.

Add this code immediately after import statements.

Replace "some_schema" with Your "Schema Name"

file: models/index.js

1.JavaScript Implementation

 (async function () {
  await sequelize.showAllSchemas({ logging: false }).then(async (data) => {
        if (!data.includes('some_schema')) {
         await sequelize.createSchema('some_schema');
        }
        if (!data.includes('some_schema2')) {
         await sequelize.createSchema('some_schema2');
       }
  });
}());

Tip: 1st time npm start will throw error that schema doesnt exist ,close npm terminal and npm start again ,it will work without any error on 2nd time,because of async nature.

2.Typescript Implementation :

(async function () {
  const allSchema: Array<string> = await sequelize
    .showAllSchemas({ logging: false })
    .then((data) => data.map((ele) => ele.toString()));
  if (!allSchema.includes('some_schema')) {
    await sequelize.createSchema('some_schema', { logging: false });
  }
})();

Sequelize schema for PostgreSQL: How to accurately define a schema in a model?

4 Answers4