Работаем с SQL Server в Node.js (с использованием SQL запросов)
Node.Js поддерживает работу со всеми популярными системами управления базами данных. Microsoft SQL Server не исключение.
В репозитории npm существует целый ряд пакетов, которые позволяют работать с этой СУБД. Однако все их можно условно разделить на две большие группы в зависимости от реализуемого подхода.
- Непосредственная работа с базой данных c использованием SQL запросов;
- Сопоставление объектной модели и структуры базы данных посредство ORM.
В данной статье речь пойдёт о первом подходе.
Что такое nosql базы данных читайте в дополнительном материале
Установка клиента для SQL Server
Для того чтобы выполнить запрос к базе данных SQL Server в Node. js существует целый ряд библиотек. Мы воспользуемся библиотекой @frangiskos/mssql, которая основана на node-mssql и предоставляет достаточно простой интерфейс для работы.
Библиотека имеет открытый исходный код, который доступен на GitHub (https://github.com/frangiskos/mssql).
Установить её можно при помощи npm следующей командой:
Подключение к базе данных
Чтобы подключиться к базе данных необходимо подключить библиотеку к проекту и создать объект SqlConfig , в котором описаны параметры подключения.
Ниже приведён пример указанных операций:
Далее нужно передать объект SqlConfig в метод init объекта sql.
Библиотека @frangiskos/mssql работает с SQL Server по протоколу TCP. Поэтому предварительно убедитесь, что для экземпляра SQL Server, к которому вы планируете подключиться включен протокол TCP и открыты соответствующие порты.
Если подключение завершено успешно, можно выполнять запросы к базе данных при помощи метода query, но о нём позже.
Так как метод init ничего не возвращает, обработать ошибку подключения можно только при помощи блока try-catch.
Запрос на выборку
Запрос на выборку данных выполняется при помощи метода query/ Этот метод обычно принимает как минимум один параметр – строку с текстом запроса. Остальное — параметры запроса (о них мы поговорим позже).
Ниже показан пример, получения всех записей из таблицы:
sql . query ( ‘SELECT * FROM TestTable’ ) . then ( data = > console . log ( data ) ) . catch ( error = > console . error ( error ) ) ;
Метод query возвращает Promise. Поэтому данные мы можем получить только в методе then или при помощи async/await.
Записи из таблицы возвращаются в виде JSON массива, где каждый элемент представляет собой JSON объект с полями соответствующими структуре таблицы.
Например, для таблицы, которая включает поля id (целое число) и testValue (nvarchar), массив с результатами будет выглядеть приблизительно так:
Запрос без выборки данных
Если запрос не предполагает получения данных из базы ( UPDATE, INSERT, CREATE и т. д.), он выполняется аналогичным образом. Единственное отличие состоит только в том, что в этом случае нет необходимости обрабатывать возвращаемые данные в приложении. Поэтому, метод then можно использовать только для контроля успешного выполнения запроса.
Ниже показан пример запроса на выполнение хранимой процедуры:
Как мы видим при помощи библиотеки @frangiskos/mssql можно спокойно работать с SQL Server используя синтаксис Transact-SQL.
В приведённом примере есть один очень серьёзный подвох.
Значение входного параметра хранимой процедуры передаётся непосредственно в строке запроса, что чревато далеко идущими последствиями. Поэтому настоятельно рекомендуется в подобных случаях применять запросы с параметрами (параметрические запросы), речь о которых пойдёт ниже.
Параметрический запрос
Библиотека @frangiskos/mssql поддерживает параметрические запросы.
Для этой цели предусмотрен специальный синтаксис.
Параметры в тексте запроса обозначаются как @P и далее номер параметра (обязательно), начиная с 1.
Обратите внимание, что подобные имена параметров являются заерезервированными.
Значения параметров запроса передаются как параметры метода query ( начиная со второго параметра, первый всегда строка запроса).
Далее представлен предыдущий пример, который был изменён для использования параметрического запроса.
sql . query ( ` EXEC AddTestValue @ testValue = @ P1 ` , testValue ) . catch ( error = > console . error ( error ) ) ;
Метод query, ранее принимавший только один параметр, теперь принимает два. Строку запроса и значение параметра.
Количество используемых параметров не ограничено.
Сопоставление параметров запроса их значениям осуществляется слева направо. То есть, второй параметр метода query — @P1, третий — @P2, четвёртый — @P3 и т. д.
Например, такой запрос к рассмотренной ранее тестовой таблице вернёт не все пять значений, а только два.
sql . query ( ‘SELECT * FROM TestTable WHERE id >= @P1 AND id < @P2' , 2 , 4 ) . then ( data = >console . log ( data ) ) . catch ( error = > console . error ( error ) ) ;
Результат будет уже таким:
Специализированные методы
В принципе, изложенное выше, это всё, что необходимо знать для успешной работы с SQL Server в Node.js при помощи @frangiskos/mssql. Но, возможности библиотеки этим не ограничиваются.
Есть ещё два метода, которые вызываются аналогично query, но предназначены для частных случаев и потому используются значительно реже. Однако эти методы могут существенно упростить вам работу.
Первый из них queryOne — полный аналог query, но возвращает только первую из выбранных записей. Этот метод избавляет от необходимости использовать « TOP 1» в SQL запросах.
Второй insertReturnIdentity, предназначен для выполнения вставки данных в таблицу с возвратом идентификатора добавленной записи в случае успеха. Этот метод возвращает Promise. Поэтому, значение идентификатора доступно только в методе then или с использованием async/await.
Javascript and sql query
Для выполнения запросов у объекта подключения применяется метод query() . Наиболее простая его форма:
Где sqlString — выполняемая SQL-команда, а callback — функция обратного вызова, через параметры которой мы можем получить результаты выполнения sql-команды или возникшую ошибку.
Например, получим все данные из таблицы:
const mysql = require("mysql2"); const connection = mysql.createConnection(< host: "localhost", user: "root", database: "usersdb", password: "123456" >); connection.query("SELECT * FROM users", function(err, results, fields) < console.log(err); console.log(results); // собственно данные console.log(fields); // мета-данные полей >); connection.end(); В данном случае выполняется команда SELECT , которая извлекает все данные из таблицы «users». Функция обратного вызова принимает три параметра. Первый параметр передает ошибка, если она возникла при выполнении запроса. Второй параметр — results собственно представляет в виде массива те данные, которые получила команда SELECT. И третий параметр fields хранит метаданные полей таблицы и дополнительную служебную информацию.
Стоит отметить, что при выполнении запросов неявно устанавливается подключение, поэтому перед выполнением запроса нам в принципе необязательно у объекта подключения вызывать метод connect() .
Также в mysql2 определен метод execute() , который работает аналогичным образом:
const mysql = require("mysql2"); const connection = mysql.createConnection(< host: "localhost", user: "root", database: "usersdb", password: "123456" >); connection.execute("SELECT * FROM users", function(err, results, fields) < console.log(err); console.log(results); // собственно данные console.log(fields); // мета-данные полей >); connection.end(); Параметризация запросов
Если в запрос надо вводить данные, которые приходят извне, то для избежания sql-инъекций рекоммендуется использовать параметризацию.
При параметризации вместо конкретных данных в тексте запроса ставятся плейсхолдеры — знаки вопроса, вместо которых при выполнении запроса будут вставляться собственно данные. Например, добавление данных:
const mysql = require("mysql2"); const connection = mysql.createConnection(< host: "localhost", user: "root", database: "usersdb2", password: "123456" >); const user = ["Tom", 29]; const sql = "INSERT INTO users(name, age) VALUES(?, ?)"; connection.query(sql, user, function(err, results) < if(err) console.log(err); else console.log("Данные добавлены"); >); connection.end(); В данном случае данные определены в виде массива user, которая в качестве параметра передается в метод connection.query() . При выполнении запроса эти данные по порядку ставляются в запрос место вопросительных знаков. То есть фактически запрос будет выглядеть так: INSERT INTO users(name, age) VALUES(«Tom», 29)
Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью Node.js
Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и он не обязательно рекомендуется к использованию корпорацией Майкрософт. Другие примеры, в которых используются те же важные функции, можно получить на сайте GitHub:
Шаг 1. Подключение
Функция new Connection используется для подключения к базе данных SQL.
var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); >); connection.connect(); Шаг 2. Выполнение запроса
Все операторы SQL выполняются с помощью функции new Request() . Если оператор, например select, возвращает строки, их можно извлечь с помощью функции request.on() . Если строк нет, функция request.on() возвращает пустые списки.
var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement(); >); connection.connect(); var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; function executeStatement() < var request = new Request("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function(err) < if (err) < console.log(err);>>); var result = ""; request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < result+= column.value + " "; >>); console.log(result); result =""; >); request.on('done', function(rowCount, more) < console.log(rowCount + ' rows returned'); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); > Шаг 3. Вставка строки
В этом примере показано, как безопасно выполнить инструкцию INSERT и передать параметры для защиты от внедрения кода SQL.
var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement1(); >); connection.connect(); var Request = require('tedious').Request var TYPES = require('tedious').TYPES; function executeStatement1() < var request = new Request("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP);", function(err) < if (err) < console.log(err);>>); request.addParameter('Name', TYPES.NVarChar,'SQL Server Express 2014'); request.addParameter('Number', TYPES.NVarChar , 'SQLEXPRESS2014'); request.addParameter('Cost', TYPES.Int, 11); request.addParameter('Price', TYPES.Int,11); request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < console.log("Product id of inserted item is " + column.value); >>); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); >