Drizzle Team•15mo ago

Postgres functions in the ORM?

Hello everyone, I'm learning typescript and SQL (with drizzle) and I'm trying to make an api endpoint that has something like this

const {name, owner, hidden, uuid} = updateServerDto;
const upd = this.db.execute(
        sql`
        UPDATE ${servers} 
        SET server_name = COALESCE(${name}, server_name), 
            server_owner = COALESCE(${owner}, server_owner),
            hidden = COALESCE(${hidden}, hidden) 
        WHERE server_uuid = ${uuid}
        `,
      );

const {name, owner, hidden, uuid} = updateServerDto;
const upd = this.db.execute(
        sql`
        UPDATE ${servers} 
        SET server_name = COALESCE(${name}, server_name), 
            server_owner = COALESCE(${owner}, server_owner),
            hidden = COALESCE(${hidden}, hidden) 
        WHERE server_uuid = ${uuid}
        `,
      );

I'm updating attributes (name, owner, hidden...) that are sent from a PATCH request and some fields are optional so they could be null/undefined. COALESCE() here gives the original value in the column if the attributes are null. This sql query works but I would like to ask if its possible to do this without calling sql`` operator. Earlier i tried something like

const upd = await this.db
        .update(servers)
        .set({
          name: name ? name : servers.name,
          serverOwner: owner ? owner : servers.owner,
        })
        .where(eq(servers.uuid, uuid))

const upd = await this.db
        .update(servers)
        .set({
          name: name ? name : servers.name,
          serverOwner: owner ? owner : servers.owner,
        })
        .where(eq(servers.uuid, uuid))

But it did not let me compile, giving me this error instead

error TS2322: Type 'string | PgColumn<{ name: "server_name"; tableName: "servers"; dataType: "string"; columnType: "PgText"; data: string; driverParam: string; notNull: true; hasDefault: false; enumValues: [string, ...string[]]; baseColumn: never; }, {}, {}>' is not assignable to type 'string | SQL<unknown>'.
  Type 'PgColumn<{ name: "server_name"; tableName: "servers"; dataType: "string"; columnType: "PgText"; data: string; driverParam: string; notNull: true; hasDefault: false; enumValues: [string, ...string[]]; baseColumn: never; }, {}, {}>' is not assignable to type 'string | SQL<unknown>'.

error TS2322: Type 'string | PgColumn<{ name: "server_name"; tableName: "servers"; dataType: "string"; columnType: "PgText"; data: string; driverParam: string; notNull: true; hasDefault: false; enumValues: [string, ...string[]]; baseColumn: never; }, {}, {}>' is not assignable to type 'string | SQL<unknown>'.
  Type 'PgColumn<{ name: "server_name"; tableName: "servers"; dataType: "string"; columnType: "PgText"; data: string; driverParam: string; notNull: true; hasDefault: false; enumValues: [string, ...string[]]; baseColumn: never; }, {}, {}>' is not assignable to type 'string | SQL<unknown>'.

If there is a better way to go about what I'm trying to do, any help would be greatly appreciated!

2 Replies

Angelelz•15mo ago

This is how I would do it:

const serversSet: Record<keyof InferInsertModel<servers>, string> = {}
if (name) {
  serversSet.name = name;
}
if (serverOwner) {
  serversSet.serverOwner = serverOwner;
}
const upd = await this.db
        .update(servers)
        .set(serversSet)
        .where(eq(servers.uuid, uuid))

const serversSet: Record<keyof InferInsertModel<servers>, string> = {}
if (name) {
  serversSet.name = name;
}
if (serverOwner) {
  serversSet.serverOwner = serverOwner;
}
const upd = await this.db
        .update(servers)
        .set(serversSet)
        .where(eq(servers.uuid, uuid))

Now, I currently don't have anywhere to test this on but you get the idea.

wanitooOP•15mo ago

this is way cleaner than what i was currently doing. Thank you!

Gaming

Programming

Postgres functions in the ORM?