ORM - rejection error: column "id" does not exist

Question

I use PostgreSQL and objection.js in Node.js back-end for ORM. I have only two simple tables with one-to-one relationship. I am not able to insert new record into a table.

I have a simple database schema with employee and salary table:

CREATE TABLE employee (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR
);

INSERT INTO employee (employee_name) VALUES ('james');

CREATE TABLE salary (
  salary_id SERIAL PRIMARY KEY,
  employee_id SERIAL UNIQUE,
  FOREIGN KEY (employee_id) REFERENCES employee (employee_id),
  amount integer
);

If I want to create new salary record by objection.js:

Salary.query()
          .insert({ employee_id: 1, amount: 10 })
          .then((data) => {
            console.log(data);
          })
          .catch((err) => {
            throw err;
          });

I get error:

Unhandled rejection error: column "id" does not exist
at Connection.parseE (./node_modules/pg/lib/connection.js:546:11)
at Connection.parseMessage (./node_modules/pg/lib/connection.js:371:19)
at TLSSocket.<anonymous> (./node_modules/pg/lib/connection.js:114:22)
at emitOne (events.js:115:13)
at TLSSocket.emit (events.js:210:7)
at addChunk (_stream_readable.js:252:12)
at readableAddChunk (_stream_readable.js:239:11)
at TLSSocket.Readable.push (_stream_readable.js:197:10)
at TLSWrap.onread (net.js:589:20)

testSalary.js

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

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

  static get columnNameMappers() {
    return snakeCaseMappers();
  }

  static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        salary_id: { type: 'integer' },
        employee_id: { type: 'integer' },
        amount: { type: 'integer' },
      },
    };
  }

  static get relationMappings() {
    return {
      employee: {
        relation: Model.BelongsToOneRelation,
        modelClass: `${__dirname}/testEmployee`,
        join: {
          from: 'salary.employee_id',
          to: 'employee.employee_id',
        },
      },
    };
  }
}

module.exports = Salary;

testEmployee.js

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

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

  static get columnNameMappers() {
    return snakeCaseMappers();
  }

  static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        employee_id: { type: 'integer' },
        employee_name: { type: 'string' },
      },
    };
  }

  static get relationMappings() {
    return {
      salary: {
        relation: Model.HasManyRelation,
        modelClass: `${__dirname}/testSalary`,
        join: {
          from: 'employee.employee_id',
          to: 'salary.employee_id',
        },
      },
    };
  }
}

module.exports = Employee;

Answer 1

Objection needs to be told what the id column is by defining idColumn on your Model classes.

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

  static get idColumn() {
    return 'salary_id';
  }
  // etc
}

Using migrations to create the tables will have made this work by creating the tables with their id column being named id, but you can use objection without migrations if you define the idColumn.