Doing CRUD to a specific schema in Postgres

[v4v4n]

Hello,

How to do CRUD to a table for a specific schema ?

I am building SAAS product with schema isolation method
each tenant has their own schema, but all those schemas has the same structure

thank you

[juanluispaz]

Hi,

I'm not sure how your final queries looks like, let me point some possibilities:

1. When you map a table or view, you provide the table name in the constructor, that table name is a raw sql, then you can put whatever you need to refer to the table like: public.author, see this test example in the code generator

2. In the case the schema has a different name per tenant, and the generated sql query needs to reflect that difference you can do this:

In your DBConnection class will be a property with the tenant Id you want to refer, and the expression {tenantId} will be replace from your table or column identifiers with that value (see the scape method)

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 
    tenantId: string

    protected escape(identifier: string, strict: boolean): string {
        identifier = identifier.replace('{tenantId}', this.tenantId)
        return super.escape(identifier, strict)
    }
    constructor(tenantId: string, queryRunner: QueryRunner) {
        super(queryRunner)
        this.tenantId = tenantId
    }
}

Then when you map your tables, you put {tenantId} in de identifiers where you want to use it. Example:

const tCompany = new class TCompany extends Table<DBConnection, 'TCompany'> {
    id = this.autogeneratedPrimaryKey('id', 'int')
    name = this.column('name', 'string')
    parentId = this.optionalColumn('parent_id', 'int')
    constructor() {
        super('{tenantId}.company'); // table name in the database
    }
}()

Then, when you want to create the DBConnection instance, you will need to provide the tenant Id you want to refer.

Please let me know if the information here answer your question and share some feedback on it.