Driver for Drizzle ORM
Closed this issue · 22 comments
I’m trying to implement the op-sqlite driver for Drizzle ORM, the biggest issue is that Drizzle requires a raw interface like better-sqlite3, it return rows as arrays instead of objects
https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md#rawtogglestate---this
https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/better-sqlite3/session.ts#L146
Upvote & Fund
- We're using Polar.sh so you can upvote and help fund this issue.
- We receive the funding once the issue is completed & confirmed by you.
- Thank you in advance for helping prioritize & fund our backlog.
if you don't mind some performance penalty you can just implement this on the typescript side. On the index.ts you can just add a method or something that just iterates over the results and returns them as rows. Or am I misunderstanding you?
yes, i can do it at js side
this is a workaround
async function queryValues(sql: string, params: any[]) {
const res = await dbConn.executeAsync(sql, params)
if (!res.metadata?.length) return []
const keys = res.metadata.sort((a, b) => a.index - b.index).map((m) => m.name)
const rows: any[] = []
res.rows!._array.forEach((row) => {
const rowValues: any[] = []
keys.forEach((key) => {
rowValues.push(row[key])
})
rows.push(rowValues)
})
return rows
}It looks quite ugly, it would be the best if it could be supported at the native level.
PRs are welcome, if not I would be willing to add the feature if you are willing to sponsor the work.
@terry-fei Would you like to provide PR with drizzle orm support?
I think it will be quite useful to have integration with great ORM
this wouldn't work, cause order is not guaranteed
order is not guaranteed?
Just FYI, you don't need to re-sort the result array, on the native side everything is linked in a std::vector and the keys are reverse linked, these objects are not managed by the JS runtime, so the order cannot change.
https://stackoverflow.com/questions/5525795/does-javascript-guarantee-object-property-order
there's no need to do anything above written by @terry-fei, Object.values do the same, but neither his approach nor Object.values do not guarantee order upon insertion to the object
I'm not sure if your implementation with std::vector changes the behaviour
we've had the same issue with D1 driver
Yeah, my comment is valid. This are not normal JavaScript objects, these are JSI HostObjects, everything is implemented natively in C++ and the data is stored in a std::vector. Order will not change.
I see, than simple Object.values() should be just fine, thanks!
I don't know if Object.values works on HostObjects. The code by @terry-fei is the safest way to iterate through the result set.
Object.value is not working for host objects.
const copyJsiHostObject = <T extends object, TKeys extends keyof T>(
obj: T,
fields?: readonly TKeys[],
): Pick<T, TKeys> => {
const result = {} as T;
for (const key of fields ?? Object.keys(obj)) {
// @ts-expect-error
result[key] = obj[key];
}
return result;
};HostObjects are not real JS objects, so prototype functions will not work.
Object.keys is working
@XantreGodlike do you happen to have repo where you have implemented it ?
Nope, I didn't implement it. If you would - it's better to provide PR than creating your own repo
Hi! I'm writing a op-sqlite driver for Drizzle ORM and I can’t map the result correctly because I’m faced with the problem of the lack of an api that returns raw data (array of arrays).
An example of why this is necessary:
import { open } from '@op-engineering/op-sqlite';
const db = open({
name: 'db',
});
// create tables
db.execute('create table if not exists users (id integer primary key, name text)');
db.execute('create table if not exists items (id integer primary key, done integer, value text, userId integer)');
// insert users
db.execute('insert into users (id, name) values (?, ?)', [11, 'user1']);
db.execute('insert into users (id, name) values (?, ?)', [22, 'user2']);
db.execute('insert into users (id, name) values (?, ?)', [33, 'user3']);
// select users
db.execute('select * from users');
result: [{"id": 11, "name": "user1"}, {"id": 22, "name": "user2"}, {"id": 33, "name": "user3"}]
// insert items
db.execute('insert into items (id, done, value, userId) values (?, ?, ?, ?)', [1, 0, 'item1', 11]);
db.execute('insert into items (id, done, value, userId) values (?, ?, ?, ?)', [2, 0, 'item2', 22]);
db.execute('insert into items (id, done, value, userId) values (?, ?, ?, ?)', [3, 1, 'item3', 33]);
// select items
db.execute('select * from items');
result: [{"done": 0, "id": 1, "userId": 11, "value": "item1"}, {"done": 0, "id": 2, "userId": 22, "value": "item2"}, {"done": 1, "id": 3, "userId": 33, "value": "item3"}]
// join
db.execute('select items.id, users.id from items left join users on items.userId = users.id');
result: [{"id": 1}, {"id": 2}, {"id": 3}] <---Drizzle Expo driver released: https://orm.drizzle.team/docs/get-started-sqlite#expo-sqlite
I've created a funding goal for this issue, once it is reached I will take care of it
Thank you @ospfranco for contributing to close this issue! ⭐
The rewards from this issue, totalling $150, has been shared with you.
What now?
- Create a Polar account
- See incoming rewards & setup Stripe to receive them
- Get payouts as backers finalize their payments
If you already have a Polar account setup, you don't need to do anything.