Knex : universal way to get the last inserted id

user_80911 0
Viewed 214

I'm using Knex, because I'm working on an application that I would like to use with multiple database servers, currently Sqlite3, Postgres and MySQL.

I'm realizing that this might be more difficult that I expected.

On MySQL, it appears that this syntax will return an array with an id:

knex('table').insert({ field: 'value'}, 'id');

On postgres I need something like this:

knex('table').insert({ field: 'value'}, 'id').returning(['id']);

In each case, the structure they return is different. The latter doesn't break MySQL, but on SQlite it will throw a fatal error.

The concept of 'insert a record, get an id' seems to exist everywhere though. What am I missing in Knex that lets me write this once and use everywhere?

knex.js node.js postgresql sqlite mysql
1 Answers

Way back in 2007, I implemented the database access class for a PHP framework. It was to support MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, and IBM DB2.

When it came time to support auto-incremented columns, I discovered that all of these implement that feature differently. Some have SERIAL, some have AUTO-INCREMENT (or AUTOINCREMENT), some have SEQUENCE, some have GENERATED, some support multiple solutions.

The solution was to not try to write one implementation that worked with all of them. I wrote classes using the Adapter Pattern, one for each brand of SQL database, so I could implement each adapter class tailored to the features supported by the respective database. The adapter satisfied an interface that I defined in my framework, to allow the primary key column to be defined and the last inserted id to be fetched in a consistent manner. But the internal implementation varied.

This was the only sane way to develop that code, in my opinion. When it comes to variations of SQL implementations, it's a fallacy that one can develop "portable" code that works on multiple brands.

Bill_Karwin_208600
Related