CODEWITHSUNDEEP
postgresqlforeign-keyssequelize.js
bir_ham
bir_ham

Reputation: 513

Postgres Foreign-key constraints in non public schema

I have a question regarding constraints on custom schemas. My app creates a new/separate schema for each clients corresponding with clients' name (i.e .clienta, clientb,...). Some of the tables have a foreign-key constraints but, they don't work on schemas other than the default public schema. For example, let's say there is schema called clienta and it has projects and tasks tables, model Task has a belongsTo(models.Project) association (i.e projects table primary_key is a foreign_key for table tasks. The issue starts here: when trying to create a record in table tasks there comes an error saying foreign key violation error... Key (project_id)=(1) is not present in table "projects... even though projects table has the respective record with id = 1. I am wording if this is a limitation of sequelize library itself or am I missing something in the configs?

Sequelize config

"development": {
    "database": "my_app",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "operatorsAliases": "Sequelize.Op",
    "dialectOptions": {
      "prependSearchPath": true
    },
    "define": {
      "underscored": true
    }
  }

Example of create function:

models.Task.create({...args}, { searchPath: 'clienta' })

N.B Everything works as expected in public schema.

Upvotes: 8

Views: 558

Answers (1)

Koen
Koen

Reputation: 734

The sync method API lists two options relating to DB-schema:

options.schema - schema the table should be created in

options.searchPath - optional parameter to set searchPath (Postgresql)

When using schemas other than the default and an association between Models has been created (using for instance belongsTo), it is important to set the searchPath to hold the name of the schema of the target table. Following the explanation in search_path in postgresql, not specifying the searchPath will have the constraint referring to a table (if it exists) in the default schema (usually 'public').

Upvotes: 1

Related Questions