Skip to main content

Microsoft SQL Server

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

TagsDatabaseSQL
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)
}