3 replies

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!

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}
        `,
      );

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!

Postgres functions in the ORM?

Postgres functions in the ORM?

Similar Threads

Similar Threads

Similar Threads