Do I need to close the DB connection after running a script using drizzle?

I have a very simple script to import some CSV data:

import { System } from "@foo/core/system";
import { readFileSync } from "fs";

const [, , csvPath] = process.argv;
const csv = readFileSync(csvPath, "utf8");
const lines = csv.split("\n");

for (let line of lines) {
  line = line.trim();

  if (line.length === 0) {
    continue;
  }

  let [identifier, name] = line.split(";");
  console.log(`Importing ${identifier} (${name})`);
  await System.create({ identifier, name });
}

// system/index.ts
export async function create(input: { name: string; identifier: string }) {
  return db.insert(systems).values(input).execute();
}

import { System } from "@foo/core/system";
import { readFileSync } from "fs";

const [, , csvPath] = process.argv;
const csv = readFileSync(csvPath, "utf8");
const lines = csv.split("\n");

for (let line of lines) {
  line = line.trim();

  if (line.length === 0) {
    continue;
  }

  let [identifier, name] = line.split(";");
  console.log(`Importing ${identifier} (${name})`);
  await System.create({ identifier, name });
}

// system/index.ts
export async function create(input: { name: string; identifier: string }) {
  return db.insert(systems).values(input).execute();
}

The script runs (executed with tsx) and I can see the rows inserted, but the script is not exiting. It just 'hangs' after this loop. Do I need to manually close the db connection?

15 Replies

roobOP•2y ago

It looks a bit like this one: https://github.com/drizzle-team/drizzle-orm/issues/619

roobOP•2y ago

As I saw in the docs, for migrations, they also make use of a pool of size one, so it autocloses:

roobOP•2y ago

but script still hanging, meh I can export my connection (the raw postgres.js connection object) and then call connection.end() at the end of each script. is that intended? How does it work in serverless environments?

pandareaper•2y ago

Yes you would most certainly need to close the connection manually. That example of max: 1 would not result in any auto close behaviour AFAIK. The connection should however would auto disconnect after the idle_timeout configured on your connection. To shutdown, you'll need to call .end() on your postgres connection

roobOP•2y ago

@pandareaper yeah exporting the connection and calling .end() works. How is this handled in serverless context? Do I need to take care of this manually?

pandareaper•2y ago

It will be the same behaviour, you will need to call .end() when you are done with the connection However in a serverless environment you will typically keep the connection alive and re-use it between invocations as creating DB connections. So it's rare that you would call .end() unless you know your function isn't likely to run again soon

roobOP•2y ago

@pandareaper you mean because of the lambda beeing held 'warm' for example? so the connection is still alive? but will this result in my lambda invocation time beeing as long as the connection is open? or is the hanging promise only related to the whole execution environment and not the handle function itself

pandareaper•2y ago

If you are finding your lambda function invocation is just hanging, then you need to set context.callbackWaitsForEmptyEventLoop = false; See docs here https://docs.aws.amazon.com/lambda/latest/dg/nodejs-handler.html

AWS Lambda function handler in Node.js - AWS Lambda

The following example function logs the contents of the event object and returns the location of the logs.

roobOP•2y ago

I'm not executing it yet on lambda, was just wondering how this hanging promise/open connection is handled but for local dev I will end the connection now manually in my scripts thanks for your help!

Anand•17mo ago

i am facing a similar issue in mysql DB. should I close my connection after each query? how does that work? there is no clear doc around this.. 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 places 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),
          },
        },
      });

pandareaper•17mo ago

What error are you getting @spiritanand ? In 99% of cases, you do not want to close the connection after each query, you want to re-use it. Creating connections is a slow process

Anand•17mo ago

this is a side project with hardly any users so it is quite possible that the there are no requests for days, and then all of a sudden recruiters try their hand on my app the error I am getting is Can't add new command when connection is in closed state which basically means my connectionto sql db is closed and thus I cannot access it.

roobOP•17mo ago

@spiritanand I'm currently closing at the end of my script. I'm doing it like this:

import { connection } from "@myproject/core/drizzle";
import { importAssetsFromCSV } from "@myproject/core/util/csv-import";

const [, , csvPath] = process.argv;
await importAssetsFromCSV(csvPath);
connection.end();

import { connection } from "@myproject/core/drizzle";
import { importAssetsFromCSV } from "@myproject/core/util/csv-import";

const [, , csvPath] = process.argv;
await importAssetsFromCSV(csvPath);
connection.end();

as you can see, I'm importing the 'raw' connection variable from my connection setup, and at the end of my script, I'm calling connect.end()

pandareaper•17mo ago

@spiritanand you could try create a connection pool instead of a single connection mysql.createPool Over time, idle connections will close automatically. I'm guessing the singleton connection that you are getting doesn't automatically reconnect, which also seems odd. A pool will manage that for you, automatically creating a new connection when it's needed. You could test this locally by running your app, restarting the DB and verifying that your app reconnects on it's own

Anand•17mo ago

yup it works. you are right. what I found werid was why the single connection was not behaving in the same way...

Gaming

Programming

Do I need to close the DB connection after running a script using drizzle?