MariaDB deselects database after query (NODEJS)-CodePudding

I am currently implementing a small personal project on my raspberry. My webapp sends a POST request to my backend which queries the database. The first query is always fine, but at the second query the database isn't selected anymore and I get an error. As proof the image below shows two sequential 'SELECT DATABASE()' queries.

I split the code in a main, server and db file so that my code is clean.

// db.js
require('dotenv').config();


const mariadb = require('mariadb');
const pool = mariadb.createPool({
     host: 'demez.asuscomm.com', 
     user: process.env.MARIADB_USER, 
     password: process.env.MARIADB_PW,
     connectionLimit: 5
});

// connect()
async function connect() {
    try {
        await pool.getConnection();
        // select default db
        await pool.query('USE athletepresencelist');
    } catch (err) {
        console.log(err);
    }
}

// query()
async function querydb(query) {
    try {
        // await pool.query('USE athletepresencelist');
        return await pool.query(query);
    } catch (err) {
        console.log(err);
    }
}

module.exports = {
    connect, querydb
}

As you can see, as soon as I connect to MariaDB, I also select the database, but as said at the second query using the 'querydb()' function from outside the db.js file, it deselcts the db I selected before. Any suggestions why it is doing that and how I can fix it?. I already tried to put a USE db before the actual query but it doesn't work

CodePudding user response：

Problem

The problem is that your connection pool is by default not "using" your database. When you create a database connection pool, it will create some number of connections to the database, each having their own state. When you do the USE database; in the connect function, that may only be being run in a single one of those connections, leaving out all the other connections. Various database pool implementations handle how the connections are used, but it is safe to assume that is what is happening here.

Solution

You can fix this quite easily by modifying how you connect to the database:

// db.js
require('dotenv').config();


const mariadb = require('mariadb');
const pool = mariadb.createPool({
     host: 'demez.asuscomm.com', 
     database: 'athletepresencelist',
     user: process.env.MARIADB_USER, 
     password: process.env.MARIADB_PW,
     connectionLimit: 5
});

// connect()
async function connect() {
    try {
        await pool.getConnection();
    } catch (err) {
        console.log(err);
    }
}

// query()
async function querydb(query) {
    try {
        return await pool.query(query);
    } catch (err) {
        console.log(err);
    }
}

module.exports = {
    connect, querydb
}

One thing to note here is that this assumes that your database already exists before you are connecting to it.