How to specify database schemas for different models?

[ArsalanDotMe]

I've been at it for hours and still can't get it to work. I have a postgres database with two schemas, lets say s1 and s2 such that my table names are then s1.name or s2.name.

Now, if I rewrite all tablename references with schema included, objection relations stop working saying that I must specify the original table in either the from or to clause.
I have also tried subclassing Model and overriding the query method with something like this:

class ApiModel extends Model {
}
ApiModel.query = function query() {
  return Model.query.call(this).withSchema('s1');
};

But when I analyze the queries, objection is still making queries without the schema applied to table names. How do I handle default schemas for my models?

[koskimas]

So you have two schemas and tables that only exist in one but not the other and you want to create models that always use a certain schema? And there are relations between the models so that Schema1Model can have, lets say, HasOneRelation to Schema2Model?

What version of objection are you using?

[ArsalanDotMe]

There isn't currently a case where a model in Schema1 may have a relation with Schema2. In my case, I have a database shared by several internal applications, each storing its data inside its own schema. Models for each application will use a certain schema and will have relationships with other models of the same application on the same schema.

EDIT: Using version 0.4.0

[koskimas]

Ok. Can you check the objection version also?

[ArsalanDotMe]

The objection version is 0.4.0.

[koskimas]

Can you try to install npm install objection@next and check if the problems continue? There has just been some major changes to how withSchema works in the 0.5.0 alpha builds.

[ArsalanDotMe]

Okay but how should I specify my schemas for my models?
As far as I understand, I have two options:

1. Override the Model class in a way that applies withSchema to the QueryBuilder.
2. Specify the schema inside tablenames. E.g.

class MyModel extends Model {
   static get tableName() { return 'schema.tablename'; }
}

Which way should I go with? I'm leaning towards the first but I'm not sure how to accomplish it.

[koskimas]

Setting the schema to the tableName doesn't work. knex doesn't interpret the dot as a schema separator, but as a part of the table name.

You can try implementing your own query builder class:

class DefaultSchemaQueryBuilder extends objection.QueryBuilder {
  constructor(modelClass) {
    super(modelClass);
    if (modelClass.defaultSchema) {
      this.withSchema(modelClass.defaultSchema);
    }
  }
}

objection.Model.QueryBuilder = DefaultSchemaQueryBuilder;
objection.Model.RelatedQueryBuilder = DefaultSchemaQueryBuilder;

and then in your models:

class Schema1Model extends Model {
  static get defaultSchema() {
    return 's1';
  }
}

class Schema2Model extends Model {
  static get defaultSchema() {
    return 's2';
  }
}

The problem may be caused by the fact that not all queries are created using the Model.query() method.

[ArsalanDotMe]

Thanks for the quick response! I'm trying this now with the latest version.

[ArsalanDotMe]

Nice!
It works with the version 0.5.0-alpha.2!
I tried doing something similar before but I wasn't overriding the RelatedQueryBuilder property. I'll try downgrading to the latest stable release and report if it still works.

[ArsalanDotMe]

Works in 0.4.0 too. Thanks again for the quick response. You're awesome!

[koskimas]

Great! ❤️

[paseaf]

Is this solution still relevant for Objection version 2.X.X?

[parker789]

Hey @koskimas - sorry to bring up such an old thread here, but myself and team are at point where we need to make a decision on whether or not we can move forward using Objection as our ORM due to an issue related to what's written here. My problem lies in this question you asked above:

And there are relations between the models so that Schema1Model can have, lets say, HasOneRelation to Schema2Model?

I was able to get a somewhat working solution, although with everything I've read it seems to be a hack, by implementing this from another issue I read through:

class SomeModel extends Model {
  static get tableName {
    return 'schemaOne.someModels'
  }

  static get relationMapping() {
    return {
      relatedModel: {
        relation: Model.HasOneRelation,
        modelClass: 'RelatedModel',
        join: {
          from: 'schemaOne.someModels.id',
          to: 'schemaTwo.relatedModels.someModelsId',
        },
      },
    }
  }
}

This seemed to work when just querying, but when mixed with objection-find and an orderBy, the generated sql had a mismatch between the aliased table: [schema1.someModels], and in the select: [schema1].[someModels].[id]. Is this supported in some way?

Thanks!