I'm have a bad experience using mssql package which is return the connection as a promise so to be able to use a method like query
I should to first wait for connection then use it something like
const sql = require('mssql')
const sqlConfig = {
user: process.env.DB_USER,
password: process.env.DB_PWD,
database: process.env.DB_NAME,
server: 'localhost',
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000
},
options: {
encrypt: true, // for azure
trustServerCertificate: false // change to true for local dev / self-signed certs
}
}
sql.connect(config).then(async pool => {
const query = await pool.query`select * from table_name`;
console.log(query)
})
But certainly I won't use that connection in a single file I want to export it to use it in a different routes files so for that case I found 2 solutions who isn't the best practice or not a good programming experience in my opinion first is introduced in the package docs which is connect to the SQL server first then run your node server and attach the connection in the app locals and I think it's really so bad solution it's stopping the whole server on the database connection what if the connection failed that's means that the whole server won't start even if there's a static files that doesn't require a database connection the user won't be able to access it anymore here's the solution provided
const express = require('express')
const sql = require('mssql')
const config = {/*...*/}
//instantiate a connection pool
const appPool = new sql.ConnectionPool(config)
//require route handlers and use the same connection pool everywhere
const route1 = require('./routes/route1')
const app = express()
app.get('/path', route1)
//connect the pool and start the web server when done
appPool.connect().then(function(pool) {
app.locals.db = pool;
const server = app.listen(3000, function () {
const host = server.address().address
const port = server.address().port
console.log('Example app listening at http://%s:%s', host, port)
})
}).catch(function(err) {
console.error('Error creating connection pool', err)
});
So to access it I will use something like the following
const express = require('express');
const router = express.Router();
router.get('/', async (req, res, next) => {
req.app.locals.db.query('SELECT TOP 10 * FROM table_name', function(err, recordset) {
if (err) {
console.error(err)
res.status(500).send('SERVER ERROR')
return
}
res.status(200).json({ message: 'success' })
})
})
That was the first solution and the second solution that I found is exporting the connection and in each time I want to use something like query
method I can't call it directly I should to repeat a static step who is const pool = await connection; const query = await connection.query('my query string')
in fact that solution is better than the first but there's a step I should to repeat it in each time I think that this isn't a good programmer experience here's the code example
const sql = require('mssql');
const { normalizePort } = require('./handlers');
const {
SQL_SERVER_USER,
SQL_SERVER_PASSWORD,
SQL_SERVER,
SQL_SERVER_DATABASE,
SQL_SERVER_PORT
} = require('./configurations/appConfig');
const config = {
user: SQL_SERVER_USER,
password: SQL_SERVER_PASSWORD,
database: SQL_SERVER_DATABASE,
server: SQL_SERVER,
port: normalizePort(SQL_SERVER_PORT),
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000
},
options: {
encrypt: true,
trustServerCertificate: true, // change to true for local dev / self-signed certs
}
}
const connection = sql.connect(sqlConfig)
module.exports = connection;
Let's assume that the filename of the code above is db.js
and then in a route file which is called production.js
there's 3 routes so let's import the connection above and use it in that route
const express = require('express');
const router = express.Router();
const connection = require('../db.js')
router.get('/products', async (req, res, next) => {
const pool = await connection;
const query = await pool.query`select * from products`;
res.setHeader("Content-Type", 'application/json; charset=utf-8');
res.status(200).send(JSON.stringify(query, null, 4));
});
router.post('/brands', async (req, res, next) => {
const pool = await connection;
const query = await pool.query`select * from brands`;
res.setHeader("Content-Type", 'application/json; charset=utf-8');
res.status(200).send(JSON.stringify(query, null, 4));
});
router.post('/categories', async (req, res, next) => {
const pool = await connection;
const query = await pool.query`select * from categories`;
res.setHeader("Content-Type", 'application/json; charset=utf-8');
res.status(200).send(JSON.stringify(query, null, 4));
});
module.exports = router
In the example above in each route I should to call const pool = await connection;
that repetitive code I think it's not the best practice and not a good programmer experience I'm looking for a better solution where I can import the connection and directly access the query
method without extra steps.
1条答案
按热度按时间zz2j4svz1#
Seems like you have a hard time using mssql module after some search i thought what if i have a function that's returning an object and that object contains a function called
query
but just wait thequery
function already doesn't exist and that function won't return it you have to wait some time until loading that object that's containsquery
method then you can called thequery
method and execute it so if you called that function dot query it's will be undefined and if you invoked it you will get a pretty error who isTypeError: function_name(...).query is not a function
to fix that problem you can useProxy
object so when your function invoked it's will directly return a newProxy
object from that proxy object you can access the called method name then return a function to access the arguments something like the following basic examplein the example above we called
sum
method who isn't included in the returned result but it's already exist in theobj
so when calling theproxyfire().sum(1)
theproxyfire
method will work as a middleware or brige between theobj
and your invoked method and you can access the called method name and it's arguments by returning a new function we can use the example above to handle your problem of repeatingawait connection
for many times let's take a look and use one of your examples then add theproxyfire
on it who will work as a bridge between your direct invoking and the waiting untill the connection is already done and ready to callquery
methodthe file above is called
db.js
and we want to import and use it inproduction.js
route so let's use itas you can see you can directly call
query
method and receive your query response all you need is just to callproxyfire
method at the top of your route then use it as you wishhere's a complete usage example
by this example you don't need even to call
proxyfire
at the top of your route module just require and use it