goza Posts - Answer Overflow

goza

•Created by goza on 6/25/2024 in #help

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.

2 replies

Gaming

Programming