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
Documentationhttps://docs.microsoft.com/en-us/sql/connect/node-js/node-js-driver-for-sql-server
NodeJS packagehttps://www.npmjs.com/package/mssql
Version9.0.1
Source Codehttps://github.com/tediousjs/node-mssql
TagsDatabase, SQL
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 params you can see here.

Here you have 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)
}
Last updated on