Skip to main content

Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft

Official Websitehttps://www.microsoft.com/en-us/sql-server/sql-server-2019
TagsDatabaseSQL
Documentationhttps://docs.microsoft.com/en-us/sql/connect/node-js/node-js-driver-for-sql-server
NodeJS packagehttps://www.npmjs.com/package/mssql
Version10.0.2
Source Codehttps://github.com/tediousjs/node-mssql
Network Connection needs

This integration needs network access to the server where the service is running.

See the Network access page for details about how to achieve that.

Credential configuration​

To configure this credential, you need the host, port, username, password and database to connect to the server.

Optionally, you can set any of the extra config parameters you can see here.

Here is an example of a filled credential configuration form in YepCode:

SQL Server Snippets available in Editor​

note

The title is the triggering text for YepCode to autocomplete the script.

Integration​

New integration from credential
const mssqlConnectionPool = yepcode.integration.mssql('credential-slug')
New integration from plain authentication data
const mssql = require('mssql');

const mssqlConnectionPool = new mssql.ConnectionPool({
  user: 'sa',
  password: 'Pass@word',
  database: 'master',
  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
  }
});

Connect​

Connect
let pool;
try {
    pool = await mssqlConnectionPool.connect();
    await pool.query('SELECT GETDATE();');
} catch (err) {
    console.error(err);
} finally {
    pool && pool.close();
}

SELECT Text Only​

SELECT Text only
try {
    const result = await mssqlPool.query(
      'SELECT name, price FROM products'
    );
    result.recordset.forEach((row) => console.log(row));
} catch (err) {
    console.error(err);
}

SELECT Parameterized​

SELECT Parameterized
try {
    const { recordset, rowsAffected } = await mssqlPool
      .request()
      .input('name_filter', mssql.VarChar(50), 'awesome-product-name')
      .query('SELECT * FROM products WHERE name = @name_filter');
    console.log('Count:', rowsAffected[0]);
    recordset.forEach(({ name, price, stock, created_at: createdAt }) =>
      console.log(name, price, stock, createdAt)
    );
} catch (err) {
    console.error(err);
}

INSERT Text Only​

INSERT Text only
try {
    const { rowsAffected } = await mssqlPool.query(
      "INSERT INTO products(name, price, stock, created_at) VALUES('awesome-product-name', 14, 99, CURRENT_TIMESTAMP)"
    );
    console.log('Inserted rows:', rowsAffected[0]); // 1
} catch (error) {
    console.error(error)
}

INSERT Parameterized​

INSERT Parameterized
const mssql = require('mssql');

try {
    const { rowsAffected } = await mssqlPool
        .request()
        .input('name', mssql.VarChar(50), 'awesome-product-name')
        .input('price', mssql.Int, 14)
        .input('stock', mssql.Int, 99)
        .query(
        'INSERT INTO products(name, price, stock, created_at) VALUES(@name, @price, @stock, CURRENT_TIMESTAMP)'
        );
    console.log('Inserted rows:', rowsAffected[0]); // 1
} catch (error) {
    console.error(error)
}