Topics

Theo's Typesafe Cult•2y ago

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•2y 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^proneOP•2y 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•2y ago

Where is your db?

_tweak^proneOP•2y ago

Located at a shared hosting. Prisma has no problem with it

iukea•2y 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^proneOP•2y 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•2y ago

Mmmm Ya

_tweak^proneOP•2y 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•2y ago

Okay

_tweak^proneOP•2y ago

let me see if i can test the open connection hypothesis. 2 secs

iukea•2y ago

Can you send a screenshot of where you make the connection into the db?

iukea•2y ago

iukea•2y ago

Is it setup like this with "connection" If so

_tweak^proneOP•2y ago

sure,

_tweak^proneOP•2y ago

iukea•2y 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^proneOP•2y 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•2y ago

Same user and host Shot in the dark

_tweak^proneOP•2y 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•2y ago

🫡🫡🫡🫡🫡🫡

_tweak^proneOP•2y 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•2y ago

Working?

_tweak^proneOP•2y ago

at the moment yes, but i will have to see it over the course of hours now

iukea•2y ago

_tweak^proneOP•2y ago

Thanks a bunch m8 ! Hope this hold ! And what a bizarre thing 1

iukea•2y 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^proneOP•2y ago

Very nice ! I like drizzl as its much closer to Laravels Eloquent.

iukea•2y 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^proneOP•2y ago

yearh, but i enjou the joins. the control 🙂

iukea•2y ago

I am so ready About to bring this thing into hire gear

_tweak^proneOP•2y 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•2y ago

https://orm.drizzle.team/kit-docs/overview

Drizzle Kit – DrizzleORM

Drizzle ORM | %s

iukea•2y ago

Drizzle kit

_tweak^proneOP•2y ago

im not sure i understand - this is more of a Prisma Studio thing right?

iukea•2y ago

Drizzle kit helps you with migrations and sync

_tweak^proneOP•2y 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•2y 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^proneOP•2y 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•2y ago

Ya I am sorry I am going to start driving soon but I followed this guide when I got started

_tweak^proneOP•2y ago

its just - to me drizzlekit seems more like prisma studio and push/pull - not type-generation

iukea•2y ago

https://youtu.be/Qo-RXkSwOtc?si=lc4nlTibvCH9MzOb

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•2y ago

Also they have zod support or something idk https://orm.drizzle.team/docs/zod

Usage – DrizzleORM

Drizzle ORM | %s

_tweak^proneOP•2y ago

yearh, but sadly that doesnt solve the problem :/ it deosnt take relations into account

iukea•2y ago

https://orm.drizzle.team/docs/rqb#declaring-relations

Relational queries – DrizzleORM

Drizzle ORM | %s

iukea•2y ago

Ast thing I got I need to go I will be back later

_tweak^proneOP•2y 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^proneOP•2y 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•2y ago

I will look into it more and ask

_tweak^proneOP•2y ago

Great - let me know if the usecase makes sense. But its super type-important 🙂

Theo's Typesafe Cult Join

21KMembers

View on Discord

Want results from more Discord servers?

Add your server