Issue
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?
Solution
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.
Answered By – Bill Karwin
Answer Checked By – Mary Flores (AngularFixing Volunteer)