Drizzle Team•5mo ago

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();

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

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.

1 Reply

gozaOP•5mo ago

ddl for mysql

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Gaming

Programming

Duplicate entry for auto increment column when execute insert statement