Objection.js: JSON Schema conversion with relations

[jballanger]

Hello, I'm having a problem when using both the JSON Schema conversion feature (object to JSON and JSON to object) and relations at the same time.

By doing a simple query it works fine:

const jennifer = await Person.query()
  .findOne({ firstName: 'Jennifer' });

/*
Person {
  id: 1,
  parentId: null,
  firstName: 'Jennifer',
  lastName: 'Lawrence',
  age: null,
  address: { street: 'Street', city: 'City', zipCode: 'Zipcode' } }
*/

(The field address is returned as an object)

But when trying to select an object from a relation, it will be returned as a string (the JSON Schema conversion is skipped?):

const doggo = await Animal.query()
  .select(
    'animal.*',
    'owner.firstName as ownerFirstName',
    'owner.address as ownerAddress',
  )
  .leftJoinRelation('owner')
  .findOne({ name: 'Doggo' });

/*
Animal {
  id: 1,
  ownerId: 1,
  name: 'Doggo',
  species: 'dog',
  ownerFirstName: 'Jennifer',
  ownerAddress: '{"street":"Street","city":"City","zipCode":"Zipcode"}' }
*/

(The field ownerAddress is not returned as an object but as a string)

Is it expected to not have the JSON Schema conversions when selecting fields from a relation?

Thanks!

Here's a reproduction file I made from reproduction-template.js.

/**
 * This is a simple template for bug reproductions. It contains three models `Person`, `Animal` and `Movie`.
 * They create a simple IMDB-style database. Try to add minimal modifications to this file to reproduce
 * your bug.
 *
 * install:
 *    npm install objection knex sqlite3 chai
 *
 * run:
 *    node reproduction-template
 */

const Model = require('objection').Model;

const Knex = require('knex');
const chai = require('chai');

async function main() {
  await createSchema();

  ///////////////////////////////////////////////////////////////
  // Your reproduction
  ///////////////////////////////////////////////////////////////

  await Person.query().insertGraph({
    firstName: 'Jennifer',
    lastName: 'Lawrence',
    address: {
      street: 'Street',
      city: 'City',
      zipCode: 'Zipcode',
    },
    pets: [
      {
        name: 'Doggo',
        species: 'dog'
      }
    ]
  });

  const jennifer = await Person.query()
    .findOne({ firstName: 'Jennifer' });
  const doggo = await Animal.query()
    .select(
      'animal.*',
      'owner.firstName as ownerFirstName',
      'owner.address as ownerAddress',
    )
    .leftJoinRelation('owner')
    .findOne({ name: 'Doggo' });
  chai.expect(jennifer.address.street).to.equal('Street');
  chai.expect(doggo.ownerAddress.street).to.equal('Street');
}

///////////////////////////////////////////////////////////////
// Database
///////////////////////////////////////////////////////////////

const knex = Knex({
  client: 'sqlite3',
  useNullAsDefault: true,
  debug: false,
  connection: {
    filename: ':memory:'
  }
});

Model.knex(knex);

///////////////////////////////////////////////////////////////
// Models
///////////////////////////////////////////////////////////////

class Person extends Model {
  static get tableName() {
    return 'Person';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['firstName', 'lastName'],

      properties: {
        id: { type: 'integer' },
        parentId: { type: ['integer', 'null'] },
        firstName: { type: 'string', minLength: 1, maxLength: 255 },
        lastName: { type: 'string', minLength: 1, maxLength: 255 },
        age: { type: 'number' },

        address: {
          type: 'object',
          properties: {
            street: { type: 'string' },
            city: { type: 'string' },
            zipCode: { type: 'string' }
          }
        }
      }
    };
  }

  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: Animal,
        join: {
          from: 'Person.id',
          to: 'Animal.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: Movie,
        join: {
          from: 'Person.id',
          through: {
            from: 'Person_Movie.personId',
            to: 'Person_Movie.movieId'
          },
          to: 'Movie.id'
        }
      },

      children: {
        relation: Model.HasManyRelation,
        modelClass: Person,
        join: {
          from: 'Person.id',
          to: 'Person.parentId'
        }
      },

      parent: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'Person.parentId',
          to: 'Person.id'
        }
      }
    };
  }
}

