Drizzle Team•14mo ago

update where multiple conditions

Trying to do something like a: UPDATE table SET values WHERE condition1 AND condition2 other ORMs will all you to pass in an object for conditions and they'll handle it under the hood, does Drizzle have something like this or do I need to do something like:

const query = db
    .update(Table)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(condition1)
    .where(eq(condition2));

const query = db
    .update(Table)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(condition1)
    .where(eq(condition2));

18 Replies

PGTOP•14mo ago

hmm tried to do something like

await Model.update()
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(Table[key], value),
        ),
      ),
    );

await Model.update()
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(Table[key], value),
        ),
      ),
    );

but getting error

PGTOP•14mo ago

Angelelz•14mo ago

That seems to be a type error, you could get around that by doing:

...
      eq(Table[key as keyof typeof Table], value)
...

...
      eq(Table[key as keyof typeof Table], value)
...

PGTOP•14mo ago

hmm I tried that but it didn't work, might have to update in my fn interface too

Angelelz•14mo ago

Yeah, I don't know what Table is

PGTOP•14mo ago

i guess let me do a full copy and paste:

import { and, eq } from 'drizzle-orm';

import { createOrGetDb } from '~/db';
import { NewProjectRender, ProjectRenderTable } from '~/db/schema';

const Table = ProjectRenderTable;

const db = createOrGetDb();

export function update(data: Partial<NewProjectRender>, conditions) {
  const query = db
    .update(Table)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(ProjectRenderTable[key as keyof typeof NewProjectRender], value),
        ),
      ),
    );
  return query;
}

import { and, eq } from 'drizzle-orm';

import { createOrGetDb } from '~/db';
import { NewProjectRender, ProjectRenderTable } from '~/db/schema';

const Table = ProjectRenderTable;

const db = createOrGetDb();

export function update(data: Partial<NewProjectRender>, conditions) {
  const query = db
    .update(Table)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(ProjectRenderTable[key as keyof typeof NewProjectRender], value),
        ),
      ),
    );
  return query;
}

PGTOP•14mo ago

tried a few variations of the key as keyof... with different types without success also tried to mess with the conditions type a bit

Angelelz•14mo ago

Looks like NewProjectRender and ProjectRenderTable are just types, not actual tables You have to import the tables from the schema, not the types

PGTOP•14mo ago

ProjectRenderTable is the pgTable definition from the schema

export const ProjectRenderTable = pgTable('project_renders', {
  id: uuid('id').primaryKey().defaultRandom(),
  projectId: uuid('project_id')
    .references(() => ProjectTable.id)
    .notNull(),
  renderId: varchar('render_id', { length: 64 }).notNull(),
  status: varchar('status', { length: 32 }).notNull(),
  renderTime: integer('render_time'),
  outputUrl: text('output_url'),
  cost: doublePrecision('cost'),
  errors: customJsonb<Record<string, string>[]>('errors'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
});

export const ProjectRenderTable = pgTable('project_renders', {
  id: uuid('id').primaryKey().defaultRandom(),
  projectId: uuid('project_id')
    .references(() => ProjectTable.id)
    .notNull(),
  renderId: varchar('render_id', { length: 64 }).notNull(),
  status: varchar('status', { length: 32 }).notNull(),
  renderTime: integer('render_time'),
  outputUrl: text('output_url'),
  cost: doublePrecision('cost'),
  errors: customJsonb<Record<string, string>[]>('errors'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
});

Angelelz•14mo ago

import { and, eq } from 'drizzle-orm';

import { createOrGetDb } from '~/db';
import { NewProjectRender, ProjectRenderTable } from '~/db/schema';

const Table = ProjectRenderTable;

const db = createOrGetDb();

export function update(data: Partial<NewProjectRender>, conditions) {
  const query = db
    .update(ProjectRenderTable)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(ProjectRenderTable[key as keyof typeof ProjectRenderTable], value),
        ),
      ),
    );
  return query;
}

import { and, eq } from 'drizzle-orm';

import { createOrGetDb } from '~/db';
import { NewProjectRender, ProjectRenderTable } from '~/db/schema';

