Problem using another schema than 'public' on a PostgreSQL database

[jlwarg]

Version I'm using:

$ sequelize-automate --version
1.2.0

I've tried to use this configuration

module.exports = {
    dbOptions: {
        database: "mydb",
        username: "user",
        password: "mypassword",
        dialect: "postgres",
        host: "myhost",
        port: "5432",
        schema: "app"
    },
    options: {
        type: "js",
        dir: "new",
    }
}

And got this result:

$ sequelize-automate -c .sequelize-automate-new.config.js
Database options: {"database":"mydb","username":"user","password":"****","dialect":"postgres","host":"myhost","port":"5432","schema":"app"}
Automate options: {"type":"js","camelCase":false,"dir":"new","emptyDir":false}
Executing (default): SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type LIKE '%TABLE' AND table_name != 'spatial_ref_sys';
Done!

As you can see, "app" gets recognized as the value of Database option "schema", but when the list of tables gets queried, "public" is used as the schema instead. I suspect that the cause of this is the function showTablesQuery() in the sequelize codebase:

showTablesQuery() {
    return "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type LIKE '%TABLE' AND table_name != 'spatial_ref_sys';";
}

https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/query-generator.js#L117-L119

IMHO this function should take a parameter schema that defaults to "public" instead of just querying the "public" schema. I haven't investigated further yet because I'm new to sequelize and sequelize-automate and am not sure that I'm using it correctly.

[gioppoluca]

I have the same problem. PostgreSQL schema is not taken into account. Is there a proper way to address this or is a missing feature? Thanks

[nodejh]

Hi @gioppoluca, could you give me a demo schema of PostgreSQL table? Let me have a test.

[osmanraifgunes]

https://github.com/sequelize/sequelize/issues/11948

[osmanraifgunes]

temporary solution is https://github.com/nodejh/sequelize-automate/issues/18#issuecomment-634709491