CODEWITHSUNDEEP
node.jspostgresqlschemasequelize.jsdatabase-schema
Val
Val

Reputation: 1350

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

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;

Upvotes: 12

Views: 45518

Answers (4)

the Titan way
the Titan way

Reputation: 11

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 });
  }
})();

Upvotes: 0

krys Funtain
krys Funtain

Reputation: 11

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: '[email protected]',
            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.

Upvotes: 1

piotrbienias
piotrbienias

Reputation: 7411

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.

Upvotes: 24

Edward Smith
Edward Smith

Reputation: 552

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.
  }

Upvotes: 2

Related Questions