Am I doing it right ?

1) I wanted to get the native sql query result typed. I used the following code.

type LimitedBoardDataVO = Pick<typeof noticeBoard.$inferSelect, 'id' | 'title' | 'content'>

export const getDrizzleSQLNoticeboard = () => {
    return db.execute(
        sql`select "id", "title", "content" from "notice_board" "noticeBoard" order by "noticeBoard"."createdAt" desc limit ${1}`
    ) as Promise<LimitedBoardDataVO[]>
}

type LimitedBoardDataVO = Pick<typeof noticeBoard.$inferSelect, 'id' | 'title' | 'content'>

export const getDrizzleSQLNoticeboard = () => {
    return db.execute(
        sql`select "id", "title", "content" from "notice_board" "noticeBoard" order by "noticeBoard"."createdAt" desc limit ${1}`
    ) as Promise<LimitedBoardDataVO[]>
}

which works fine in general. I was just wondering if there is another way with e.g. generics sql<LimitedBoardDataVO> or so ? 1b) Is there an easy / better way to get ts-types from the schema? Should I have to use things like drizzle-zod (haven't looked into)? Seems a little overkill if you don't want to do validation. So is there a better way to access / generate those types from the schema definitions ? 2) I implemented an app (SvelteKit stack against postgres / neondb) with drizzle, kysely and prisma. Just to compare DX, Migrations and Performance. I added drizzle in 2 connection configurations once with postgres and once with pg. I noticed that the pg connection dies quicker then the postgres connection ( at least that is what I think it does as the first query takes a little longer after a short time - around 500ms vs 50ms ). Is is just a thing of configuring the expiration-time of the connections? Is it even possible? I noticed that the connections of the rather slow prisma connection lives the longest. postgres seems to live a little longer then (node-)pg. 3) kyzely facilitates an easy possibility to log query duration additionally to the query itself. Is there something similar in drizzle?

6 Replies

Angelelz•14mo ago

Is there a reason why you're using db.execute() instead of the crud API? The crud API will give you correct types out of the box 2) You can configure all that in your driver, if that driver supports it, you can do it before you pass it to the drizzle object 3) Drizzle doesn't have that out of the box but you can create your own logger where you can have the the logic you want (for example console.time()), and then log the time after the query returns.

delightOP•14mo ago

Just wanted to see, what drizzle feels like when I get into the situation of needing sql ... obviously the simple sql I wouldn't do. I'll look into that the crud api is the one that looks like:

db.query.noticeBoard.findAll({})

db.query.noticeBoard.findAll({})

right?

Angelelz•14mo ago

No, that's the relational query API

delightOP•14mo ago

Well that is not 100% correct to say. TBH I was comparing drizzle, kysely and prisma on the look for something that would let me write simple SQL and put that into typed results - because knowing sql it is mostly looking up how the query is done in framework a,b,c. But I like the the combination of fine-grained schemas, migration and good performance. So I'll take the pill of using a different api to query the data

Angelelz•14mo ago

Drizzle aims to be as close to SQL syntax as possible. It does feel like writing sql

delightOP•14mo ago

I'll try the crud api .. I hope I'll get familiar with it quickly Indeed crud is pretty intuitive ... I'll play some more with it 🙂

Gaming

Programming

Am I doing it right ?