How to insert default values to foreign object with relation using Postgresql, Knex.js and Objection.js with HasOneRelation?

Question

I'm setting up simple API using Postgresql, Knex.js and Objection.js. I created User model with "location" property. This "location" property is another table. How I have to insert that user to database with defaults 'city' and 'country' in 'location' property?

I already tried to use 'static get jsonSchema' in model itself and 'allowInsert' method in mutation but when I fetching created that user the 'location' still 'null'.

So, let's say we have migration for users_table:

exports.up = knex =>
  knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table
      .string('email')
      .unique()
      .notNullable();
    table.string('firstName').notNullable();
    table.string('lastName').notNullable();
    table.string('password').notNullable();
  });

exports.down = knex => knex.schema.dropTable('users');

And we have location_table:

exports.up = knex =>
  knex.schema.createTable('locations', table => {
    table.increments('id').primary();
    table.string('country').defaultTo('USA');
    table.string('city').defaultTo('San Francisco');
    table
      .integer('user_id')
      .references('id')
      .inTable('users')
      .onUpdate('CASCADE')
      .onDelete('CASCADE');
  });

exports.down = knex => knex.schema.dropTable('locations');

Here User Model with objection.js:

export default class User extends Model {
  static get tableName() {
    return 'users';
  }

  // wrong probably
  static get jsonSchema() {
    return {
      type: 'object',
      properties: {
        location: {
          type: 'object',
          properties: {
            city: {
              type: 'string',
              default: 'Los Angeles',
            },
            country: {
              type: 'string',
              default: 'USA',
            },
          },
        },
      },
    };
  }

  fullName() {
    return `${this.firstName} ${this.lastName}`;
  }

  static get relationMappings() {
    return {
      location: {
        relation: Model.HasOneRelation,
        modelClass: Location,
        join: {
          from: 'users.id',
          to: 'locations.user_id',
        },
      },
    };
  }
}

And Location model:

export default class Location extends Model {
  static get tableName() {
    return 'locations';
  }

  static get relationMappings() {
    return {
      user: {
        relation: Model.BelongsToOneRelation,
        modelClass: `${__dirname}/User`,
        join: {
          from: 'locations.user_id',
          to: 'users.id',
        },
      },
    };
  }
}

My mutation when I creating new User:

// ...
const payload = {
      email,
      firstName,
      lastName,
      password: hash,
    };

    const newUser = await User.query()
      .allowInsert('[user, location]')
      .insertAndFetch(payload);
// ...

And in the end query:

// ...
User.query()
    .eager('location')
    .findOne({ email });
// ...

From query of user I expect to see the object with locatoin propety with my defaults. Example:

{
  email: 'jacklondon@gmail.com',
  firstName: 'Jack',
  fullName: 'Jack London',
  id: '1',
  lastName: 'London',
  location: {
    city: 'San Francisco',
    country: 'USA',
  },
  userName: 'jacklondon1',
  __typename: 'User',
}

So, where I made mistake with such simple operation?

Answer 1

One to One Solution

I think part of the issue is that your allow insert included the user object. You shouldn't include the user in the allow insert because it's implicit since you're on the User model (example). The other issue you had was that you were trying to use insertAndFetch method. insertAndFetch cannot be used when inserting a graph. You need to use the insertGraph method to insert a graph (docs). Since you are using Postgres, you can chain the returning(*) method and it will return the result without additional queries (example). Finally, since you're asking for a one-to-one relation, you have to specify a city and country every time. Objection will not know it needs to create a new row without specifying it (even if you have configured the database to have default values). The way I accomplished this for you was to use default parameters.

const createNewuser = async (email, firstName, lastName, hash, city = 'Los Angeles', country = 'USA') => {   

  const newUser = await User
    .query()
    .insertGraph({
      email,
      firstName,
      lastName,
      password: hash,
      location: {
        city: city,
        country: country
      }
    })
    .returning('*');

   return newUser;
}

Additional Thought to Ponder

I'm not sure why you have a one-to-one relationship between user and location. Why not just make city, state, and country part of the user's table since it's already one to one?

However, what I think you're really going for is a one-to-many relationship between user and location. One location has multiple users. This would put you into 3rd normal form by reducing the amount of duplicate data in your database since you wouldn't duplicate a city/country for each user in an identical location.

If you're just learning objection, I would recommend reading up on graphs in the documentation.