How to create database connection with Drizzle ORM in Next.js app
In the previous post I have shown how to add drizzle to a project and create migrations. I'd like to extend this further, and show you how to initialize the connection and preserve it during hot reloads.
I'm using the node-postgres driver, but you can adjust it to any driver you want.
#How drizzle does that
In the drizzle docs, in the "Get started" section we can find this snippet of code, that allow us to connect with the database, by instantiating the Client and then wrap the client with drizzle, to be able to use it properly with drizzle API.
client.tsimport { drizzle } from 'drizzle-orm/node-postgres';
import { Client } from 'pg';
const client = new Client({
connectionString: 'postgres://user:password@host:port/db',
});
await client.connect();
const db = drizzle(client);
#The problem with official solution
I don't like this snippet. First off - we have the top-level await, so we have to modify our tsconfig to be able to even run this code. You'd probably be fine if you didn't await this connection, but that can lead to a race condition, where you may run a query with the client that's not connected yet. I think it's really unlikely, but not completely impossible.
Secondly - we don't know how to properly export this db instance, to use it in API, server component or server action. My best bet is that you just go with export const db = drizzle(client), and import it the standard way, but that's not always that obvious. Also, how does that work with top level await? How would Next.js handle that? I honestly don't know. I don't think I necessarily should know that. I would much rather just wrap it with a function that returns a database instance and use it that way in my code. I'd be much less concerned about how would my code act in a certain environment.
#My solution
With that in mind, I decided to write a little helper.
src/db/client.tsimport { Client } from 'pg';
import * as schema from './schema';
import { NodePgDatabase, drizzle } from 'drizzle-orm/node-postgres';
type Database = NodePgDatabase<typeof schema>;
let database: Database;
const initializeDatabase = async () => {
try {
const client = new Client({
connectionString: process.env.POSTGRES_URL,
});
await client.connect();
database = drizzle(client, { schema });
console.log('Database connection established');
} catch {
console.warn(
`Unable to connect with DB. process.env.POSTGRES_URL: ${process.env.POSTGRES_URL}`,
);
}
};
export const getDb = async (): Promise<Database> => {
if (!database) {
await initializeDatabase();
}
return database;
};
I started with declaring a top-level local variable called database and typing it correctly. This variable is declared with let keyword, so that I'd be able to assign it elsewhere. Else where? In the initializeDatabase function.
This function is basically a copy-paste from the Drizzle docs, but I wrapped it with a try-catch block and added the schema definition. I also added console.logs to make sure this function runs and with what result. This function also allows me not to use top-level await. Pretty sweat function, right?
So when is that function called? Whenever you want to use database in the code, and the database variable was not assigned any value yet - basically wasn't initialized with this function yet.
This logic happens in the getDb function, that is the only export from this file. Later in code you use it like that:
src/app/page.tsximport { getDb, posts } from '@/db'; // i have db/index.ts where I re-export useful stuff
import { desc } from 'drizzle-orm';
...
async function HomePage() {
// either gets the database value right away, or initializes it first
const db = await getDb();
// now we can use db the way the docs instruct us to
const latestPosts = await db
.select()
.from(posts)
.orderBy(desc(posts.createdAt))
.limit(5);
return (
<>
... some markup with latestPosts
</>
);
}
#The problem with my solution
This code is fine and all (surely still could be improved, more on that later), but there is one big problem and it stems from the hot reloading feature, that Next.js offers during development. Every time your application hot-reloads after you saved some changes to the codebase, this initializeDatabase function will re-run, because database local variable will be reset to undefined again. As a result, a new Client will be initialized every hot reload, without properly closing the old connections. That will, sooner or later, lead to exceeding max connections limit in your database.
That's pretty unfortunate. How can we fix that? Well, there's an option. It's the globalThis object. I have found this solution on one of the discussions in the Next.js GitHub repo and also in the Prisma docs.
#How to fix the hot-reloading re-instantiation problem?
The solution is that you don't create a local variable, like let database, but a global one instead. That way it will be persisted over hot-reloads, because the server's global object will remain unchanged, since we don't re-run the server. So how to do this? Very simply, actually. We just replace database with globalThis.database. Just take a look:
src/db/client.tstype Database = NodePgDatabase<typeof schema>;
// we define it only for type-safety, we could ignore it in JS
const globalForDrizzle = globalThis as unknown as {
database: Database;
}
const initializeDatabase = async () => {
try {
const client = new Client({
connectionString: process.env.POSTGRES_URL,
});
await client.connect();
globalForDrizzle.database = drizzle(client, { schema });
console.log('Database connection established');
} catch {
console.warn(
`Unable to connect with DB. process.env.POSTGRES_URL: ${process.env.POSTGRES_URL}`,
);
}
};
export const getDb = async (): Promise<Database> => {
if (!globalForDrizzle.database) {
await initializeDatabase();
}
return globalForDrizzle.database;
};
With that change, now our database will not exceed the limit of connections after a few dozens of hot-reloads (depending on the DB config). That's good!
#Possible simplifications
If you don't really feel like writing const db = await getDb() every time you want to access database, you may just call the initializeDatabase() function, with a small tweak (if-statement to avoid re-runs), in the top-level of this client.ts file and export globalForDrizzle.database as the db right away.
src/db/client.tstype Database = NodePgDatabase<typeof schema>;
// we define it only for type-safety, we could ignore it in JS
const globalForDrizzle = globalThis as unknown as {
database: Database;
}
const initializeDatabase = async () => {
if (globalForDrizzle.database) {
return;
}
try {
const client = new Client({
connectionString: process.env.POSTGRES_URL,
});
await client.connect();
globalForDrizzle.database = drizzle(client, { schema });
console.log('Database connection established');
} catch {
console.warn(
`Unable to connect with DB. process.env.POSTGRES_URL: ${process.env.POSTGRES_URL}`,
);
}
};
initializeDatabase();
export const db = globalForDrizzle.database;
I have tested this solution, and it seems to work just fine, but I still would not recommend it, for the sake of safety. Call me old-fashioned, but when I see that a function returns a Promise (as with the client.connect()), I want to await this Promise and only after it's resolved, use anything that it enables me to.