Создание простого REST API для базы данных SQL-сервера
Это вторая версия статьи на тему создания REST API с дополнительными комментариями по исходной статье для перевода.
При работе с проектами по интеграции, для получения данных на сайт клиента, в CRM или мобильное приложение из базы данных под управлением MS SQL — реализуем стандартный REST API.
Проще всего создать такую интеграцию используя Node.js и два популярных npm-модуля Express (оснастка веб-сервера) и mssql (MS SQL Server клиент для Node.js).
Сначала создаем таблицы sales и invoices в базе данных SQL-сервера, процедуру для добавления записей в таблицу invoices и заполняем таблицу sales несколькими тестовыми записями:
create table sales(
id int primary key identity(1,1),
title varchar(255) null,
amount dec(12,2) not null,
clientId int not null,
date_create datetime DEFAULT (getdate())
);
go
create table invoices(
id int primary key identity(1,1),
idSale int not null REFERENCES sales(id),
date_create datetime DEFAULT (getdate())
);
go
create procedure addInvoices
(
@idSales int
) as
begin
insert into invoices (idSale)
output inserted.*
select id from sales where id = @idSales
end
go
insert into sales (title, amount, clientId) values
('Заказ 1', 100.80, 1),
('Заказ 2', 120.30, 2),
('Заказ 3', 78.11, 1);
go
Проверяем на SQL-сервере созданную таблицу и добавленные тестовые данные:
select * from sales
Переходим к созданию приложения в файле server.js добавляем код.
var express = require('express'); // оснастка веб сервера
var app = express();
var sql = require('mssql'); // клиент для MS SQL Server
// строка для подключения к базе данных.
var sqlConfig = {
user: 'UserName',
password: 'mot de passe',
server: 'localhost',
database: 'DatabaseName'
}
// сервер для http://localhost:8081/
var server = app.listen(8081, function () {
var host = server.address().address
var port = server.address().port
console.log("сервер доступен по url http://%s:%s", host, port)
});
После сохранения файла server.js проверим работоспособность сервера и выполнения файла скрипта:
node server.js
сервер должен вернуть сообщение о доступности для выполнения запросов, например:
сервер доступен по url http://localhost:8081
Добавим в файл server.js код обработки запроса к web-серверу для получения всех данных из таблицы SQL сервера sales.
app.get('/sales', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.query('select * from sales', function(err, resp) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
Сохраним файл, перезапустим сервер и проверим запрос в Postman, вернется JSON-объект с данными из таблицы SQL-сервера:
Усложняем запрос, добавим в обработчик параметр из URL для выборки по таблице invoices только запись с id = 2.
Передачу значения параметра из URL-запроса реализуем специальным отдельным методом — для исключения проблемы SQL-инъекций.
app.get('/sales/:id', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.input('input_parameter', sql.Int, Number(req.params.id)) // защита от SQL-инъекций и преобразование к числовому типу
.query('select * from sales where id = @input_parameter', function(err, resp) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
Результат выполнения запроса, возврат отдельной записи из таблицы в формате JSON:
В следующем обработчике запроса добавим в таблицу “invoices” запись с новым заказом, для REST это должен быть метод HTTP, тип запроса POST :
app.post('/sales/:id/invoices', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.input('idSales', sql.Int, Number(req.params.id)) // защита от SQL-инъекций
.execute('addInvoices', function(err, resp, returnValue, affected) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
Для тестирования запросов типа POST требуется установка в браузер дополнения, использования отдельного приложения, например Postman, или же запрос возможно выполнить при помощи curl, используя командную строку:
В результате выполнения запроса получаем json с данными о добавленной записи в таблице invoices. При повторном выполнении новый ID и дата добавления записи.
В результате скрипт приложения server.js следующего содержания:
var express = require('express'); // оснастка веб сервера
var app = express();
var sql = require('mssql'); // клиент для MS SQL Server
// строка для подключения к базе данных.
var sqlConfig = {
user: 'UserName',
password: 'mot de passe',
server: 'localhost',
database: 'DatabaseName'
}
// сервер для http://localhost:8081/
var server = app.listen(8081, function () {
var host = server.address().address
var port = server.address().port
console.log("сервер доступен по url http://%s:%s", host, port)
});
app.get('/sales', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.query('select * from sales', function(err, resp) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
app.get('/sales/:id', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.input('input_parameter', sql.Int, Number(req.params.id)) // защита от SQL-инъекций и преобразование к числовому типу
.query('select * from sales where id = @input_parameter', function(err, resp) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
app.post('/sales/:id/invoices', function (req, res) {
sql.connect(sqlConfig, function() {
var request = new sql.Request();
request.input('idSales', sql.Int, Number(req.params.id)) // защита от SQL-инъекций
.execute('addInvoices', function(err, resp, returnValue, affected) {
if(err) console.log(err);
res.json(resp.recordset); // результат в формате JSON
sql.close(); // закрываем соединение с базой данных
});
});
});
Дальнейшее дополнение скрипта приложения server.js это — включение в код обработчика ошибок, подключение к SQL через организацию пула соединений, обработка JSON встроенными функциями SQL-сервера.
Ссылка на источник для перевода и корректировки исходного кода статьи. Дополнительная информация по технологиям интеграции систем с использованием MS SQL-сервер — на сайте voInfo.ru.
MSSQL? Нода? "Проще всего"?
1. Большинство проектов от интернет-магазинов до персональных блогов использует MySQL, MongoDB, PostgreSQL, а не MSSQL.
2. REST само собой пишется на уже существующий стек, а не наоборот. И если проект написан на PHP, Java или GoLang - какой смысл тянуть ноду?
3. Компании, использующие не коробочные решения, работающие с MSSQL уже имеют специалистов, способных написать RESTful API с авторизацией, ограничениями и валидацией данных.
показан пример вызова процедуры, далее планировал рассмотреть варианты с передачей параметрами структуры json, использование out параметров, здесь возврат таблицы из процедуры
актуальна авторизация на клиента в http запросе или на сессию подключения к sql серверу? При подключении к sql пул запросов под общей УЗ, в базе сессионные хэши на пользователей http.
Да для клиента конечно. Или любой желающий сможет запросить информацию. А так, материал полезный. Немного ностальгии сразу свалилось, как раньше нужно было по быстрому апи накидать.
Еще как идея для статьи. Завернуть базу и сервис в докер-композ.
Спасибо за статью, думая она пригодится многим начинающим программистам. Тем не менее, не однозначны пара моментов в данной статье.
Первое, указание данных для подключения напрямую в server.js. Для этих целей лучше использовать модуль dotenv и настройки хранить в отдельном файле, так как это упрощает развёртывание на дополнительных машинах, а также спасёт от компрометации данных о БД в репозитории в гитхабе.
Второе, кажется излишним повторять несколько раз похожие запросы к БД в коде. Более удобным представляется написать собственный модуль-обёртку поверх mssql. Данный модуль мог бы заниматься однотипными запросами с БД, и который можно многократно использовать в server.js. В server.js можно оставить только callback' и с требуемой логикой. С ростом объёма кода, это должно сильно упростить обслуживание кода. В принципе, вместе с пулом соединений, обозначенном в конце статьи, мне кажется это первое, с чего вообще стоило начинать. Чтобы делать правильно сразу ;)
На дворе 2020, а где тогда GraphQL, ProtoBuff, http2, socket, схемы API, тесты, распределоенность, модульность, монолит/микросервис, параллелизм и прочие хештеги? ;)
Комментарий удален модератором
Комментарий удален модератором
MSSQL? Нода? "Проще всего"?
1. Большинство проектов от интернет-магазинов до персональных блогов использует MySQL, MongoDB, PostgreSQL, а не MSSQL.
2. REST само собой пишется на уже существующий стек, а не наоборот. И если проект написан на PHP, Java или GoLang - какой смысл тянуть ноду?
3. Компании, использующие не коробочные решения, работающие с MSSQL уже имеют специалистов, способных написать RESTful API с авторизацией, ограничениями и валидацией данных.
Комментарий недоступен
Вот это я понимаю - образец объективных и обоснованных ответов.
Хранимка вам зачем?
показан пример вызова процедуры, далее планировал рассмотреть варианты с передачей параметрами структуры json, использование out параметров, здесь возврат таблицы из процедуры
Автор, спасибо за проделанную работу!
Применимо в своём проекте ! Ещё раз благодарю 👍🏼
Читаем и пишем без авторизации? =)
актуальна авторизация на клиента в http запросе или на сессию подключения к sql серверу? При подключении к sql пул запросов под общей УЗ, в базе сессионные хэши на пользователей http.
Да для клиента конечно. Или любой желающий сможет запросить информацию. А так, материал полезный. Немного ностальгии сразу свалилось, как раньше нужно было по быстрому апи накидать.
Еще как идея для статьи. Завернуть базу и сервис в докер-композ.
Кстати, а нет информации, как эта БД нагрузку при большом количестве запросов держит? Скажем, при 100к запросов?
использовал технологию In-Memory tables, достаточно эффективна при большой нагрузке
Спасибо за статью, думая она пригодится многим начинающим программистам. Тем не менее, не однозначны пара моментов в данной статье.
Первое, указание данных для подключения напрямую в server.js. Для этих целей лучше использовать модуль dotenv и настройки хранить в отдельном файле, так как это упрощает развёртывание на дополнительных машинах, а также спасёт от компрометации данных о БД в репозитории в гитхабе.
Второе, кажется излишним повторять несколько раз похожие запросы к БД в коде. Более удобным представляется написать собственный модуль-обёртку поверх mssql. Данный модуль мог бы заниматься однотипными запросами с БД, и который можно многократно использовать в server.js. В server.js можно оставить только callback' и с требуемой логикой. С ростом объёма кода, это должно сильно упростить обслуживание кода. В принципе, вместе с пулом соединений, обозначенном в конце статьи, мне кажется это первое, с чего вообще стоило начинать. Чтобы делать правильно сразу ;)
Ну и отсутствие авторизации на сервере, конечно, но об этом писали и другие
На дворе 2020, а где тогда GraphQL, ProtoBuff, http2, socket, схемы API, тесты, распределоенность, модульность, монолит/микросервис, параллелизм и прочие хештеги? ;)
(сарказм)
Спасибо за статью, многим будет полезна.
up
Только почему на дворе 2020, а автор все ещё использует Express, а не гораздо более лаконичный Koa?