Dev mode hot reload loses database connection #26427
Replies: 26 comments 13 replies
-
|
👋 Hi! I believe this is expected unless you add custom I'll have one of our team members look into this to confirm, though. |
Beta Was this translation helpful? Give feedback.
-
|
how do you add custom module.hot handling? |
Beta Was this translation helpful? Give feedback.
-
|
Thanks @Timer! I was able to add Is there any documentation for adding |
Beta Was this translation helpful? Give feedback.
-
|
@seejamescode under further review this isn't possible right now. We'll tag this as a feature request, sorry about that! |
Beta Was this translation helpful? Give feedback.
-
|
@seejamescode: |
Beta Was this translation helpful? Give feedback.
-
|
Beta Was this translation helpful? Give feedback.
-
|
Overall, my issue was reading blog posts about database connections in serverless setups too fast. I was calling Feel free to keep the issue open if you think it would still be a beneficial feature request for others. Otherwise, I am fine with closing it for user error! |
Beta Was this translation helpful? Give feedback.
-
|
@seejamescode Can you add please a guide or an example with your approach? |
Beta Was this translation helpful? Give feedback.
This comment was marked as spam.
This comment was marked as spam.
-
|
Hey there, using Next.js 9.3, in dev mode, when an API route is rebuilt, and this API route references db.js where you initiate your database connection, then it will reconnect (and previous connections are just kept alive somehow indefinitely). When using knex/objection.js/node-postgres (trying to put keywords for SEO for people with similar issues) then you'll get the infamous "sorry, too many clients already". @Timer how do you handle that on your side? Every Next.js project using a database connection should have the issue at some point. I have not managed to find a way to express "Ignore this file for auto reload" when that file is a server side module (like lib/db.js) used by one of the API pages. Thanks! |
Beta Was this translation helpful? Give feedback.
-
|
Update: I was able to ignore a file to be reloaded by Next.js on change by using https://nextjs.org/docs/api-reference/next.config.js/custom-webpack-config and: webpackDevMiddleware(config) {
config.watchOptions.ignored.push(path.join(__dirname, "lib/db.js"));
return config;
},But the issue is still that connection between different routes is not reused, as each route re-runs the lib/db.js file which is confusing. In a regular express.js server every file require is cached. Weird! |
Beta Was this translation helpful? Give feedback.
-
|
Note: while I was able to reproduce this once, I tried again and was not able to get constant connection leaking so I guess we can just close this. There's definitely a bug somewhere, most probably not in Next.js though. If someone else ends up here let me know! |
Beta Was this translation helpful? Give feedback.
-
|
Ok I managed to pinpoint exactly the issue I have been facing with knex connection pooling, see here: knex/knex#3788 |
Beta Was this translation helpful? Give feedback.
-
|
@Timer Do you think it would be feasible to have an option to say "Do not clear require cache for this module" so that we can avoid bugs on some libraries in dev mode? Thanks! |
Beta Was this translation helpful? Give feedback.
-
|
Oh man, this one really got me. I’ve been using Prisma and Next.js and could not for the life of me figure out why periodically my database would error saying there were too many connections. I opened an issue on the Prisma client to figure it out. At some point I realized that the dev server was reloading the file where the Prisma client is instantiated, eventually creating multiple clients and thus too many connections. I tried @vvo’s recommendation of adding the file to This is one of those edge-casey situations where technically neither the database library nor Next are doing anything wrong, but in combination you get really unexpected results. I suspect the only way to really this would be with an official way to make Next ignore a file in dev mode? |
Beta Was this translation helpful? Give feedback.
-
|
You can do workaround like this meanwhile let prisma: PrismaClient
if (process.env.NODE_ENV !== 'production') {
if (!global.prisma) {
global.prisma = new PrismaClient({
debug: true,
})
}
prisma = global.prisma
} else {
prisma = new PrismaClient()
} |
Beta Was this translation helpful? Give feedback.
-
|
In my case, this solution does not work anymore in Next 13+ |
Beta Was this translation helpful? Give feedback.
-
|
@ChimUng you can check out my update using We have a demo app using this method that is using Next.js v15 ( |
Beta Was this translation helpful? Give feedback.
-
I have trouble with setting up NextJS v15. Do you have a repo for NextJS v14? |
Beta Was this translation helpful? Give feedback.
-
|
It's the same for v14, no changes were necessary for v15. But if you want to see a v14 example, we have other example repos: |
Beta Was this translation helpful? Give feedback.
-
|
@huv1k 🙌🏼🙌🏼🙌🏼 I completely forgot about |
Beta Was this translation helpful? Give feedback.
-
|
@huv1k you're the real MVP, I solved my issue this way too: const knex = require("knex");
let pg;
if (process.env.NODE_ENV === "development") {
global.pg =
global.pg ||
knex({
client: "pg",
connection: process.env.DATABASE_URL,
});
pg = global.pg;
} else {
pg = knex({
client: "pg",
connection: process.env.DATABASE_URL,
});
} |
Beta Was this translation helpful? Give feedback.
-
|
Had the same issue for the past 10 months while working on a large project with Next.js + Objection.js/Knex + PostgreSQL. /**
* @returns {import('knex')}
*/
const getConnection = () => {
if (PRODUCTION) return knex(DB);
/**
* This `global` hack solves a long-standing issue appearing in development more,
* when successive hot-reloads lead to connections being leaked and eventually produce
* an unrecoverable error
*/
/* eslint-disable no-underscore-dangle */
if (!global.__KNEX_DB_CONNECTION__) global.__KNEX_DB_CONNECTION__ = knex(DB);
return global.__KNEX_DB_CONNECTION__;
/* eslint-enable */
};
const db = getConnection();
export default db;Thanks a lot! 🙏🏻 |
Beta Was this translation helpful? Give feedback.
-
|
On further testing, doesn't appear to work with Duplicate connections from navigating back and forth between index and the same dynamic page (3 iSSG pages): |
Beta Was this translation helpful? Give feedback.
-
|
I'm also having a similar issue with Next.js where the database connections of Postgres.js start to accumulate during a development workflow (page reloading, Fast Refresh, etc), leading to the dreaded PostgreSQL error: @huv1k is the Here is our current workaround: import postgres from 'postgres';
declare module globalThis {
let postgresSqlClient: ReturnType<typeof postgres> | undefined;
}
// Connect only once to the database
// https://github.com/vercel/next.js/discussions/26427#discussioncomment-898067
function connectOnceToDatabase() {
if (!globalThis.postgresSqlClient) globalThis.postgresSqlClient = postgres();
return globalThis.postgresSqlClient;
}
export const sql = connectOnceToDatabase();Update (June 2024):
|
Beta Was this translation helpful? Give feedback.
-
|
I test your code. Every time I refresh the page, a new database connection will be created. 
|
Beta Was this translation helpful? Give feedback.
-
|
@nanzm,
Used connection count doesn't increase even as the function is repeatedly called Screen.Recording.2023-12-04.at.10.54.24.movConnecting without the Screen.Recording.2023-12-04.at.10.58.30.mov |
Beta Was this translation helpful? Give feedback.
-
@nanzm Do you have any official docs where this statement was made? Indeed this behavior of reconnecting to the database from a dynamic route despite the usage of the |
Beta Was this translation helpful? Give feedback.
-
|
The workaround in this thread doesn't prevent the database connection from reconnecting on every reload on dynamic routes/segments, even though the Screen.Recording.2023-12-04.at.10.54.24.movI don't know why the subsequent connection to the database doesn't increase the count but I found out that these subsequent connections don't go deep past this Sql function in These functions get called only once when the initial connection happens and don't get called on subsequent connections🤔 https://github.com/porsager/postgres/blob/6f20a4820c683b33e7670b606d8daf5670f4b973/src/connection.js#L326 Could this explain why the connection count doesn't accumulate? I have no other explanation
Screen.Recording.2023-12-05.at.15.16.33.mov |
Beta Was this translation helpful? Give feedback.
-
|
Sorry for not replying to you in a timely manner. Thank you for answering my question. Postgres.js may have some special mechanism to prevent the creation of a large number of connections within a short period of time. In my project, I’m using Prisma to connect to a MySQL database. Every time I refresh the page, a new connection is created, and I quickly get the error “Too many connections.” From my observation, this only happens in development mode because globalThis is reset every time the page is refreshed. Therefore, implementing a singleton pattern is not feasible in this case, and I haven’t found a solution yet. |
Beta Was this translation helpful? Give feedback.
-
|
@huv1k Actually, would the Next.js team be open to a new page in the docs about "Database Connections and Other Side Effects"? I would be open to writing a short guide on how to create singletons for this using |
Beta Was this translation helpful? Give feedback.
-
|
A bit late here but you guys all saved my life. I'm using Bull and each API call would create new Redis clients, so I had to restart the server frequently (like every 10 minutes) when developing because of memory leaks. I package all my instances in a const registerService = (name, initFn) => {
if (process.env.NODE_ENV === 'development') {
if (!(name in global)) {
global[name] = initFn();
}
return global[name];
}
return initFn();
};So that I can create unique instances of my services by simply calling: // src/server/services/index.js
export const db = registerService('db', () => knex({
client: 'pg',
connection: process.env.DATABASE_URL,
}));
// src/pages/api/...
import { db } from 'server/services';for example. I've spent so many hours trying to find a way to fix that. And this works perfectly. Thanks a lot! |
Beta Was this translation helpful? Give feedback.
-
|
For knex / objection, I've been setting things up in the const Knex = require('knex');
const { Model } = require('objection');
const {
PHASE_PRODUCTION_SERVER,
PHASE_DEVELOPMENT_SERVER,
} = require('next/constants');
const knexConfig = require('./knexfile.js');
const serverPhases = [PHASE_DEVELOPMENT_SERVER, PHASE_PRODUCTION_SERVER];
module.exports = (phase) => {
if (serverPhases.includes(phase)) {
const knex = Knex(knexConfig);
Model.knex(knex);
console.log('Initialized objection models...');
}
return {
reactStrictMode: true,
};
};with module.exports = {
client: 'mysql',
connection: {
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME,
},
migrations: {
directory: './src/models/migrations',
tableName: 'knex_migrations',
stub: './src/models/migrations/migration.stub',
},
};This works well for me, but I haven't been hammering my dev environment. So, I haven't had an occasion where I've run out of connections. I do get two I'd be interested if this worked for someone else -- or if there was a reason why I shouldn't do it this way! |
Beta Was this translation helpful? Give feedback.
-
|
it would be really great if there was a way to tell next to not clear a module from require.cache - per knex/knex#3788 (comment) something is possible with NextJsRequireCacheHotReloader, but I'm not sure how to actually override it. |
Beta Was this translation helpful? Give feedback.
-
|
The new Next.js 13 with the
During development, because of the Fast Refresh (hot reloading), the database connection count increases and causes the error when it reaches 90 and above I have the database connection in the const sql = postgres();export async function getFruits() {
const fruits = await sql`
SELECT * FROM foods
`;
return fruits;
}Importing the database query function import { getFruits } from './database';
export default async function RootLout() {
const fruits = await getFruits();
return (
<div>
{fruits.map((fruit) => (
<div key={fruit.id}>
<h1>{fruit.name}</h1>
<p>{fruit.description}</p>
</div>
))}
</div>
);
}The solution still remains the |
Beta Was this translation helpful? Give feedback.
-
|
The server-side process on hot-reload receives a SIGTERM you can use to clean up connections. Wouldn't call this solution ideal as it prevents work in progress during shutdown from writing to database; but it works, so lets get into it: import postgres from "postgres";
import { drizzle } from "drizzle-orm/postgres-js";
const connection = postgres(connectionOptions);
export const db = drizzle(connection, { schema, logger: DB_LOGGING });
// Clean up connections on hot reload or process exit
process.on("SIGTERM", async () => {
console.log("Database client disconnecting (SIGTERM)");
await connection.end();
});
process.on("SIGINT", async () => {
console.log("Database client disconnecting (SIGINT)");
await connection.end();
});If you're using a client that explicitly requires |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Bug report
Describe the bug
Loving v9! Just ran into an issue where I can’t use an existing database connection with the new API routes.
To Reproduce
mongooseConnect.js:pages/api/feature and requiresnext devmongooseConnect.jsand saveExpected behavior
The response will be the same. Instead, I get the following error:
This is because
dbreturns to{}with the HMR.System information
Additional context
Is there a different way we should be handling serverless database connections or is this a bug?
Beta Was this translation helpful? Give feedback.
All reactions