Objection.js: Exclude column from Query

[matheustav]

Hi. First of all, congratulations for this awesome tool.
English is not my first language, so I apologize for eventual mistakes.

It's relatively common for ORMs to read the tables before execution to know exactly all the columns and its specifications. For example: in Mysql/MariaDb: DESCRIBE products; could return something like this:

Field       Type               Null     Key     Default    Extra
id          int(11) unsigned   NO       PRI     NULL       auto_increment
uuid        char(36)           NO               NULL
name        varchar(255)       NO               NULL
created_at  datetime           NO               NULL
updated_at  datetime           YES              NULL

By doing this, the QueryBuilder could start all SELECT queries with all fields by default, instead of *. And this is great for several reasons.

I'd like to know if there is a way to enable this?
At this moment all my queries are using "select table.* [...]"

My second question is: how to exclude columns from my query?
I already saw how to omit columns after fetched, but i think this is a really poor solution, since my query:

static findByUUID( uuid ) {
        return this.query().where( 'uuid', uuid ).first().omit( [ 'id' ] );
    }

... Is being written like this anyway:
select `products`.* from `products` where `uuid` = ?

Summing up: I'm just trying to do this:

    `products`.`uuid`,
    `products`.`name`,
    `products`.`created_at`,
    `products`.`updated_at`
from `products` where `uuid` = ?

I really appreciate any contributions, since I'm really new to Knex and/or Objection.js.

[koskimas]

Hi @matheustav

There's no built-in support for this in objection _for all queries_. Currently joinEager does this because there we need to select each column individually so that we can give them aliases.

I'd like to add this feature, but there are couple of problems and things to solve. Objection has no asynchronous initialization function that needs to be called before it can be used. Therefore there is no predicable single place to run these metadata queries. We could run them with the first query that actually needs the columns, but that would cause unpredicable queries. I don't know if it matters though since it's only the first query. We would need to run one of those per table, whenever a new table is encountered since objection has no central registry that knows about all tables.

Here are some questions that need answering before we can do this:

1. Does it really matter if we run one unpredictable query (the metadata query) when a model is used for the first time? What downsides does it have?

2. Should we also add some initialization function that could be called to avoid the unpredictable queries:

await objection.initialize([Person, Pet, Movie, Comment])

1. Are there any downsides in selecting all columns separately instead of using foo.*?

2. Should this be the default or enabled using a flag? I'd like to avoid adding yet another configuration option.

While I agree this would be a good feature if implemented in a way that doesn't break any existing stuff, I think some of the issues in the link you posted were irrelevant to this case or simply not true.

since you're not giving your database any hints as to what you want, it will first need to check the table's definition in order to determine the columns on that table. That lookup will cost some time - not much in a single query - but it adds up over time

The cost of finding out columns in the database side is insignificant compared to the actual query.

if you start to rely on certain aspects of the data, e.g. the order of the columns returned, you could get a nasty surprise once the table is reorganized and new columns are added (or existing ones removed)

Who does this, seriously 😄

in SQL Server (not sure about other databases), if you need a subset of columns, there's always a chance a non-clustered index might be covering that request (contain all columns needed).

I don't really get this. Does SQL Server create indexes without you knowing about them? If you are aware of an index, you certainly know how to use it and it usually only covers a couple of columns. I don't think there's a chance of accidentally hitting an index with a set of columns. What I mean is that in order to hit an index, you need to list the couple of columns it contains explicitly anyway.

Only the first point is really relevant here. Otherwise it is 100% same thing whether you select all columns explicitly, or select all columns using table.*.

