Close database connection every finished queries

[vzrenggamani]

Description

I'm building a NextJS application that uses email as an authentication system. I'm trying to do a production deployment on Netlify hosting. https://github.com/asaurusedu/educlient-mitraapp

I am using a MySQL database which I put on a database server hosting in my country. I have granted access via Remote MySQL so that my database can be accessed via any IP or hostname.

Problems

Every time I login using email, I always get an error message:

Task timed out after 10.01 seconds

Current Know Limitations & Why it is happen

After doing some simple research, I found out that Netlify limits the execution time of the function to just 10 seconds. I don't know why the execution time on NextAuth can exceed 10 seconds. I think nextauth persist the connection when requesting some queries into the database.

Netlify Functions Limitations

Possible Fixes and Enhancements

I think its better to close every database connection rather than creating new connection every user request in manner of persisting cookies check. I don't know if its is possible, just looking around for better solutions.

I would like to use database connection like : https://www.npmjs.com/package/serverless-mysql rather than convention mysql package.

Duct tape Solution

Recently, I use Firebase Firestore Database Instead MySQL that hosted on Indonesia. (Latency Issues)

Refers: asaurusedu/educlient-mitraapp#4 (comment)

Functions Logs (Using Firebase Firestore)

7:49:36 PM: 679c4909 INFO   [request] /api/auth/session
7:49:36 PM: 679c4909 Duration: 2.04 ms	Memory Usage: 121 MB	
7:49:48 PM: e69d9dda INFO   [request] /api/auth/session
7:49:48 PM: e69d9dda Duration: 1.82 ms	Memory Usage: 122 MB	
7:49:48 PM: 8c25b168 INFO   [request] /api/auth/providers
7:49:48 PM: 8c25b168 Duration: 3.27 ms	Memory Usage: 122 MB	
7:49:49 PM: 3a242cf7 INFO   [request] /api/auth/signin
7:49:49 PM: 3a242cf7 Duration: 2.36 ms	Memory Usage: 122 MB	
7:49:53 PM: aab06464 INFO   [request] /api/auth/signin/email
7:50:01 PM: aab06464 Duration: 8522.37 ms	Memory Usage: 122 MB	
7:50:02 PM: fa0a148d INFO   [request] /api/auth/verify-request
7:50:02 PM: fa0a148d Duration: 2.06 ms	Memory Usage: 122 MB

References

// pages/api/auth/[...nextauth].js 

import NextAuth from "next-auth";
import Providers from "next-auth/providers";

const options = {
  site: process.env.NEXTAUTH_URL,
  providers: [
    Providers.Email({
      server: {
        port: 465,
        host: process.env.EMAIL_HOST,
        secure: true,
        auth: {
          user: process.env.EMAIL_USERNAME,
          pass: process.env.EMAIL_PASSWORD,
        },
        tls: {
          rejectUnauthorized: false,
        },
      },
      from: process.env.EMAIL_FROM,
    }),
  ],
  database: {
    type: 'mysql',
    host: process.env.DB_HOST,
    username: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  },
  session: {
    jwt: true,
    maxAge: 24 * 60 * 60
  },
};

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

Thanks in advance.

[StefanSelfTaught]

You can create your own database adapter and use serverless-mysql: https://next-auth.js.org/tutorials/creating-a-database-adapter

I think Vercel has also 10s for the lambda execution time and it worked fine for me. You can also check this out #1477.

Maybe @balazsorban44 can help you more.

[vzrenggamani]

Based on @s-kris reply, it doesn't work, same as to me #1477 (reply in thread)