Drizzle TeamDT
Drizzle Team•2y ago
goza

Duplicate entry for auto increment column when execute insert statement

Default value is being inserted into the MySQL Auto Increment Column for insert statement.
Below is the code to reproduce the issue.

dependencies as below
"better-sqlite3": "^11.0.0",
"drizzle-orm": "^0.30.10",
"mysql2": "^3.10.0"


import Database from 'better-sqlite3';
import { drizzle as sqlite_drizzle } from 'drizzle-orm/better-sqlite3';
import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";
import { drizzle } from "drizzle-orm/mysql2";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import mysql from "mysql2/promise";

const sqlite3_users = sqliteTable('user', {
  id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  name: text('name')
});

const mysql_users = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 30 }).default('').notNull(),
});

async function main() {
    const connection = await mysql.createConnection({
        uri:"mysql://admin:admin@localhost:3306/drizzle_sample",
    });
  const db = drizzle(connection);
  const query = await db.insert(mysql_users).values({
    name: "Hello, World!",
  }).toSQL()

  console.log(query.sql);
}
async function sqlite_run() {
const sqlite = new Database('sqlite.db');
const db = sqlite_drizzle(sqlite);
const query = await db.insert(sqlite3_users).values({
  name: "Hello, World!",
}).toSQL()

console.log(query.sql);
}

async function run() {
  await main();
  await sqlite_run();
}

run();

The output on the console is as follows.

insert into `user` (`id`, `name`) values (default, ?) # MySQL
insert into "user" ("id", "name") values (null, ?) # sqlite3


Why insert statement set 
default
 for ai column 
id
 ?
Anyone else encountered this issue or mysql schema defination was wrong?
Any comments will be gratefully appreciated, thanks in advance.
Was this page helpful?