Connecting to a Database

Overview


For security reasons, web browsers usually cannot connect directly to a database. (For information about connecting to a database from Javascript running outside the browser, please see node database) The best way to retrieve data from a database is to have a webserver act as a proxy which will pass on any query issued by the browser to the database and return the results to the calling browser.

The query is typically passed as a parameter in the HTTP POST. Using the $url service, the following code would demonstrate passing a query in the POST parameters: 


let data = await $url({
  url:'http://proxy-server/',
  data:{
    query:`select * from Table where age > 20;`					
  }
});

Sample Webserver


The details of connecting to any particular database is dependent on the database. This example shows how to connect to Mysql. 


const mysql = require('mysql2/promise'); // Using the Promise-based API

const pool = mysql.createPool({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

app.post('/data/', async (req, res) => {
    try{
        let query = req.body.query;
        const [rows] = await req.pool.execute(query);
        res.json(rows);
    }
    catch(err){
        res.status(500).send('Error');
    }
});

Streaming the Response


As an alternative to the above, you can stream the response as opposed to sending the response all at once. This can be useful for large datasets. 



function stream(sqlQuery, req, res){
    pool.getConnection((err, connection) => {
        if (err) {
            return res.status(500).json({ error: 'Failed to connect' });
        }

        res.setHeader('Content-Type', 'application/json');
        res.write('[');
        const queryStream = connection.query(sqlQuery).stream();
        let comma = '';
        // 3. Process the stream
        queryStream.on('data', (row) => {
            let str = comma + JSON.stringify(row);
            comma = ','
            res.write(str);
        });

        queryStream.on('end', async () => {
            res.write(']');
            res.end();
            connection.release(); // Release the connection
        });

        queryStream.on('error', (err) => {
            res.status(500).json({ error: 'Failed to stream data' });
            connection.release();
        });
    });
}

When the dataset is large and streamed in this manner, it may be necessary to use the streaming facilities on the client side. (see streaming large data and dealing with large data )