const Table = ProjectRenderTable;

const db = createOrGetDb();

export function update(data: Partial<NewProjectRender>, conditions) {
  const query = db
    .update(ProjectRenderTable)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(ProjectRenderTable[key as keyof typeof ProjectRenderTable], value),
        ),
      ),
    );
  return query;
}

What is the shape of conditions?

PGTOP•14mo ago

should be Partial<NewProjectRender> i'm using it to find specific rows to update i've tried a few different types, still same error i've also tried eq(ProjectRenderTable[key as keyof typeof ProjectRenderTable], value), somewhat of a different error

No overload matches this call.
  Overload 1 of 3, '(left: Column<ColumnBaseConfig<ColumnDataType, string>, object, object>, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'Column<ColumnBaseConfig<ColumnDataType, string>, object, object>'.
      Type 'boolean' is not assignable to type 'Column<ColumnBaseConfig<ColumnDataType, string>, object, object>'.
  Overload 2 of 3, '(left: Aliased<unknown>, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'Aliased<unknown>'.
      Type 'boolean' is not assignable to type 'Aliased<unknown>'.
  Overload 3 of 3, '(left: SQLWrapper, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'SQLWrapper'.
      Type 'boolean' is not assignable to type 'SQLWrapper'.

No overload matches this call.
  Overload 1 of 3, '(left: Column<ColumnBaseConfig<ColumnDataType, string>, object, object>, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'Column<ColumnBaseConfig<ColumnDataType, string>, object, object>'.
      Type 'boolean' is not assignable to type 'Column<ColumnBaseConfig<ColumnDataType, string>, object, object>'.
  Overload 2 of 3, '(left: Aliased<unknown>, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'Aliased<unknown>'.
      Type 'boolean' is not assignable to type 'Aliased<unknown>'.
  Overload 3 of 3, '(left: SQLWrapper, right: unknown): SQL<unknown>', gave the following error.
    Argument of type 'boolean | PgColumn<{ name: "id"; tableName: "project_renders"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}> | ... 12 more ... | { ...; }' is not assignable to parameter of type 'SQLWrapper'.
      Type 'boolean' is not assignable to type 'SQLWrapper'.

Angelelz•14mo ago

I think the problem is the shape of conditions. How are you defining that?

PGTOP•14mo ago

only tried to explicitly define it in the signature, so something like

export function update(data: Partial<NewProjectRender>, conditions: Partial<NewProjectRender>)

export function update(data: Partial<NewProjectRender>, conditions: Partial<NewProjectRender>)

ideally i want to use import update and invoke it with different conditions so i'm thinking it makes sense here don't mind changing it elsewhere though

Angelelz•14mo ago

Well, by the way you are using it it should be an object with column names for ProjectRenderTable and the value it should be equal to the value you want it to be equal to

PGTOP•14mo ago

yeah, I'm using it something like this:

import {update} from '...'

update(data, {renderId: 'some id', projectId: 'project id'})

import {update} from '...'

update(data, {renderId: 'some id', projectId: 'project id'})

which should be defined cols of ProjectRenderTable

Angelelz•14mo ago

That should work

PGTOP•14mo ago

yeah, it does, i just get the type error even with your last snipppet above, i get have the type error: Type 'boolean' is not assignable to type 'SQLWrapper' actually do you mean I have to explicitly define an object with the col names i'll update? like this?

export function update(
  data: Partial<NewProjectRender>,
  conditions: { renderId: string; projectId: string },
) {
  const query = db
    .update(Table)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(Table[key as keyof typeof Table], value),
        ),
      ),
    );
  return query;
}

export function update(
  data: Partial<NewProjectRender>,
  conditions: { renderId: string; projectId: string },
) {
  const query = db
    .update(Table)
    .set(data)
    .where(
      and(
        ...Object.entries(conditions).map(([key, value]) =>
          eq(Table[key as keyof typeof Table], value),
        ),
      ),
    );
  return query;
}

still gives me the Type 'boolean' is not assignable to type 'SQLWrapper' type error abovee

Gaming

Programming

update where multiple conditions