CODEWITHSUNDEEP
node.jspostgresqlormnode-postgresobjection.js
Matt
Matt

Reputation: 8942

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

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;

Upvotes: 2

Views: 3621

Answers (2)

lurgburg
lurgburg

Reputation: 101

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.

Upvotes: 10

Sam H.
Sam H.

Reputation: 4349

My guess is this has something to do with the reliance on Knex under the hood. In the docs, there is a code snippet that suggests that tables be created via Knex migrations. Maybe doing that will fix your problem, as Knex probably creates an id column that Objection expects to exist.

Upvotes: 0

Related Questions