class Animal extends Model {
  static get tableName() {
    return 'Animal';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['name'],

      properties: {
        id: { type: 'integer' },
        ownerId: { type: ['integer', 'null'] },
        name: { type: 'string', minLength: 1, maxLength: 255 },
        species: { type: 'string', minLength: 1, maxLength: 255 }
      }
    };
  }

  static get relationMappings() {
    return {
      owner: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'Animal.ownerId',
          to: 'Person.id'
        }
      }
    };
  }
}

class Movie extends Model {
  static get tableName() {
    return 'Movie';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['name'],

      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 255 }
      }
    };
  }

  static get relationMappings() {
    return {
      actors: {
        relation: Model.ManyToManyRelation,
        modelClass: Person,
        join: {
          from: 'Movie.id',
          through: {
            from: 'Person_Movie.movieId',
            to: 'Person_Movie.personId'
          },
          to: 'Person.id'
        }
      }
    };
  }
}

///////////////////////////////////////////////////////////////
// Schema
///////////////////////////////////////////////////////////////

async function createSchema() {
  await knex.schema
    .dropTableIfExists('Person_Movie')
    .dropTableIfExists('Animal')
    .dropTableIfExists('Movie')
    .dropTableIfExists('Person');

  await knex.schema
    .createTable('Person', table => {
      table.increments('id').primary();
      table
        .integer('parentId')
        .unsigned()
        .references('id')
        .inTable('Person');
      table.string('firstName');
      table.string('lastName');
      table.integer('age');
      table.json('address');
    })
    .createTable('Movie', table => {
      table.increments('id').primary();
      table.string('name');
    })
    .createTable('Animal', table => {
      table.increments('id').primary();
      table
        .integer('ownerId')
        .unsigned()
        .references('id')
        .inTable('Person');
      table.string('name');
      table.string('species');
    })
    .createTable('Person_Movie', table => {
      table.increments('id').primary();
      table
        .integer('personId')
        .unsigned()
        .references('id')
        .inTable('Person')
        .onDelete('CASCADE');
      table
        .integer('movieId')
        .unsigned()
        .references('id')
        .inTable('Movie')
        .onDelete('CASCADE');
    });
}

main()
  .then(() => {
    console.log('success')
    return knex.destroy()
  })
  .catch(err => {
    console.error(err)
    return knex.destroy()
  });

[koskimas]

Thanks for the reproduction!

Currently objection doesn't handle joined tables' json attributes. This is because objection allows all kinds of joins to be built. You can join subqueries, raw expressions, subqueries with nested subqueries with raw expression with aliased column names; anything you can think of. There's no way to know which property comes from which table without implementing a full cross-db SQL parser, which obviously isn't feasible.

So full support, which works in all cases, is not possible to implement. We could implement a subset of this feature, that only works with *joinRelation, in which case we can be fairly certain which tables (and thus models) are joined. It's still possible to add modifiers that give aliases for the columns, which will screw up the conversion or make implmenting this feature much harder.

If I implement this so that it only works for non-modified *joinRelation, I will get a bunch of issues complaining that this and that case doesn't work and those are bugs, not missing features :smile:

I don't know which is better:

1. Adding a partially working "buggy" feature
2. Not adding the feature at all

I usually go with the second one.

You should also note that this is not a problem with "relations", but joins specifically. If you eager load relations, you do get the json columns as objects.

[jballanger]

Thanks for your answer!

Alright, I understand why my case was not working as I expected now 😄
I'll try to do something with the eager methods then, thanks!

[koskimas]

@jballanger I'm reopening this because this has come up before and we probably need to consider adding this feature, even if it works only partially. Let's see if this collects some opinions or votes.

[evandeininger]

It would be great to have this to make sure relations are required before an insert, currently I have to make relations non-required but I'd like to enforce their relations. Thanks @koskimas for working on this so much by the way, it's a huge time saver and seems to get better all the time.