m&tra
m&tra
PostsComments
DTDrizzle Team
Created by cvr on 7/4/2023 in #help
How to set a default timestamp_ms for sqlite
@IP @cvr @dandadan I was using IP's solution with a $default arrow function in my schema.ts returning new Date(), but the generated migration files didn't have SQL-defined DEFAULTs. I found a solution that sets the SQL default and works with SQLite, returning the same timestamp format as new Date(): 
created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()
created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()
16 replies
DTDrizzle Team
Created by m&tra on 1/20/2024 in #help
Creating triggers with SQLite
FWIW, I did make a working trigger function using a transaction: 
export async function addUserOrg(email: string) {
    //@ts-expect-error trigger workaround
    await useDB().transaction(async tx => {
        await tx.insert(users).values({ email: email }).run();
        const [newUser] = await tx.select().from(users).where(eq(users.email, email));

        await tx.insert(orgs).values({ name: email }).run();
        const [newOrg] = await tx.select().from(orgs).where(eq(orgs.name, email));

        await tx.insert(user_org).values({ user_id: newUser.id, org_id: newOrg.id, role: 'ADMIN'}).run();

        return newUser;
    }, { behavior: 'immediate' });
}
export async function addUserOrg(email: string) {
    //@ts-expect-error trigger workaround
    await useDB().transaction(async tx => {
        await tx.insert(users).values({ email: email }).run();
        const [newUser] = await tx.select().from(users).where(eq(users.email, email));

        await tx.insert(orgs).values({ name: email }).run();
        const [newOrg] = await tx.select().from(orgs).where(eq(orgs.name, email));

        await tx.insert(user_org).values({ user_id: newUser.id, org_id: newOrg.id, role: 'ADMIN'}).run();

        return newUser;
    }, { behavior: 'immediate' });
}
10 replies
DTDrizzle Team
Created by m&tra on 1/20/2024 in #help
Creating triggers with SQLite
Ty @Angelelz !
10 replies
DTDrizzle Team
Created by m&tra on 1/20/2024 in #help
Creating triggers with SQLite
It works now... my migration script was pointing to the wrong DB this whole time pepeQQ
10 replies
DTDrizzle Team
Created by m&tra on 1/20/2024 in #help
Creating triggers with SQLite
I've tried doing this, but it doesn't seem to work--maybe I'm doing something wrong. This is what I'm adding to the end of my migration file: 
--> statement-breakpoint
CREATE TRIGGER IF NOT EXISTS `new_user_org` AFTER INSERT ON `users`
BEGIN
    INSERT INTO `orgs` (`name`) VALUES (NEW.email);
    INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;
--> statement-breakpoint
CREATE TRIGGER IF NOT EXISTS `new_user_org` AFTER INSERT ON `users`
BEGIN
    INSERT INTO `orgs` (`name`) VALUES (NEW.email);
    INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;
After adding this, I just npm exec drizzle-kit push:sqlite to apply?
10 replies