Astro + DrizzleORM => trouble

_tweak^prone · 2023-07-30T18:34:54.844Z

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.

I

iukea•7/30/23, 8:57 PM

Can you attach a log please

I

iukea•7/30/23, 8:58 PM

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)

I

iukea•7/30/23, 8:58 PM

If you can actually see the logs of your server, you might be able to see that you're hitting a connection pool limit

I

iukea•7/30/23, 8:59 PM

Because you are just leaving them open and for whatever reason not closing them

_

_tweak^proneOP•7/30/23, 9:14 PM

@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)

_

_tweak^proneOP•7/30/23, 9:15 PM

@iukea I dont have access to the MySQL server sadly. But everything "checks out" as to hitting the limit

__tweak^prone @iukea its working until it hits the ~10 connections in total. The driver "shou...

I

iukea•7/30/23, 9:26 PM

Where is your db?

_

_tweak^proneOP•7/30/23, 9:27 PM

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

I

iukea•7/30/23, 9:28 PM

Is Prisma running on a serverless system or running on some bare metal

I

iukea•7/30/23, 9:28 PM

Because it sounds like there's something else that's taking up this pooling and not giving it back

I

iukea•7/30/23, 9:28 PM

You can run some SQL commands to see active sessions

I

iukea•7/30/23, 9:28 PM

My guess is Prisma is just holding onto a pool

_

_tweak^proneOP•7/30/23, 9:28 PM

theres nothing else connecting to this db. I have two versions of my code, one iwth prisma, one with drizzl.

_

_tweak^proneOP•7/30/23, 9:28 PM

Both in Astro - bpth in the same project.

_

_tweak^proneOP•7/30/23, 9:29 PM

When i do "connection" or "poolconnection" (mysql) in Drizzl - the result is the same.

_

_tweak^proneOP•7/30/23, 9:30 PM

 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)

 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.

I

iukea•7/30/23, 9:30 PM

Mmmm

I

iukea•7/30/23, 9:30 PM

Ya

_

_tweak^proneOP•7/30/23, 9:30 PM

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)

 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)

_

_tweak^proneOP•7/30/23, 9:31 PM

all code runs server-side ( as in, SSR in Astro)

I

iukea•7/30/23, 9:32 PM

Okay

_

_tweak^proneOP•7/30/23, 9:32 PM

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

I

iukea•7/30/23, 9:33 PM

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

I

iukea•7/30/23, 9:33 PM

I

iukea•7/30/23, 9:33 PM

Is it setup like this with "connection"

I

iukea•7/30/23, 9:33 PM

If so

_

_tweak^proneOP•7/30/23, 9:33 PM

sure,

_

_tweak^proneOP•7/30/23, 9:33 PM

I

iukea•7/30/23, 9:33 PM

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•7/30/23, 9:34 PM

without the connection string ?

_

_tweak^proneOP•7/30/23, 9:34 PM

I have tried the poolConnection - its the same result. But you would like to try it with username/host separated ?

I

iukea•7/30/23, 9:34 PM

Same user and host

I

iukea•7/30/23, 9:34 PM

Shot in the dark

_

_tweak^proneOP•7/30/23, 9:35 PM

I can try with specifying host/user/db instead of connectionstring. 2 secs

Iiukea Shot in the dark

_

_tweak^proneOP•7/30/23, 9:41 PM

The shot in the dark seemed to work. Let me try with connectionString instead

I

iukea•7/30/23, 9:41 PM

_

_tweak^proneOP•7/30/23, 9:42 PM

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

I

iukea•7/30/23, 9:45 PM

Working?

_

_tweak^proneOP•7/30/23, 9:46 PM

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

I

iukea•7/30/23, 9:47 PM

_

_tweak^proneOP•7/30/23, 9:48 PM

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

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

I

iukea•7/30/23, 9:49 PM

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•7/30/23, 9:50 PM

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

I

iukea•7/30/23, 9:50 PM

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•7/30/23, 9:51 PM

yearh, but i enjou the joins. the control

I

iukea•7/30/23, 9:52 PM

I am so ready

I

iukea•7/30/23, 9:52 PM

About to bring this thing into hire gear

_

_tweak^proneOP•7/30/23, 9:54 PM

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)

_

_tweak^proneOP•7/30/23, 9:54 PM

Like prisma has

I

iukea•7/30/23, 9:56 PM

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

Drizzle Kit – DrizzleORM

Drizzle ORM | %s

Similar Threads

Similar Threads

Similar Threads