Drizzle Team•17mo ago

Is there something wrong with my schema?

fyi: i'm building an authenticated todo app

19 Replies

JaxwnOP•17mo ago

this is want it said when i tried to run drizzle-kit studio

JaxwnOP•17mo ago

Updated: i change my created_at and updated_at to

    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),

    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),

and it worked

JaxwnOP•17mo ago

then i drop title column, and it suddenly not working again

JaxwnOP•17mo ago

Angelelz•17mo ago

For timestamp you should be able to use:

...
    createdAt: timestamp('created_at').notNull().default(sql`current_timestamp`),
    updatedAt: timestamp('updated_at').notNull().default(sql`current_timestamp`).onUpdateNow(),

...
    createdAt: timestamp('created_at').notNull().default(sql`current_timestamp`),
    updatedAt: timestamp('updated_at').notNull().default(sql`current_timestamp`).onUpdateNow(),

quasar_here•17mo ago

I'm using postgres so I have an error: Property onUpdateNow does not exist on type PgTimestampBuilder

Angelelz•17mo ago

How about

.default(sql`current_timestamp on update current_timestamp`)

.default(sql`current_timestamp on update current_timestamp`)

That's a long shot it may or may not work.

quasar_here•17mo ago

It did not worked, i'm struggling for hours for such a simple feature lmao

Running migrations...
error: syntax error at or near "on"

Running migrations...
error: syntax error at or near "on"

Angelelz•17mo ago

I'll suggest you edit it manually for now. Can you paste the result of the generate command? I'll help you edit it.

quasar_here•17mo ago

Sure, I appreciate it:

CREATE TABLE IF NOT EXISTS "users" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "name" text NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp DEFAULT now() NOT NULL,
);

CREATE TABLE IF NOT EXISTS "users" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "name" text NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp DEFAULT now() NOT NULL,
);

Right now I created a trigger:

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And I'm calling after the users table creation with:

CREATE TRIGGER users_update_trigger
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER users_update_trigger
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

Can it get better? Btw if this could be done in typescript as a function or something I think it will be better

JaxwnOP•17mo ago

is mysql and planetscale use the same syntax?

Andrii Sherman•17mo ago

Would be great if you can create an issue on GH to support onUpdate functions I'll gladly take thar into development

Angelelz•17mo ago

Andrew, I'm actually working on that for my next PR. It's very related to issues #596 and #921 that I'm working on @quasar_here your migration should look like this:

CREATE TABLE IF NOT EXISTS "users" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "name" text NOT NULL,
    "created_at" timestamp DEFAULT current_timestamp NOT NULL,
    "updated_at" timestamp DEFAULT current_timestamp ON UPDATE current_timestamp NOT NULL,
);

CREATE TABLE IF NOT EXISTS "users" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "name" text NOT NULL,
    "created_at" timestamp DEFAULT current_timestamp NOT NULL,
    "updated_at" timestamp DEFAULT current_timestamp ON UPDATE current_timestamp NOT NULL,
);

quasar_here•17mo ago

@angelelz I appreciate your help very much and congratulations for becoming a Drizzle Advocate I got a error when trying to run this migration and according to ChatGPT (I'm not proficient in databases myself), Postgres doesn't support update on current_timestamp that's why it recommended to use triggers instead

Angelelz•17mo ago

I though you were using mysql. You are importing from mysql in you original post. Your tables are myqslTables

quasar_here•17mo ago

Actually I'm a guy who took the OP post to solve my issue because I didn't want to create another post

Angelelz•17mo ago

My bad. My answer is not going to work for you. I'm not super familiar with pg.

rphlmr ⚡•17mo ago

Feel free to open your own 😉 with all you have so I can look at your issue (PG user here).

JaxwnOP•17mo ago

never open github issue before, don't know what to write in the desc lol, but here you go https://github.com/drizzle-team/drizzle-orm/issues/1106#issue-1864487095

GitHub

[FEATURE]: Support onUpdate Function (Mysql) · Issue #1106 · drizzl...

Describe what you want my current schema right now look like this: updated_at: timestamp('updated_at') .notNull() .default(sqlcurrent_timestamp) .onUpdateNow(), not sure if it the right w...

Gaming

Programming

Is there something wrong with my schema?