Objection.js: Timestamps once again

Created on 6 Dec 2017 · 15Comments · Source: Vincit/objection.js

I have just started using objection.js and I love it!

However, I have a problem with timestamps. It is not about how to create them. I followed the docs and looked up examples and they are setup properly.

The problem is that I want those fields to be set only by the db, and from none else.

Instead, after the setup I did, I do

People.query().insert({updated_at: '2017-01-01'});

I am able to explicitly set the value. Is there a way to avoid this?

My people.js model file:

'use strict';

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

class People extends Model {
  // Table name is the only required property.
  static get tableName() {
    return 'people';
  }

  $beforeInsert() {
    this.created_at = new Date().toISOString();
  }

  $beforeUpdate() {
    this.updated_at = new Date().toISOString();
  }
  static get jsonSchema() {
    return {
      type: 'object',
      required: ['first_name', 'last_name'],

      properties: {
        id: { type: 'integer' },
        first_name: { type: 'string', minLength: 1, maxLength: 255 },
        last_name: { type: 'string', minLength: 1, maxLength: 255 },
        bio: { type: 'string' }
      }
    }
  };
}
module.exports = People;

and my migration file for the people table:

'use strict'

exports.up = function (knex, Promise) {
  return knex.schema
    .createTable('people', function (table) {
      table.increments('id').primary();
      table.string('first_name').notNullable();
      table.string('last_name').notNullable();
      table.text('bio');
      table.timestamps(true, true);
    });
};

exports.down = function (knex, Promise) {
  return knex.schema.dropTable('people');
};

Source

officer-rosmarino

Most helpful comment

Ah, it didn't work because in my BaseModel:

class BaseModel extends Model {
  $beforeInsert() {
    if (this.timestamps) { // <-- Should actually be `this.constructor.timestamps`
      const timestamp = new Date().toISOString()
      this.createdAt = timestamp
      this.updatedAt = timestamp
    }
  }

  $beforeUpdate() {
    if (this.timestamps) { // <-- Should actually be `this.constructor.timestamps`
      this.updatedAt = new Date().toISOString()
    }
  }
}

...and in my actual model:

class MyModel extends BaseModel {
  static get timestamps() {
    return true
  }
}

...in case anyone else finds their way to this issue.

heisian on 14 May 2018

👍5

All 15 comments

You can do this:

  $beforeInsert() {
    this.created_at = new Date().toISOString();
    delete this.updated_at;
  }

  $beforeUpdate() {
    this.updated_at = new Date().toISOString();
    delete this.created_at;
  }

or is there something that prevents you from doing that?

koskimas on 6 Dec 2017

No, I didn't think about that. I'll give it a try and if it works fine i'll close the issue.

Thanks!

officer-rosmarino on 6 Dec 2017

@koskimas ok, so, this works but I think it's kind of a "trick" because it relies on the webserver to set the datetime, when I think that it should be the db itself. Don't you think?

officer-rosmarino on 6 Dec 2017

That's easily doable using DEFAULT keyword in the migration for created_at . Something like this:

table.timestamp('created_at').defaultTo(knex.raw('now()'));

You need to check the correct syntax for now() for your database engine. Autmatic updated_at can be achieved using triggers. I don't remember the correct syntax off the top of my head, but google is your friend. Then if you want to make absolutely sure the code never sets those columns you can override $formatDatabaseJson:

$formatDatabaseJson(json) {
  json = super.$formatDatabaseJson(json);
  delete json.created_at;
  delete json.updated_at;
  return json;
}

In any case, you need to do this in the database and you cannot do it using objection. Objection has no magic in it that automatically generates anything in the database. It simply uses the schema, but never modifies it.

koskimas on 6 Dec 2017

👍2

Something like this would work on postgres:

CREATE OR REPLACE FUNCTION update_updated_at_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW; 
END;
$$ language 'plpgsql';

CREATE TRIGGER update_people_updated_at BEFORE UPDATE ON people FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();

You need to use knex.raw to create those functions and triggers in a migration file.

koskimas on 6 Dec 2017

@koskimas Thank you very much.

I have just a question given that you initially proposed a solution which dealt with timestamps in the webapp itself.
From a design point of view, do you think that dealing with created_at and updated_at in the webapp instead of the db is correct?

officer-rosmarino on 6 Dec 2017

I think it's better to do it in the database since you can be absolutely sure the value is always updated, but doing it using triggers is a bit clumsy since you need to write raw sql and the code is different for each database. That's why the recipe is to do it in the webapp. There's nothing wrong in that either, and it works just fine. It only fails if you go around objection models and use raw SQL or knex directly.

koskimas on 6 Dec 2017

ok. thank you very much, i'm gonna close the issue

officer-rosmarino on 6 Dec 2017

@koskimas How would I get knexSnakeCaseMappings to work in the events example for timestamps?

For example this won't work:

module.exports = class SomeModel extends BaseModel {
  $beforeInsert() {
    const timestamp = new Date().toISOString()
    this.createdAt = timestamp
    this.updatedAt = timestamp
  }

  $beforeUpdate() {
    this.updatedAt = new Date().toISOString()
  }
}

thanks

heisian on 14 May 2018

👍1

It should work

koskimas on 14 May 2018

hmm.. ok here is my full chain:

./knexfile.js

const { knexSnakeCaseMappers } = require('objection')
require('dotenv').config({ path: './.env' })

console.log('knex connecting to', process.env.PSQL_HOST) // eslint-disable-line

