Ilan Yehezkely Posts - Answer Overflow

Ilan Yehezkely

Explore posts from servers

•Created by Ilan Yehezkely on 9/2/2024 in #help

How to use OVERRIDING SYSTEM VALUE with drizzle?

I want to use the GENERATED ALWAYS AS IDENTITY for identities but also for local dev I need to manually set ids', so how would I use OVERRIDING SYSTEM VALUE clause with drizzle and not raw sql?

1 replies

DTDrizzle Team

•Created by Ilan Yehezkely on 4/12/2024 in #help

Is this the correct way to use With clause (CTEs) to delete a row using only one transaction?

export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
  const userId = neonClient
    .$with('user_id')
    .as(neonClient.select({ id: user.id }).from(user).where(eq(user.publicUserId, publicUserId)))

  const mainAccountId = neonClient
    .$with('account_id')
    .as(
      neonClient
        .select({ id: mainAccount.id })
        .from(mainAccount)
        .where(eq(mainAccount.publicId, publicMainAccountId))
    )

  return neonClient
    .with(userId, mainAccountId)
    .delete(mainAccountUser)
    .where(
      and(eq(mainAccountUser.userId, userId), eq(mainAccountUser.mainAccountId, mainAccountId))
    )
}

I first need to get both the userId and mainAccountId before removing the row in mainAccountUser, is this the best way to do it? Do I have to use sql in order to fully use the CTEs flow?

14 replies

DTDrizzle Team

•Created by Ilan Yehezkely on 3/19/2024 in #help

Neon postgres, drop all fk constraints on every push without any changes

A new db and new migrations, but on the second push and forward drizzle drops and add again all the fk constraints of the schema. I've also made sure all of the fk names are within the 63 characters limit. I'm on: "drizzle-orm": "^0.30.4", "drizzle-kit": "^0.20.14", Example of the push statement: ALTER TABLE "company_users" DROP CONSTRAINT "company_users_company_id_companies_id_fk"; ALTER TABLE "main_account_users" DROP CONSTRAINT "main_account_users_user_id_users_id_fk"; ALTER TABLE "organization_users" DROP CONSTRAINT "organization_users_user_id_users_id_fk"; DO $$ BEGIN ALTER TABLE "company_users" ADD CONSTRAINT "company_users_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "companies"("id") ON DELETE cascade ON UPDATE no action; EXCEPTION WHEN duplicate_object THEN null; END $$; DO $$ BEGIN ALTER TABLE "main_account_users" ADD CONSTRAINT "main_account_users_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action; EXCEPTION WHEN duplicate_object THEN null; END $$; DO $$ BEGIN ALTER TABLE "organization_users" ADD CONSTRAINT "organization_users_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action; EXCEPTION WHEN duplicate_object THEN null; END $$;

3 replies

Gaming

Programming