Objection.js: Strange behavior on insert with returning(*) and case conversions

[mitchellporter]

I've been experiencing some strange behavior when inserting to postgres and performing case conversions going to and from the database.

Please see the test project I have put together: https://github.com/mitchellporter/objectionjs-insert

I have two tests in the linked project. Here is the first one:

it.only('should save to the database BUT returns duplicate snake_case keys for some reason', function (done) {

      JiraAgileBoardVersion
        .query()
        .insert(validJson)
        .returning('*')
        .then(item => {
          console.log('item: ' + util.inspect(item));
          done();
        })
        .catch(err => {
          console.log('error: ' + err);
          done(err);
        });

});

When I run the above code, the insert is successful, but when I log item it has duplicate keys. It will have some snake_case versions of a few keys, and then also have camelCase versions of those same keys. What's strange is when I perform my camel case conversion in $parseDatabaseJson and log the results, everything looks perfect, but then right after that $afterInsert fires and the json now has the duplicate keys.

Here is an example of what item logs as with the duplicate keys. You will notice that there are BOTH snake case and camel case fields for jira_id, board_id, and jira_project_id, when the expected result is to only have camel case fields:

{
  self: 'http://www.example.com/jira/version/10000',
  jira_id: 10000,
  board_id: 1,
  name: 'Version 1',
  description: 'A first version',
  archived: false,
  released: true,
  releaseDate: '2015-04-20T01:02:00.000+10:00',
  jira_project_id: 10000,
  id: 9,
  createdAt: 2017-07-07T01:09:07.991Z,
  updatedAt: 2017-07-07T01:09:07.991Z,
  jiraId: 10000,
  boardId: 1,
  jiraProjectId: 10000,
  rawJson: null 
}

You can see all of this by setting up the project and running the tests, I'm logging everything with before and after json results in all of the Model methods.

If you look at the second test I wrote, you'll see that instead of using returning(*), I'm using the less efficient way and performing a second query for the new instance that I just finished inserting. When I log the fetched item in this case the duplicate keys are not there and everything works perfectly.

The only way I've found to fix the first test that uses returning(*) is by manually deleting the unwanted duplicate snake case fields in $afterInsert, but this feels gross and like it's not actually solving the problem.

[mitchellporter]

Also I did notice with a quick search prior to posting this that it looks like someone else was experiencing the same thing: https://github.com/Vincit/objection.js/issues/200

After looking through that, I'm either not understanding the problem/solution, it's slightly different, or the same but not actually being solved.

Also, please let me know if there's anything I can do in the future to make test projects easier to setup and use. I want to make it as easy as possible for you to take a look at this.

As always thanks for the help.

[koskimas]

Why are you passing some snake cased and some camel cased properties to the insert method? Also why do you set snake cased properties in $parseJson? $parseJson is called when a model instance is created from a non-db object. I'm pretty confused about what you are trying to camelize and when...

When you do the insert, you get back the snake cased properties you pass in. your conversions work in a way that the keys are only camelized when the object comes from the database.

[koskimas]

Your json schema also contains snake cased properties. Are you sending and receiving snake cased json from your API? Do you only want to use camel case in in code but snake case everywhere else? In that case you also need to run camelizeKeys and decamelizeKeys in $parseJson and $formatJson respectively. Remember to call the super implementation from all hooks.

[koskimas]

Something like this:

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

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['jiraId', 'boardId'],
      properties: {
        self: { type: 'string' },
        jiraId: { type: 'integer' },
        boardId: { type: 'integer' },
        jiraProjectId: { type: 'integer' },
        name: { type: 'string' },
        description: { type: 'string' },
        archived: { type: 'boolean' },
        released: { type: 'boolean' },
        release_date: { type: 'string' },
        rawJson: { type: 'object' }
      }
    };
  }

  $parseDatabaseJson(json) {
    return super.$parseDatabaseJson(camelizeKeys(json));
  }

  $formatDatabaseJson(json) {
    return decamelizeKeys(super.$formatDatabaseJson(json));
  }

  $parseJson(json, opt) {
    if (json.id) {
      json.jira_id = json.id;
      delete json.id;
    }

    if (json.projectId) {
      json.jira_project_id = json.projectId;
      delete json.projectId;
    }

    return super.$parseJson(camelizeKeys(json), opt);
  }

  $formatJson(json) {
    return decamelizeKeys(super.$formatJson(json));
  }
}

[mitchellporter]

I now have it working thanks to your code sample and details thanks.

Your json schema also contains snake cased properties. Are you sending and receiving snake cased json from your API? Do you only want to use camel case in in code but snake case everywhere else? In that case you also need to run camelizeKeys and decamelizeKeys in $parseJson and $formatJson respectively.

I would like to only use snake case for the column names in the database. In our application code, and in all of our json responses to API clients, I would like to use camel case. Sometimes when we receive json payloads from third party services like jira for example, the payload can use camel case or snake case. We don't have control over it.

Most of our models are being created directly from json that's coming from these third party services. Am I correct that if the jira json payload uses camel case, that the model's jsonSchema property should implement camel case as well? This means that I could pass jsonSchema validation, but still fail to save to the database if I don't convert the camel case to snake case to match the db column names.

Does all of that sound right?

[mitchellporter]

Also, if you wouldn't mind offering general advice, have you ever seen a project where snake case is used as the database naming convention, but camel case is used in application code, or does this seem strange to you? I've always just used snake case everywhere for my REST API's, but the team I currently work with prefers to use snake case for db naming and camel case everywhere else, which is why all of these conversions are necessary.

Thanks.

[koskimas]

• $parseDatabaseJson is only called when parsing database rows into model instances. This method is called as the very first thing.
• $formatDatabaseJson is only called when converting model instances to database rows. This method is called as the very last thing.
• $parseJson is called whenever a model instance is created (except when creating one from a database row). This is called before validation. Objects passed to insert, update, patch etc. are always converted into model instances first and therefore $parseJson gets called.
• $formatJson is called whenever a model is converted into JSON using toJSON() or $toJson. Note that JSON.stringify automatically calls toJSON and for example res.send(model) (express.js) will call JSON.stringify for model.

So yes, if you insert an object with snake_cased properties, you need to either have a conversion in $parseJson or have your jsonSchema properties also in snake_case.

Using snake_cased column names is very common, but I don't know why... There is no good reason to do that and it only causes problems and confusion. Off course sometimes the database already exists and developers don't have control over this, but otherwise I would simply use the same casing in database and code.

[mitchellporter]

@koskimas Thank you for such a detailed reply. I fully understand everything now. Cheers! 🍻