Handling INSERT .returning() Across Dialects in Knex.js

Answer the question first

Knex does not expose a single, dialect‑agnostic API for retrieving inserted IDs. The .returning() clause is native only to PostgreSQL, partially emulated on SQLite (≥3.35.0) and unsupported on MySQL. The most DRY approach is to wrap the insert in a helper that detects the client and normalizes the result to a consistent shape (e.g. an array of objects with an id property).

Why the behavior differs

• PostgreSQL – RETURNING returns an array of row objects.
• MySQL – no RETURNING; Knex returns an array of inserted IDs (often a single number for a single row).
• SQLite – native RETURNING available from 3.35.0; otherwise Knex falls back to lastInsertRowid() and returns a single ID.

Recommended DRY helper

async function insertAndReturnId(knex, table, data) {
  const client = knex.client.config.client;
  // 1️⃣ PostgreSQL or modern SQLite
  if (client === 'pg' || client === 'sqlite3' || client === 'better-sqlite3') {
    const [row] = await knex(table).insert(data).returning('id');
    return { id: row.id };
  }
  // 2️⃣ MySQL / MariaDB
  if (client === 'mysql' || client === 'mysql2') {
    const ids = await knex(table).insert(data); // returns array of ids
    return { id: Array.isArray(ids) ? ids[0] : ids };
  }
  throw new Error(`Unsupported client: ${client}`);
}

Call it like:

const result = await insertAndReturnId(knex, 'users', { name: 'bob' });
console.log(result.id); // 1

Knex built‑in fallback?

No, Knex does not provide a unified normalizer for .returning(). The library intentionally leaves the return shape to the underlying driver so that developers can access raw database results when needed.

Automatic client switch

Knex cannot be configured to automatically substitute LAST_INSERT_ID() for .returning(). The logic must live in your application code, as shown above.

Verification checklist

1. Confirm Knex version: npm list knex – 2.x+ is recommended for consistent behaviour.
2. Check client string: knex.client.config.client.
3. Test a minimal insert in each dialect: knex('t').insert({a:1}).returning('id').then(console.log).
4. For SQLite, verify version: knex.raw('select sqlite_version()').then(console.log) – must be ≥3.35.0 to use native RETURNING.
5. For MySQL, ensure you’re using mysql2 or mysql driver; both expose LAST_INSERT_ID() automatically.

Missing diagnostic detail

If you encounter unexpected results, let us know your Knex version and the database driver you’re using (e.g., mysql2, sqlite3, better-sqlite3). This information can pinpoint whether a driver‑specific quirk or a Knex version change is causing the discrepancy.