Next.js Discord

Discord Forum

Pool connection api routes

Unanswered
Николя posted this in #help-forum
Open in Discord
НиколяOP
Hello everyone, I have a problem too many connection to my db.
Last time they told me that I didn’t close the connections, I decided to use the pool for this, but now this error has appeared again, that there are too many connections, why could this be? How to fix it?

3 Replies

НиколяOP
const mysql = require('mysql');

// Создание пула соединений
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'SonyaRyKo2023',
    database: 'anime',

    connectionLimit: 500, // Максимальное количество соединений в пуле
    queueLimit: 0,       // Максимальное количество запросов в очереди (0 - без ограничений)
    acquireTimeout: 10000,
    waitForConnections: true,
});

// Проверка соединения (опционально)
pool.getConnection((err, connection) => {
    if (err) {
        console.error('Error connecting to MySQL database:', err);
    } else {
        console.log('Connected to MySQL database');
        connection.release(); // Освобождаем соединение обратно в пул
    }
});

module.exports = pool;
Spectacled bear
Could be: This function is getting executed infinite times
@Spectacled bear Could be: This function is getting executed infinite times
НиколяOP
I think not, here's an example api routes
api/data
import pool from "@/db";

export async function GET() {
    try {
        // Получаем соединение из пула
        const connection = await new Promise((resolve, reject) => {
            pool.getConnection((err, connection) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(connection);
                }
            });
        });

        // Выполняем запрос с использованием полученного соединения
        const results = await new Promise((resolve, reject) => {
            connection.query('SELECT * FROM series', (err, results) => {
                // Освобождаем соединение обратно в пул после выполнения запроса
                connection.release();
                if (err) {
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // console.log(results);
        return NextResponse.json(results);
    } catch (error) {
        return NextResponse.json(
            { message: error },
            {
                status: 500
            }
        );
    }
}



api/choose/[id]
// import { NextResponse } from "next/server";
// import pool from "@/db";

// export async function GET(request, { params }) {
//     const id = params.id;

//     try {
//         const results = await new Promise((resolve, reject) => {
//             pool.getConnection((err, connection) => {
//                 if (err) {
//                     reject(err);
//                 } else {
//                     connection.query(`SELECT * FROM series WHERE ID = ?`, [id], (err, results) => {
//                         connection.release(); // Освобождаем соединение обратно в пул
//                         if (err) {
//                             reject(err);
//                         } else {
//                             resolve(results[0]);
//                         }
//                     });
//                 }
//             });
//         });
//         return NextResponse.json(results);
//     } catch (error) {
//         return NextResponse.json(
//             { message: error },
//             {
//                 status: 500
//             }
//         );
//     }
// }
import { NextResponse } from "next/server";
import pool from "@/db";
import { notFound } from "next/server";

export async function GET(request, { params }) {
    const id = params.id;

    try {
        const results = await new Promise((resolve, reject) => {
            pool.getConnection((err, connection) => {
                if (err) {
                    reject(err);
                } else {
                    connection.query(`SELECT * FROM series WHERE ID = ?`, [id], (err, results) => {
                        connection.release(); // Освобождаем соединение обратно в пул
                        if (err) {
                            reject(err);
                        } else {
                            resolve(results[0]);
                        }
                    });
                }
            });
        });

        if (!results) {
            return NextResponse.json(
                { message: 'Серия не найдена' },
                {
                    status: 404
                }
            );
        }

        return NextResponse.json(results);
    } catch (error) {
        return NextResponse.json(
            { message: error },
            {
                status: 500
            }
        );
    }
}