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.
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)
);
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(sqlCURRENT_TIMESTAMP
) .onUpdateNow(), is_deleted: boolean("is_deleted").default(false).notNull(), deleted_at: timestamp("deleted_at") .default(sqlnull
) .$type<Date | null>(), //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, CONSTRAINTusers_id
PRIMARY KEY(id
), CONSTRAINTemail_idx
UNIQUE(email
), CONSTRAINTusername_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 SQLusername
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, CONSTRAINTusers_id
PRIMARY KEY(id
), CONSTRAINTemail_idx
UNIQUE(email
), CONSTRAINTusername_idx
UNIQUE(username
) );