CODEWITHSUNDEEP
sql-servernode.jsapiexpressroutes
user2235420
user2235420

Reputation:

Multiple mssql connections in express REST API

I've made up a express server with a few routes. The plan was to give each route its own mssql connection via connection pooling, but it appears the module can only have one global connection. Am I doing something wrong here or is this intended?

Also, should I even do this if possible? Should I have one API per database? All of these database are on one engine and are seperate schemas.

Upvotes: 0

Views: 2069

Answers (2)

Tony
Tony

Reputation: 1

What if the two databases need to be queried together?

The mssql package allows you to access different databases using the table qualifier.

const result1 = pool.request().query("select * from DATABASE1..table1 t JOIN DATABASE2..table1 t2 ON t1.id = t2.id")

Upvotes: 0

Kris Lawton
Kris Lawton

Reputation: 169

The module allows you to set up connection pools which act as connections to databases as users. So if you want to connect to a different database, you have to use a different pool. For example:

const config1 = {
    user: 'username',
    password: 'password',
    server: 'server1',
    database: 'database1',
    options: {
        // ...
    }
}
const config2 = {
    user: 'username',
    password: 'password',
    server: 'server1',
    database: 'database2'
    // ...
}

const pool1 = new sql.ConnectionPool(conf1, err => {
    // ...
})
const pool2 = new sql.ConnectionPool(conf2, err => {
    // ...
})

Now when you want to access database1, you use pool1, and so on. For more info, refer to the mssql documentation. The theme here, though, is that if you want to access different databases, you must use different connection pools. Also be careful how you use the word "schema" around SQL Server. Schema in the general context of SQL refers to the structure of a database or table, but in Microsoft's SQL Server it can also refer to a feature that allows you to namespace tables inside a database for management, ownership and security purposes. See this technet article for a better explanation. As Larnu said in the comments, the hierarchy goes [database].[schema].[table]. Since connection pools access databases, you don't need to specify that bit – you can stick to [schema].[table] or even omit the schema depending on your setup.

Now as to whether you should separate out the pools, well obviously you have to if you're using more than one database. Otherwise, if it's the same database, I think we'd need more context and even then it's not black and white.

Just keep in mind, the connection pool is a user logging in to a database – it has the same privileges as that user. So do you want RouteA to only have access to certain database objects and ensure this is enforced database-side? Then set up a database user (RouteAUser), set up a connection pool for that route (PoolA), and inside that pool's config, use the RouteAUser's credentials, and so on. If all your data access is written inside the application, hidden from the end user, you probably don't need to do this – just write RouteA's code so that it only accesses the objects you want.

Upvotes: 2

Related Questions