Drizzle Team•17mo ago

Connection to SQL destroyed (after idle). Do I need to manually handle open + close for each query?

i deployed my SQL server and nodejs server on railway. The server is up and running, for some reason my SQL connection disconnected? I was wondering if I am missing something fundamental or what?

database.ts
import dotenv from "dotenv";
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

import * as schema from "../schema.js";

dotenv.config();

const connection = await mysql.createConnection({
  host: process.env.MYSQLHOST,
  port: Number(process.env.MYSQLPORT),
  user: process.env.MYSQLUSER,
  password: process.env.MYSQLPASSWORD,
  database: process.env.MYSQLDATABASE,
});

const db = drizzle(connection, { schema, mode: "default" });

export { db };

database.ts
import dotenv from "dotenv";
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

import * as schema from "../schema.js";

dotenv.config();

const connection = await mysql.createConnection({
  host: process.env.MYSQLHOST,
  port: Number(process.env.MYSQLPORT),
  user: process.env.MYSQLUSER,
  password: process.env.MYSQLPASSWORD,
  database: process.env.MYSQLDATABASE,
});

const db = drizzle(connection, { schema, mode: "default" });

export { db };

I am using this db in different controllers to make queries. Am i messing up somewhere?

example query
const userTodos = await db.query.users.findFirst({
        where: eq(users.id, +userId),
        with: {
          todos: {
            where: (todoItem) => eq(todoItem.type, type),
          },
        },
      });

example query
const userTodos = await db.query.users.findFirst({
        where: eq(users.id, +userId),
        with: {
          todos: {
            where: (todoItem) => eq(todoItem.type, type),
          },
        },
      });

Should I handle the open and close state manually in every query or am I missing something?

6 Replies

Andrii Sherman•17mo ago

Drizzle is not responsible for the database connection; mysql2 is. After reviewing your code, everything appears to be in order. However, the connection might be disrupted by external factors. You could consider using the Pool from mysql2, which can handle reconnections on your behalf.

AnandOP•17mo ago

so I should create a pool of connections and pass it to drizzle(poolConnection, otherConfigs) and then i will simply use the db for querying right?

Andrii Sherman•17mo ago

yes, you can try this approach didn't have any issue with it, when was using Pool

AnandOP•17mo ago

Ok i will try the pool way. the error I am currently getting is Can't add new command when connection is in closed state that is maybe because my DB did not receive any request for 8 hrs or more and hence the connection was closed.

Andrii Sherman•17mo ago

I guess you won't get any of that with Pool so if some connections are open - Pool will use it if all of them are closed - Pool will create a new one and use it if new request is there and connection is busy - Pool will create a new one and use it I guess by default mysql2 has 10 connection limit in a Pool but you can setup any size you need

AnandOP•17mo ago

this makes sense. thanks alot Andrew.

Gaming

Programming

Connection to SQL destroyed (after idle). Do I need to manually handle open + close for each query?