Astro + DrizzleORM => trouble

Im working with Drizzle ORM on Astro and im having some problems with continous "ETIMEDOUT". When using Prisma for the same project, I have no problem connecting to the MySQL. Anyone has a clue as to what this could be ? Does DrizzleORM and Astro play nice together ? Aka: will Drizzle close its connections once done ? It could seem like its a connection-not-closing problem, as my MySQL has maximum of 10 connections, and once this error happens, it will instantly "ETIMEDOUT" for every request, until i stop/start Astro server.
49 Replies
iukea
iukea•16mo ago
Can you attach a log please Drizzle should not have any issues with Astro just make sure you have the write drivers downloaded for drizzle for the correct db (mySQL, postgresSQL, planet scale) If you can actually see the logs of your server, you might be able to see that you're hitting a connection pool limit Because you are just leaving them open and for whatever reason not closing them
_tweak^prone
_tweak^prone•16mo ago
@iukea its working until it hits the ~10 connections in total. The driver "should be correct" - as its working with MySQL (and the server is MySQL) Each pageload takes ~4 SQL lookups, at the third page-reload it locks up. I am not leaving the connection intentionally open - I have not seen any guides as to how to close them again (let alone, combined w Astro) @iukea I dont have access to the MySQL server sadly. But everything "checks out" as to hitting the limit
iukea
iukea•16mo ago
Where is your db?
_tweak^prone
_tweak^prone•16mo ago
Located at a shared hosting. Prisma has no problem with it
iukea
iukea•16mo ago
Is Prisma running on a serverless system or running on some bare metal Because it sounds like there's something else that's taking up this pooling and not giving it back You can run some SQL commands to see active sessions My guess is Prisma is just holding onto a pool
_tweak^prone
_tweak^prone•16mo ago
theres nothing else connecting to this db. I have two versions of my code, one iwth prisma, one with drizzl. Both in Astro - bpth in the same project. When i do "connection" or "poolconnection" (mysql) in Drizzl - the result is the same.
error Can't add new command when connection is in closed state
File:
/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22
Code:
93 | const c = this.connection;
> 94 | const localErr = new Error();
| ^
95 | if (typeof params === 'function') {
96 | throw new Error(
97 | 'Callback function is not available with promise clients.'
Stacktrace:
Error: Can't add new command when connection is in closed state
at PromiseConnection.query (/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22)
at MySql2PreparedQuery.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/mysql2/index.mjs:51:37)
at QueryPromise.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/session-9628aea0.mjs:1150:31)
at QueryPromise.then (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
error Can't add new command when connection is in closed state
File:
/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22
Code:
93 | const c = this.connection;
> 94 | const localErr = new Error();
| ^
95 | if (typeof params === 'function') {
96 | throw new Error(
97 | 'Callback function is not available with promise clients.'
Stacktrace:
Error: Can't add new command when connection is in closed state
at PromiseConnection.query (/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22)
at MySql2PreparedQuery.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/mysql2/index.mjs:51:37)
at QueryPromise.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/session-9628aea0.mjs:1150:31)
at QueryPromise.then (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
This is the error im getting from drizzl / astro.
iukea
iukea•16mo ago
Mmmm Ya
_tweak^prone
_tweak^prone•16mo ago
This starts the first time after i get one of these:
error read ETIMEDOUT
File:
/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22
Code:
93 | const c = this.connection;
> 94 | const localErr = new Error();
| ^
95 | if (typeof params === 'function') {
96 | throw new Error(
97 | 'Callback function is not available with promise clients.'
Stacktrace:
Error: read ETIMEDOUT
at PromiseConnection.query (/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22)
at MySql2PreparedQuery.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/mysql2/index.mjs:51:37)
at QueryPromise.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/session-9628aea0.mjs:1150:31)
at QueryPromise.then (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
error read ETIMEDOUT
File:
/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22
Code:
93 | const c = this.connection;
> 94 | const localErr = new Error();
| ^
95 | if (typeof params === 'function') {
96 | throw new Error(
97 | 'Callback function is not available with promise clients.'
Stacktrace:
Error: read ETIMEDOUT
at PromiseConnection.query (/Users/<folder>/astro-test/node_modules/mysql2/promise.js:94:22)
at MySql2PreparedQuery.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/mysql2/index.mjs:51:37)
at QueryPromise.execute (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/session-9628aea0.mjs:1150:31)
at QueryPromise.then (file:///Users/<folder>/astro-test/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
all code runs server-side ( as in, SSR in Astro)
iukea
iukea•16mo ago
Okay
_tweak^prone
_tweak^prone•16mo ago
let me see if i can test the open connection hypothesis. 2 secs
iukea
iukea•16mo ago
Can you send a screenshot of where you make the connection into the db?
iukea
iukea•16mo ago
iukea
iukea•16mo ago
Is it setup like this with "connection" If so
_tweak^prone
_tweak^prone•16mo ago
sure,
_tweak^prone
_tweak^prone•16mo ago
iukea
iukea•16mo ago
Try import { drizzle } from "drizzle-orm/mysql2"; import mysql from "mysql2/promise"; const poolConnection = mysql.createPool({ host: "host", user: "user", database: "database", ... }); const db = drizzle(poolConnection);
_tweak^prone
_tweak^prone•16mo ago
without the connection string ? I have tried the poolConnection - its the same result. But you would like to try it with username/host separated ?
iukea
iukea•16mo ago
Same user and host Shot in the dark
_tweak^prone
_tweak^prone•16mo ago
I can try with specifying host/user/db instead of connectionstring. 2 secs The shot in the dark seemed to work. Let me try with connectionString instead
iukea
iukea•16mo ago
🫡🫡🫡🫡🫡🫡
_tweak^prone
_tweak^prone•16mo ago
I will have to test this quite a bit more but it seems specifiying the same content in an object, instead of in a connection string.. fixed it 😮
iukea
iukea•16mo ago
Working?
_tweak^prone
_tweak^prone•16mo ago
at the moment yes, but i will have to see it over the course of hours now
iukea
iukea•16mo ago
_tweak^prone
_tweak^prone•16mo ago
Thanks a bunch m8 ! Hope this hold ! And what a bizarre thing 1
iukea
iukea•16mo ago
I hope it works also. Next-auth and Drizzle are now in kinda working also https://github.com/mschieller/drizzle-nextauth-planetscale
GitHub
GitHub - mschieller/drizzle-nextauth-planetscale: A simple NextJS p...
A simple NextJS project showing the usage of NextAuth with Drizzle and PlanetScale, utilising prepared statement for all queries. - GitHub - mschieller/drizzle-nextauth-planetscale: A simple NextJS...
_tweak^prone
_tweak^prone•16mo ago
Very nice ! I like drizzl as its much closer to Laravels Eloquent.
iukea
iukea•16mo ago
Also you can write you queries just like you did in Prisma You don't have to do the whole entire left join right join thing lol He gives you two different ways to query for relationships
_tweak^prone
_tweak^prone•16mo ago
yearh, but i enjou the joins. the control 🙂
iukea
iukea•16mo ago
I am so ready About to bring this thing into hire gear
_tweak^prone
_tweak^prone•16mo ago
do you know if theres anyway to get the Type-definition returned by a Drizzl-call ? So that it can be used as a parameter for a function (for example) Like prisma has
iukea
iukea•16mo ago
Drizzle kit
_tweak^prone
_tweak^prone•16mo ago
im not sure i understand - this is more of a Prisma Studio thing right?
iukea
iukea•16mo ago
Drizzle kit helps you with migrations and sync
_tweak^prone
_tweak^prone•16mo ago
What i meant was this: Prisma has this:
export const PostComponentMainInclude = {
include: {

written_by_user: {
select: {
slug: true,
username: true,
}
},
submitted_by_user: {
select: {
slug: true,
username: true,
}
},
edited_by_user: {
select: {
slug: true,
username: true,
}
},
via: {
select: {
name: true,
}
},

forum_thread: {
include: {
_count: { select: { items: true } },
forum: {
select: {
title: true,
slug: true,
},
},
}
}
},
};
export type PostComponentMainType = Prisma.postsGetPayload<typeof PostComponentMainInclude>
export const PostComponentMainInclude = {
include: {

written_by_user: {
select: {
slug: true,
username: true,
}
},
submitted_by_user: {
select: {
slug: true,
username: true,
}
},
edited_by_user: {
select: {
slug: true,
username: true,
}
},
via: {
select: {
name: true,
}
},

forum_thread: {
include: {
_count: { select: { items: true } },
forum: {
select: {
title: true,
slug: true,
},
},
}
}
},
};
export type PostComponentMainType = Prisma.postsGetPayload<typeof PostComponentMainInclude>
Its the Typescript-type-definition of the Payload call you are about to make this means i can use ´PostComponentMainType` as my "requred type" in a function props for example
iukea
iukea•16mo ago
It generates all your types for you npm i -D drizzle-kit and then run npm run introspect On your non prod system
_tweak^prone
_tweak^prone•16mo ago
So for example, lets say i have this Drizzl:
const comments = await db.select(
{
uid: forum_thread_items.uid,
user_id: forum_thread_items.user_id,
content: forum_thread_items.content,
is_spam: forum_thread_items.is_spam,
version: forum_thread_items.version,
created_at: forum_thread_items.created_at,
user: {
id: users.id,
username: users.username,
slug: users.slug,
},
})
.from( forum_thread_items )
.where( eq( forum_thread_items.forum_thread_id, forum_thread_id ) )
.leftJoin( users, eq( forum_thread_items.user_id, users.id ) )
.offset( ( page - 1 ) * pageSize )
.limit( pageSize )
const comments = await db.select(
{
uid: forum_thread_items.uid,
user_id: forum_thread_items.user_id,
content: forum_thread_items.content,
is_spam: forum_thread_items.is_spam,
version: forum_thread_items.version,
created_at: forum_thread_items.created_at,
user: {
id: users.id,
username: users.username,
slug: users.slug,
},
})
.from( forum_thread_items )
.where( eq( forum_thread_items.forum_thread_id, forum_thread_id ) )
.leftJoin( users, eq( forum_thread_items.user_id, users.id ) )
.offset( ( page - 1 ) * pageSize )
.limit( pageSize )
This gets the comments and merges in the "user" type with a leftjoin I make a component: <ShowComment comment={...}> And i want the TYPE of the prop "comment" in my component props to be exactly one of the returntypes of the above drizzl. So i go like this:
const ShowComponent = (props:{
comment: ????
}) =>
const ShowComponent = (props:{
comment: ????
}) =>
Can Drizzlekit give me "????" ?
iukea
iukea•16mo ago
Ya I am sorry I am going to start driving soon but I followed this guide when I got started
_tweak^prone
_tweak^prone•16mo ago
its just - to me drizzlekit seems more like prisma studio and push/pull - not type-generation
iukea
iukea•16mo ago
Marius Espejo
YouTube
Drizzle ORM First impressions - migrations, relations, queries!
In this video we take a quick look at the drizzle ORM to see if it's good enough to replace other options like prisma, typeorm, and Kysely. We'll create a simple application to test out creating migrations, running introspection, creating queries, and using relations. If you're looking for an orm for database queries that has very good typescrip...
iukea
iukea•16mo ago
Also they have zod support or something idk https://orm.drizzle.team/docs/zod
_tweak^prone
_tweak^prone•16mo ago
yearh, but sadly that doesnt solve the problem :/ it deosnt take relations into account
iukea
iukea•16mo ago
Ast thing I got I need to go I will be back later
_tweak^prone
_tweak^prone•16mo ago
yearh, tahts just declaring the relations though - it wont give you any way to define a specific type that has been returned by the DB.table.select.leftjoin Its the return type that I / we need a way to get. This one: it changes with the SQL sentence, and "should be possible to get" from somewhere. Thats what Prisma can do:
_tweak^prone
_tweak^prone•16mo ago
The above example is a type containing SOME fields from "comment" type and SOME fields of "user" type - in a mixed type / result from the database. In prisma you can do commentGetPayload< your object that you pass into prisma db call without actually calling the database > and that will return a type that you can use to Type your parameters. Problem is, if you take Drizzle results and parse it into a component, when you use it in the component the type will by definition be "lost", as theres no relation from the component to where you use the component. (and it will have to be an "any" type, or you have to make a 1:1 copy type of whatever you return from the drizzl db.table.select.leftjoin)
iukea
iukea•16mo ago
I will look into it more and ask
_tweak^prone
_tweak^prone•16mo ago
Great - let me know if the usecase makes sense. But its super type-important 🙂
Want results from more Discord servers?
Add your server