Where to close db connection manually?

[abcd-ca]

Your question
I am exceeding my database connections limit. Where can I destroy my database connection manually? Is one of the callbacks the right place? If so which one?

What are you trying to do
I'm using knex.js with PostgreSQL and Next.js API routes which at runtime are lambdas and as such, manually destroying the database connection is necessary. I am going to do this for my other API routes and I assume it is also necessary for my [...nextauth].ts route. I'm just not sure where I can do this for next-auth api calls. What I need to do in my other API routes is in a finally block, put await knex.destroy() and I'm looking for where to put that in the next-auth route.

Reproduction

Feedback
Documentation refers to searching through online documentation, code comments and issue history. The example project refers to next-auth-example.

• Found the documentation helpful
• Found documentation but was incomplete
• Could not find relevant documentation
• Found the example project helpful
• Did not find the example project helpful

my next-auth route:

import NextAuth, {InitOptions, User} from 'next-auth'
import Providers from 'next-auth/providers'
import {PostgresConnectionOptions} from 'typeorm/driver/postgres/PostgresConnectionOptions'
import {GenericObject} from 'next-auth/_utils'
import {getUserRole, userIsOnWhitelist} from '../../../src/server/userHelper'
import {MyProjectSession} from '../../../src/model/MyProjectSession'

const nextAuthDBConnectionOptions: PostgresConnectionOptions = {
  type: 'postgres',
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  username: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  entityPrefix: 'nextauth_',

  //https://github.com/nextauthjs/next-auth/issues/822#issuecomment-782037934
  ssl: true,
    extra: {
      ssl: {
        rejectUnauthorized: false
      },
  
      // pool size
      min: 0,
      max: 1,
  
      idleTimeoutMillis: 3000,
      connectionTimeoutMillis: 1000
  },
  poolErrorHandler: (error) => console.warn("next-auth typeorm poolErrorHandler error: ", error)
}

const options: InitOptions = {
  // Configure one or more authentication providers
  providers: [
    Providers.Email({
      server: {
        host: process.env.EMAIL_SERVER_HOST,
        port: Number(process.env.EMAIL_SERVER_PORT),
        auth: {
          user: process.env.EMAIL_SERVER_USER,
          pass: process.env.EMAIL_SERVER_PASSWORD
        },

      },
      from: process.env.EMAIL_FROM,
    }),
    // ...add more providers here
  ],

  // A database is optional, but required to persist accounts in a database
  database: nextAuthDBConnectionOptions,

  // ref: https://next-auth.js.org/configuration/callbacks
  callbacks: {
    signIn: async (user: User, account: GenericObject, profile: GenericObject) => {
      // You can also Reject this callback with an Error or with a URL:
      // return Promise.reject(new Error('error message')) // Redirect to error page
      // return Promise.reject('/path/to/redirect')        // Redirect to a URL
      // Return false to display a default error message
      console.log("*** signIn callback")
      try {
        return await userIsOnWhitelist(user.email)

      } catch (error) {
        console.log("*** signIn callback error: ", error)
        return false;

      } finally {
        // await knex.destroy();
      }
    },

    session: async (session: MyProjectSession, user: User): Promise<GenericObject> => {
      /*
        If you need to persist session data server side, you can use the accessToken returned
        for the session as a key - and connect to the database in the session() callback
        to access it. Session accessToken values do not rotate and are valid as long
        as the session is valid.
       */
      console.log("*** session callback")
      try {
        // support authorization
        session.role = await getUserRole(user.email);

      } catch (error) {
        console.log("*** session callback error: ", error)

      } finally {
        // await knex.destroy();
      }

      return session
    },

    async redirect(url, baseUrl) {
      console.log("*** redirect callback")
      // // await knex.destroy()
      return baseUrl
    },

    async jwt(token, user, account, profile, isNewUser) {
      console.log("*** jwt callback")
      // await knex.destroy()
      return token
    }
  },

  // if omitted next-auth will provide default views. These are here for customized versions
  // ref: https://next-auth.js.org/configuration/pages
  pages: {
    signIn: '/auth/signin', // Customized sign-in view
    error: '/auth/error', // Error code passed in query string as ?error=
  }
}

export default (req, res) => NextAuth(req, res, options)

[balazsorban44]

You can just close the connection right after you have done all the interactions you needed?

Some code example would help us to help you.

[abcd-ca]

updated the extra section. Still no luck. Seems to only be an issue when my users are signed in which points me to nextauth. The rest of my site uses a knex.js connection and that one seems ok with the same connection pool settings. I can watch the number of connections grow just for a few seconds then they go back down. Once user authenticates, idle connections remain in db for quite a while before going away on their own.

[paulwongx]

@abcd-ca How did you go about resolving this. I'm experiencing the same issue now.

[andrew-cfx]

I refactored my app by moving the back-end api to a remote server, that is not serverless and not in Vercel/Next. Another option could have been a service like Supabase. Cloudflare has a service called Hyperdrive that might help. Or you could have your db in AWS RDS and add AWS Proxy.

[paulwongx]

I ended up with something like this which fixed the issue for me.
Link to article

// Avoids maxing out connections in postgres due to NextJS rebuilding API routes
// https://dev.to/noclat/fixing-too-many-connections-errors-with-database-clients-stacking-in-dev-mode-with-next-js-3kpm
const registerService = (name: string, initFn: () => any) => {
	if (process.env.NODE_ENV === "development") {
		if (!(name in global)) {
			global[name] = initFn();
		}
		return global[name];
	}
	return initFn();
};

export const pgClient = registerService("db", () =>
	postgres(process.env.POSTGRES_DB_URL, {
		ssl: { rejectUnauthorized: false },
	})
);

export const db = drizzle(pgClient, { schema });

[merthanmerter]

thats how I handle it for now;

//* In-memory store for database connections
const store = new Map<string, mysql2.Connection>();

//* Utility function for database connection handling
async function createConnection(dbName: string) {
  const exconn = store?.get(dbName);
  if (exconn && (await ping(exconn))) {
    return exconn;
  }

  const conn = await mysql2.createConnection({ ...masterDbOptions, database: dbName });
  store.set(dbName, conn);
  return conn;
}

//* Function to establish shared database connection
export const sharedDb = async () => {
  if (!zenv?.DATABASE_NAME) return null;
  const connection = await createConnection(zenv.DATABASE_NAME);
  return drizzle(connection, { schema, mode: 'default' });
};

//* Function to establish tenant database connection with authorization
export const tenantDb = async ({ request, permissions }: { request: Request; permissions?: UserPermissions[] }) => {
  const { user, tenant } = await authenticate(request, { permissions });
  const connection = await createConnection(`tenant_${tenant}`);
  const conn = drizzle(connection, { schema: tenancySchema, mode: 'default' }) as MySql2Database<
    typeof tenancySchema
  > & { ctx: { user: string; tenant: string } };
  conn.ctx = { user: user.username, tenant };
  return conn;
};

//* ping function to check if server is alive
const ping = async (conn: mysql2.Connection): Promise<boolean> =>
  conn
    ?.ping()
    .then(() => true)
    .catch(() => false);