BTW. your english seems perfect to me (I'm Finnish).

[koskimas]

I forgot to mention why I think this would be a good feature:

1. We could implement an unselect (name can be debated) method requested here.
2. joinEager already needs the metadata and running the queries in joinEager has already caused some issues (like QueryBuilder.toString() only working after the first query).
3. This would open up new possibilities in the future.

[koskimas]

Here's an example plugin that implements the feature using only public interface:

const Metadata = (Model) => {
  class MetadataQueryBuilder extends Model.QueryBuilder {
    constructor(...args) {
      super(...args);

      // Whenever a query builder is created, register a function to be called
      // during it's build phase.
      this.onBuild(builder => {
        // Select all columns except excluded ones, but only if there are no
        // explicit selects.
        if (!builder.hasSelects()) {
          const modelClass = builder.modelClass();
          // The correct table reference. Can be other than the table name
          // for example if an alias is used.
          const table = builder.tableRefFor(modelClass);

          builder.select(modelClass.$$columns
            .filter(col => !builder.$$unselect || !builder.$$unselect.includes(col))
            .map(col => `${table}.${col}`));
        }
      });
    }

    // We need to override `clone` too so that our custom property
    // is cloned too.
    clone(...args) {
      const copy = super.clone(...args);

      if (this.$$unselect) {
        Object.defineProperty(copy, '$$unselect', {
          enumerable: false,
          value: this.$$unselect
        });
      }

      return copy;
    }

    // This is a new query builder method that can be used to exclude columns.
    unselect(columns) {
      // We use `Object.defineProperty` to hide our custom property. 
      // This is not necessary. You could just use `this.$$unselect = something`
      // or even `this.unselected = something`. `$` prefix is just a convention for
      // "internal" properties.
      Object.defineProperty(this, '$$unselect', {
        enumerable: false,
        value: columns
      });

      return this;
    }
  }

  return class extends Model {
    // Register our custom query builder to be used instead of the default one.
    static get QueryBuilder() {
      return MetadataQueryBuilder;
    }

    // An initialization function that fetches the column metadata. This only
    // needs to be called once. `knex` argument is optional and only needed
    // if you haven't given the knex connection using `Model.knex(knex)` or
    // any other supported way.
    static initialize(Models, knex) {
      return Promise.all(Models.map(async (Model) => {
        const columnInfo = await  Model.query(knex).columnInfo();

        // We use `Object.defineProperty` to hide our custom property. 
        // This is not necessary. You could just use `Model.$$columns = something`
        // or even `Model.columns = something`. `$` prefix is just a convention for
        // "internal" properties.
        Object.defineProperty(Model, '$$columns', {
          enumerable: false,
          // Run the properties through `propertyNameToColumnName` to make sure
          // any possible column name conversion (for example snake_case -> camelCase)
          // is done.
          value: Object.keys(columnInfo).map(prop => Model.propertyNameToColumnName(prop))
        });
      }));
    }
  };
}

You would use it like this:

const { Model } = require('objection');

class BaseModel extends Metadata(Model) {

}

...

class Product extends BaseModel {
  ...
}

...

// In some initialization code
await BaseModel.initialize([Product, Foo, Bar, Baz, Spam, All, Your, Models]);

...

// Use it
static findByUUID( uuid ) {
    return this.query().findOne({ uuid }).unselect( [ 'id' ] );
}

[matheustav]

Hi @koskimas. Thanks for your helpful and fast reply!

Objection has no asynchronous initialization function that needs to be called before it can be used. Therefore there is no predicable single place to run these metadata queries. We could run them with the first query that actually needs the columns, but that would cause unpredicable queries.

I don't see any problems here, but I think that it shouldn't be ran at first query.
At this moment we have Model.knex( knex );, so why not do it like this instead?

Model.initialize( knex, options = {} );

In options object we could even add tweaks like "useLimitInFirst" 😄

Does it really matter if we run one unpredictable query (the metadata query) when a model is used for the first time? What downsides does it have?

I don't think this should be a concern. Just to exemplify, I work in a project which uses Phalcon and that's exactly what they do:

SHOW TABLES;
DESCRIBE table1;
DESCRIBE table2;

(As you can see, there's also no need to pass the tables manually to ORM...)
After this, they store this information in Cache, but the dev has the option to retrieve and store this info by other ways.

The piece of code you posted is just awesome. I'll try to use it, then I share my thoughts here, but I think this might fit perfectly.

Thank you very much!

[koskimas]

Model.initialize( knex, options = {} );

This would be a major breaking change and break all objection code in existence. Also it would have to be async forcing all code calling it to be asynchronous too. Also we would have to change objection significantly so that Model.initialize would know about models that have been created. I don't want to add such global state.

Without global state (lack of global state is one of the main design principles behind objection) we cannot query and cache metadata of all tables in one place. We need to have access to the model constructors so that we can store the metadata as properties of the constructors.

[koskimas]

So we either have to add a breaking change _or_ run the metadata queries lazily when needed (and then off course cache them). My proposal to add initialize etc. method was to offer an easy way to run these lazy initilization functions in a controlled manner __if__ the user wants to do so. If a call to initialize is not done metadata is fetched lazily.

[matheustav]

This would be a major breaking change and break all objection code in existence.

Oh, you're totally right! Sorry, but i'm not used to write code for other devs.

Without global state (lack of global state is one of the main design principles behind objection) we cannot query and cache metadata of all tables in one place. We need to have access to the model constructors so that we can store the metadata as properties of the constructors.

This makes sense too.

But for example, I'm not using jsonSchema, since it's said in the docs that this only serves to validation purposes, but why not use this information as metadata?
This way we forget about SHOW TABLES / DESCRIBE, but all the metadata information could be manually inserted. 😄

[koskimas]

jsonSchema describes the "outside world representation" of a model and not the table schema. Those two can be different. As a contrived example: fullName for a Person, which would then be stored as firstName and lastName in the db.

I'd really like to have this metadata present for the models. It would open up a bunch of feature/validation possibilities. Can you give me an example why querying the metadata lazily during the first real query is a bad idea (other than unpredictable performance of the first query)? That seems about the only way this could be added to objection.

[koskimas]

We could add an option to parse the metadata from jsonSchema. If your jsonSchema and database schema are equal (pretty common I'd quess) you could tell objection to use jsonSchama as the database schema.

[matheustav]

Can you give me an example why querying the metadata lazily during the first real query is a bad thing?

I could say performance, but I know this wouldn't be much significant.
I also think it's a side effect, and a bad one. I mean, you try to run a query but your app runs two?

We could add an option to parse the metadata from jsonSchema.

Yes, I think that could be a good option, but I was reading the plugin you wrote and I think it's almost perfect exactly because I don't have to write the jsonSchema. I'm definitely going to use it.

[koskimas]

I could say performance, but I know this wouldn't be much significant.

It would only be executed once during the lifetime of the process. The performance impact is only significant if you expect the first query to be fast.

I also think it's a side effect, and a bad one. I mean, you try to run a query but your app runs two?

Yeah, this is the only bad thing I was able to come up with.

[koskimas]

After more consideration, this is too much of a hack for objection.

[rickysullivan]

I'm using Schwifty, which relies on Joi for schema validations on models. I specify which fields I don't want to show using .strip(). When someone hits my API endpoint (Hapi). I get the Joi schema for the model. Filter out (using lodash) any fields with the flag strip. Then make my query specifying the columns by using the filter Joi schema.

module.exports = class DataCentre extends Schwifty.Model {
  static get tableName() {
    return "DataCentres";
  }

  static get joiSchema() {
    return Joi.object({
      id: Joi.string().required(),
      name: Joi.string().required(),
      host: Joi.string()
        .required()
        .strip(),
      user: Joi.string()
        .required()
        .strip(),
      password: Joi.string()
        .required()
        .strip(),
      database: Joi.string().required(),
      timezone: Joi.string().required(),
      online: Joi.boolean()
        .default(false)
        .required()
    });
  }
};

const { DataCentre } = request.models();

const joiSchema = DataCentre.joiSchema.describe();

const selectCols = _(joiSchema.children)
  .omitBy("flags.strip")
  .keys()
  .value();

const result = await DataCentre.query().columns(selectCols);

return result;