DT
Drizzle Teamwanitoo

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!
Angelelz
Angelelz•246d 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.
wanitoo
wanitoo•246d ago
this is way cleaner than what i was currently doing. Thank you!
Want results from more Discord servers?
Add your server
More Posts
InferModelFromColumns with columns defined with sql``Let's say I have a select list that looks like this ```ts const defaultBookFields = { id: books.Issue with drizzle-zod?Schema ``` export const testinArray = pgTable("testing_array", { id: varchar("id", { length: 14 Optional One-to-One RelationCurrently it's possible to define only a One-to-One relation (https://orm.drizzle.team/docs/rqb#one-Having Issues Pushing Database SchemaHi I am getting this issue whenever I am trying to push the database schema. Any help would be greatmigration failedcode: ```js const migrationClient = postgres("postgresql://jer:admin@localhost:5432/test", { max: 1TypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys')I am attempting to run db push, but I am getting this error above^^ does anyone know what might be Nesting sqlite drizzle into a deeper location in my ts app, difficulties with src/ vs dist/Hey! I'm very new to drizzle and trying to set up a very small drizzle instance for a service that rsyntax highlighting not workingI reinstalled the node_modules multiple times and it didnt fix my problem. This isnt suposed to look