module.exports = Object.assign(
  {
    client: 'pg',
    connection: {
      host: process.env.PSQL_HOST,
      database: process.env.PSQL_DATABASE,
      user: process.env.PSQL_USERNAME,
      password: process.env.PSQL_PASSWORD,
    },
    migrations: {
      stub: './knex-stub.js',
    },
  },
  knexSnakeCaseMappers()
)

./db/models/BaseModel.js

const Knex = require('knex')
const objection = require('objection')
const config = require('../../knexfile')

/**
 * Extend objection Model to include a property for
 * defining a PostgreSQL schema and using it in queries.
 */
class ExtendedQueryBuilder extends objection.QueryBuilder {
  constructor(ModelClass) {
    super(ModelClass)
    if (ModelClass.schemaName) {
      this.withSchema(ModelClass.schemaName)
    }
  }
}

objection.Model.QueryBuilder = ExtendedQueryBuilder
objection.Model.RelatedQueryBuilder = ExtendedQueryBuilder

objection.Model.knex(Knex(config))

module.exports = class BaseModel extends objection.Model {
  static get modelPaths() {
    return [__dirname]
  }

  $beforeInsert() {
    if (this.timestamps) {
      const timestamp = new Date().toISOString()
      this.createdAt = timestamp
      this.updatedAt = timestamp
    }
  }

  $beforeUpdate() {
    if (this.timestamps) {
      this.updatedAt = new Date().toISOString()
    }
  }
}

./db/models/User.js

const BaseModel = require('./BaseModel')

module.exports = class User extends BaseModel {
  static get schemaName() {
    return 'auth'
  }
  static get tableName() {
    return 'users'
  }
  static get timestamps() {
    return true
  }

  static get relationMappings() {
    return {
      emails: {
        relation: BaseModel.HasManyRelation,
        modelClass: 'Email',
        join: {
          from: 'users.id',
          to: 'emails.userId',
        },
      },
      groups: {
        relation: BaseModel.ManyToManyRelation,
        modelClass: 'Group',
        join: {
          from: 'users.id',
          through: {
            from: 'users_groups.userId',
            to: 'users_groups.groupId',
            extra: ['role'],
          },
          to: 'groups.id',
        },
      },
    }
  }
}

./db/models/Email.js

const BaseModel = require('./BaseModel')

module.exports = class Email extends BaseModel {
  static get schemaName() {
    return 'auth'
  }
  static get tableName() {
    return 'emails'
  }
  static get timestamps() {
    return true
  }

  static get relationMappings() {
    return {
      user: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: 'User',
        join: {
          from: 'emails.userId',
          to: 'users.id',
        },
      },
    }
  }
}

./db/models/Group.js

const BaseModel = require('./BaseModel')

module.exports = class Group extends BaseModel {
  static get schemaName() {
    return 'auth'
  }
  static get tableName() {
    return 'groups'
  }
  static get timestamps() {
    return true
  }

  static get relationMappings() {
    return {
      users: {
        relation: BaseModel.ManyToManyRelation,
        modelClass: 'User',
        join: {
          from: 'groups.id',
          through: {
            from: 'users_groups.groupId',
            to: 'users_groups.userId',
            extra: ['role'],
          },
          to: 'users.id',
        },
      },
    }
  }
}

Running a query:

// Graph insert:
(async () => {
  console.log(
    await User.query().insertGraph({
      leadId: 'asdf',
      emails: [{
        email: '[email protected]',
        verified: true
      }],
      groups: [{
        name: 'Ethos',
        type: 'other',
        role: 'admin'
      }],
   })
  )
})()

Result:

(node:14482) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): error: insert into "auth"."users" ("lead_id") values ($1) returning "id" - null value in column "created_at" violates not-null constraint
(node:14482) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Important part:
null value in column "created_at" violates not-null constraint

heisian on 14 May 2018

Nevermind, I got it to work, I have no idea why it didn't, probably because I was using the node REPL and didn't drop the require cache before trying the query again.

Thanks!!!

heisian on 14 May 2018

Ah, it didn't work because in my BaseModel:

class BaseModel extends Model {
  $beforeInsert() {
    if (this.timestamps) { // <-- Should actually be `this.constructor.timestamps`
      const timestamp = new Date().toISOString()
      this.createdAt = timestamp
      this.updatedAt = timestamp
    }
  }

  $beforeUpdate() {
    if (this.timestamps) { // <-- Should actually be `this.constructor.timestamps`
      this.updatedAt = new Date().toISOString()
    }
  }
}

...and in my actual model:

class MyModel extends BaseModel {
  static get timestamps() {
    return true
  }
}

...in case anyone else finds their way to this issue.

heisian on 14 May 2018

👍5

how about

class YourModel extends ObjectionModel {
  static get jsonSchema() {
    return {
      type: `object`,
      additionalProperties: false, // will throw error if there is additional properties
      properties: {
        ...your properties schema,
      }
    }
  }
}

and if you want to not throw error but remove the additional properties instead, use this

static createValidator() {
    return new AjvValidator({
      onCreateAjv: ajv => {}, // if want to custom something
      options: {
        useDefaults: true,
        removeAdditional: true, // will remove additional properties if you set schema additionalProperties: fase
        allErrors: true,
        validateSchema: false,
        ownProperties: true,
        v5: true,
      },
    });
  }

https://vincit.github.io/objection.js/#ajvvalidator
https://github.com/epoberezkin/ajv#filtering-data