drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: explicitly mark a models' column definition as NULL with a ".null()" chain function
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Describe what you want
# Request:
Please add the ability to mark a specific column in a model definition file to generate the correct sql containing "NULL" after the datatype of the column you want nullable.
# Issue:
When using certain datatype's like timestamp and null, and even setting the $type to Date | null ( which solves IDE related warnings but does not do anything for sql code, hence generates incorrect sql code )
# Fix:
A ".null()" chain call to be used when defining a model to explicitly mark a column as having to generate the NULL keyword after the columns datatype.
# Current result and model setup:
Given the following model definition:
export const users = mysqlTable(
"users",
{
id: bigint("id", {
mode: "number",
unsigned: true,
})
.primaryKey()
.autoincrement(),
//Timestamps
created_at: timestamp("created_at")
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
modified_at: timestamp("modified_at")
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.onUpdateNow(),
is_deleted: boolean("is_deleted").default(false).notNull(),
deleted_at: timestamp("deleted_at")
.default(sql`null`)
.$type(),
//Fields
username: varchar("username", { length: 32 }).notNull(),
email: varchar("email", { length: 100 }).notNull(),
password: varchar("password", { length: 256 }).notNull(),
first_name: varchar("first_name", { length: 50 }).notNull(),
last_name: varchar("last_name", { length: 50 }).notNull(),
age: int("age").default(0).notNull(),
},
(users) => ({
emailIndex: uniqueIndex("email_idx").on(users.email),
usernameIndex: uniqueIndex("username_idx").on(users.username),
})
);
generates the following sql which is incorrect due to "deleted_at" datatype not containing the nececarry "null" keyword after its datatype, here is generated sql for above definition which is incorrect at the "deleted_at" column(s):
CREATE TABLE `users` (
`id` bigint unsigned AUTO_INCREMENT NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp DEFAULT NULL, <---- Problem here, in mysql is fine, but in maria, it expects "timestamp null"
`username` varchar(32) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(256) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`age` int NOT NULL DEFAULT 0,
CONSTRAINT `users_id` PRIMARY KEY(`id`),
CONSTRAINT `email_idx` UNIQUE(`email`),
CONSTRAINT `username_idx` UNIQUE(`username`)
);
# Expected generated SQL:
CREATE TABLE `users` (
`id` bigint unsigned AUTO_INCREMENT NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL, <-- explicitly needs the "NULL" keyword after timestamp for valid SQL
`username` varchar(32) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(256) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`age` int NOT NULL DEFAULT 0,
CONSTRAINT `users_id` PRIMARY KEY(`id`),
CONSTRAINT `email_idx` UNIQUE(`email`),
CONSTRAINT `username_idx` UNIQUE(`username`)
);
Contributor guide
Assessment
This issue has not been assessed yet.