Drizzle Team•11mo ago

Reusing db connection in lambda with Drizzle

I'm using rds mysql community as my DB and have been running into a connection limit problem when using server actions in Nextjs with prisma to interact with it(first image is the number of connections when I was burst testing), after searching a bit through the sst discord saw that someone recommended drizzle for better perfomance. When I looked through their docs I saw they mentioned a way to reuse the db connection: https://orm.drizzle.team/docs/perf-serverless But through their example I found it hard to understand how it's being shared and if that would help with my connection limit problem My connection looks like this:

const connection = await mysql.createConnection({
  host: env.MYSQL_HOST,
  user: env.MYSQL_USER,
  port: 3306,
  password: env.MYSQL_PASSWORD,
  database: env.MYSQL_DATABASE,
});

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

const connection = await mysql.createConnection({
  host: env.MYSQL_HOST,
  user: env.MYSQL_USER,
  port: 3306,
  password: env.MYSQL_PASSWORD,
  database: env.MYSQL_DATABASE,
});

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

And this is an example server action

"use server";

import { db } from "@/server/db";

export const getPosts = async () => {
  const posts = await db.query.posts.findMany();
  return posts;
};

"use server";

import { db } from "@/server/db";

export const getPosts = async () => {
  const posts = await db.query.posts.findMany();
  return posts;
};

Is there anything else I need to do so it can reuse the connection? Because just letting it like this isn't really working as you can see in the second image.

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

2 Replies

lutefdOP•11mo ago

I've tried something closer to their example modifying the config like this:

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

import { env } from "@/env";
import * as myschema from "./schema";

const connectionPromise = mysql.createConnection({
  host: env.MYSQL_HOST,
  user: env.MYSQL_USER,
  port: 3306,
  password: env.MYSQL_PASSWORD,
  database: env.MYSQL_DATABASE,
});
export const dbPromise = connectionPromise.then((connection) => {
  return drizzle(connection, {
    schema: myschema,
    mode: "default",
  });
});

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

import { env } from "@/env";
import * as myschema from "./schema";

const connectionPromise = mysql.createConnection({
  host: env.MYSQL_HOST,
  user: env.MYSQL_USER,
  port: 3306,
  password: env.MYSQL_PASSWORD,
  database: env.MYSQL_DATABASE,
});
export const dbPromise = connectionPromise.then((connection) => {
  return drizzle(connection, {
    schema: myschema,
    mode: "default",
  });
});

And the action like this:

"use server";
import { dbPromise } from "@/server/db";

export const getPosts = async () => {
  const db = await dbPromise; 
  const posts = await db.query.posts.findMany();
  return posts;
};

"use server";
import { dbPromise } from "@/server/db";

export const getPosts = async () => {
  const db = await dbPromise; 
  const posts = await db.query.posts.findMany();
  return posts;
};

But had no success with the db connection limit still being reached

lutefdOP•11mo ago

Also tried something more like this sst guide but no luck again Guide: https://sst.dev/examples/how-to-use-mongodb-atlas-in-your-serverless-app.html Code for the config:

import { type MySql2Database, drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { env } from "@/env";
import * as myschema from "./schema";

let cachedDbPromise: MySql2Database<typeof myschema> | null = null;

async function connectToDatabase() {
  if (cachedDbPromise) {
    return cachedDbPromise;
  }
  const connection = await mysql.createConnection({
    host: env.MYSQL_HOST,
    user: env.MYSQL_USER,
    port: 3306,
    password: env.MYSQL_PASSWORD,
    database: env.MYSQL_DATABASE,
  });
  cachedDbPromise = drizzle(connection, {
    schema: myschema,
    mode: "default",
  });
  return cachedDbPromise;
}

export const dbPromise = connectToDatabase();

import { type MySql2Database, drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { env } from "@/env";
import * as myschema from "./schema";

let cachedDbPromise: MySql2Database<typeof myschema> | null = null;

async function connectToDatabase() {
  if (cachedDbPromise) {
    return cachedDbPromise;
  }
  const connection = await mysql.createConnection({
    host: env.MYSQL_HOST,
    user: env.MYSQL_USER,
    port: 3306,
    password: env.MYSQL_PASSWORD,
    database: env.MYSQL_DATABASE,
  });
  cachedDbPromise = drizzle(connection, {
    schema: myschema,
    mode: "default",
  });
  return cachedDbPromise;
}

export const dbPromise = connectToDatabase();

SST

How to use MongoDB Atlas in your serverless app

In this example we will look at how to use MongoDB Atlas in your serverless app on AWS using SST. We'll be using the Api construct to create a simple API that gets a list of users.

Gaming

Programming

Reusing db connection in lambda with Drizzle