nodejs-mssql switching database name using username and password login

[oukimhoit]

Supposed that we build nodejs with mssql

And we have multi users that using our application and also multi databases, each user must be connected to their own database , I want to check and verify with their username and password, more over I 've created one database name: database_headquarter to store other info like username, password, server_name, database_name, database_user, database_password). It's mean that the first login all users must be point to database_headquarter first , then check real using database in database_headquarter and after that point to their own database.

Please see in photo attachments.

Software versions

• NodeJS:
• node-mssql:
• SQL Server:

[dhensby]

I'm not sure exactly what you're asking.

What you're suggesting should be possible with some clever pool management. The only part that will be tricky is going to know when to close your pools for users and making sure you don't have too many connections to your database server open at one time.

[oukimhoit]

The problem is that I want switching to new database name according to their username and password (but I can access only 1 time of new Database name)

const sql_server = require('mssql');
const dotenv = require('dotenv');
dotenv.config();
let _pool;

const config= 
{
    user: process.env.DB_USER_HQ,
    password: process.env.DB_PASS_HQ,
    server: process.env.DB_HOST_HQ,
    database: process.env.DB_Database_HQ,      //  Switch to new database name (DB1 or DB2) depend on username and password login
    pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
    },
    options: {
      port: process.env.DB_PORT_HQ,
      encrypt: false,
      trustServerCertificate: false 
    }
}

async function getDatabaseName(get_database_name)
{
    try {
        const connection = await new sql_server.ConnectionPool(config).connect()
        let db_name = await connection.request()
            .input('username', sql_server.NVarChar(250), get_database_name.username)
            .input('password_user', sql_server.NVarChar(250), get_database_name.password_user)
            .execute('spLogin_HQ_User');
             sql_server.close();
             _pool = await new sql_server.ConnectionPool({  
                user: process.env.DB_USER_HQ,
                password: process.env.DB_PASS_HQ,
                server: process.env.DB_HOST_HQ,
                database: db_name.recordset[0].database_name,
                pool: {
                max: 10,
                min: 0,
                idleTimeoutMillis: 30000
                },
                options: {
                  port: process.env.DB_PORT_HQ,
                  encrypt: false,
                  trustServerCertificate: false 
                }}).connect();
            return db_name.recordset[0].database_name;
    } catch (error) {
        console.log(error);
        sql_server.close();
    }
}

[dhensby]

This is just an architectural issue for your app. You need a way to be able to store lots of pools and access them based on the user that is making the request to your app. At the moment you're just storing one in _pool. FYI, in your example code you're closing the global pool (sql_server.close() - which is not open) instead of the pool you have open (connection.close() - so this is remaining open).

If you see the docs around advanced pool management, you could use that code to create a set of pools you can access based on the user ID (or username).

As I said, the problem is how to close those pools when the user is no longer using your service so you don't end up with lots of database pools open when they aren't needed. One way to do that would be to use an LRU cache for the pools instead of a plain Map.

Also, some security advice:

1. Don't store user passwords in plain text in your database
2. Don't send user passwords in plain text to your database

To verify user passwords, you store them hashed in the database and then compare them in the application layer. If you send passwords to the database (even if it is doing the hash comparison) it's possible for the passwords to be logged in database query logs.

Likewise, I'd want to also encrypt the db passwords in a way that is also linked to the users password.

[oukimhoit]

I'm very new in node-mssql. But thanks for your feedback. And I have one more question, if we have 1000 users so will we also need 1000 connection pools?(1000 users refer to access to different 1000 databases name)

[dhensby]

Yes, in theory, but that will be far too many connection pools to keep open, which is why you will need to manage how you keep track of the connection pools. Presumably you won't have all your users using your service at the same time, so you'll be able to handle that.

That's where using an LRU cache could help you, though under load it could lead to a kind of thrashing where there are more concurrent users than pool capacity resulting in pools being repeatedly destroyed/created.

[oukimhoit]

And do you have another solution that can handle this hard point, and can access to different databases name in the same server from different users?

[dhensby]

My opinion is that this approach isn't scalable; you don't want a database per user at scale.

When you share a database, you only need one pool and that pool can be shared around any user of your application. So any current connection can be released and used by the application allowing your connections to persist between requests/users.

If you have a single database per user, you can't share database connections/pools like you would normally. If you have to have separate databases, then you have two options:

1. Database pools/connections are created & destroyed for every request you receive. This isn't terrible, it's how languages like PHP work, but it's just overhead you can remove in Node. This does keep thing simplest though (in terms of managing connection pools and keeping open connections to the DB server lower.
2. Or, as mentioned before, you create pools that persist across requests for each user, those pools can then drop out of use (get closed) if they aren't used for a while or if you start to hit a limit to how many pools/connections you're prepared to have open.

A problem with option 1, is that you'll need the user's username/password for every single request so that you can fetch the database credentials and create the pool, so that's something to think about too. However, this is all really outside the scope of this library and there's not much more help I can give you. The library is working as expected, you just need to think about your pool management to meet your needs.

[dhensby]

Closing as no reply / not a bug.