I'm trying to POST (register) some variables using query with nodejs / express and a SQL Server database, but I'm getting an error

TypeError: pool.query is not a function

I've already tried some solutions available but nothing working for me.

This is my dbconfig file:

const  pool = {
  user:  process.env.USER, // sql user
  password:  process.env.PASS, //sql user password
  server:  process.env.SERVER, // if it does not work try- localhost
  database:  process.env.DB,
  options: {
    encrypt: false,
    trustServerCertificate: true,
    instancename:  'MSSQLSERVER'  // SQL Server instance name
  },
  port:  1433
}

module.exports = pool;

My controller:

const pool = require("../../config");

module.exports = {
  create: (data, callback) => {
    pool.query(`insert into testRegistration (userName, password, email)
    values(?,?,?)`,
    [
      data.userName,
      data.password,
      data.email
    ],
    (error, results, fields) => {
      if(error){
        return callback(error)
      }
      return callback(null, results)
    }

);
}}; 

Looking for suggestions and possible solution for this, any help would be grateful

CodePudding user response：

You should first initialize your pool with the ConnectionPool method:

const sql = require('mssql');

const pool = new sql.ConnectionPool({
  user:  process.env.USER, // sql user
  password:  process.env.PASS, //sql user password
  server:  process.env.SERVER, // if it does not work try- localhost
  database:  process.env.DB,
  options: {
    encrypt: false,
    trustServerCertificate: true,
    instancename:  'MSSQLSERVER'  // SQL Server instance name
  },
  port:  1433
});

module.exports = pool;

Then, connect to the database before executing your query:

const pool = require('../../config');
const sql = require('mssql');

module.exports = {
  create: (data, callback) => {
    pool
      .connect()
      .then(function (dbPool) {
        const ps = new sql.PreparedStatement();
        ps.input('userName', sql.VarChar());
        ps.input('password', sql.VarChar());
        ps.input('email', sql.VarChar());
        ps.prepare(
          'insert into testRegistration (userName, password, email) value (@userName, @password, @email)',
          (err) => {
            if (err) return callback(err);
            ps.execute(
              {
                userName: data.userName,
                password: data.password,
                email: data.email,
              },
              (err, results) => {
                if (error) {
                  return callback(error);
                }
                return callback(null, results);
              }
            );
          }
        );
      })
      .catch(function (err) {
        console.error('Error creating connection pool', err);
      });
  },
};

For more, check the official docs.