Default value is being inserted into the MySQL Auto Increment Column for insert statement.
Below is the code to reproduce the issue.
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.
Expected behavior
insert into user (id, name) values (null, ?) # MySQL
What version of
drizzle-orm
are you using?0.31.2
What version of
drizzle-kit
are you using?0.22.7
Describe the Bug
Default value is being inserted into the MySQL Auto Increment Column for insert statement. Below is the code to reproduce the issue.
The output on the console is as follows.
Why insert statement set
default
for ai columnid
? Anyone else encountered this issue or mysql schema defination was wrong? Any comments will be gratefully appreciated, thanks in advance.Expected behavior
insert into
user
(id
,name
) values (null, ?) # MySQLor
insert into
user
(name
) values (?) # MySQLEnvironment & setup
No response