Hoje vou ensinar como que você pode usar Node.js com MS SQL Server. Não, essa não é uma dupla muito comum de ver no mercado, o mais normal é que Node.js seja utilizado com MongoDB, ou com MySQL. Eu cheguei a fazer umas pesquisas de tendências antes de escrever este post e MySQL é em média 10x mais utilizado com Node.js. No entanto, recentemente tive de fazer uma API que se comunicava com SQL Server e isso me fez descobrir que esse é um assunto pouco comentado na Internet, então resolvi deixar aqui este tutorial para quem precisar mais tarde (incluindo eu).
Veremos neste artigo (pule seções à vontade):
- Configurando o Ambiente
- Criando e populando a tabela
- Criando a API
- Criando a listagem de clientes
- Criando a pesquisa de um cliente
- Excluindo um cliente
- Adicionando um cliente
- Atualizando um cliente
- Bônus 1: Executando muitas operações SQL
- Bônus 2: ORM
Então vamos lá!
Parte 1: Configurando o Ambiente
Se você já possui um banco de dados SQL Server, ok, use ele e pule para a próxima parte.
Se você não possui um banco SQL Server, você tem duas opções:
- baixar e instalar o MS SQL Server Express (gratuito) na sua máquina;
- contratar um banco SQL Server na nuvem (pago);
A primeira opção é um pouco mais trabalhosa, porém é free. Baixe, instale e crie um novo banco de dados para uso nesse tutorial. Durante a instalação, você irá precisar definir uma senha para o usuário ‘sa’, não esqueça dela.
A segunda opção é bem simples, mais vai te custar alguns pilas e envolve usar algum serviço profissional de SQL Server em um provedor de hospedagem.
Além de um banco de dados SQL Server, você vai precisar ter o Node.js instalado na sua máquina e um editor de código como o Visual Studio Code. Ensino a instalar ambos no vídeo abaixo, caso precise de ajuda no processo.
Parte 2: Criando e populando a tabela
Agora que você já tem o banco pronto, vamos criar uma tabela nele e colocar alguns dados de exemplo. Não pule esta etapa pois vamos fazer tudo isso usando Node.js!
Crie uma pasta para guardar os arquivos do seu projeto Node.js, você pode fazer isso pelo console se quiser, usaremos ele algumas vezes nesse tutorial. No exemplo abaixo, criei a pasta e depois entrei dentro dela, inicializando um projeto Node.js com o npm init.
|
1 2 3 4 5 |
mkdir nodesqlserver cd nodesqlserver npm init -y |
Com o projeto criado, vá no console novamente, ainda na pasta do projeto e digite o seguinte comando para instalar a extensão mssql, que permite usar Node com MS SQL Server, mas também outras duas que vamos precisar.
|
1 2 3 |
npm install mssql express dotenv |
Se você abrir o arquivo package.json vai ver que tem coisa nova por lá.
Uma das extensões que instalamos foi a DotEnv, que serve para gerenciar configurações da nossa aplicação (também chamadas de variáveis de ambiente). Para usarmos o dotenv precisamos primeiro criar um arquivo com o nome de .env, na pasta do projeto, com o seguinte conteúdo.
|
1 2 3 4 |
CONNECTION_STRING=Server=XXX;Database=XXX;User Id=XXX;Password=XXX; PORT=3000 |
A primeira configuração diz respeito à string de conexão com o banco de dados, no formato que mostrei acima, substituindo os “XXX” pelos valores reais do seu servidor. Se você já usou SQL Server com C#, a string é a mesma. Já a segunda configuração usaremos mais tarde e é a porta de rede que nossa API vai esperar as requisições, sendo 3000 o valor mais popular para aplicações Node.js.
Agora, crie um arquivo create-table.js dentro dessa pasta, que será o arquivo que vai criar e popular nossa tabela que usaremos neste exercícios. Também usaremos ele para entender o básico de comandos SQL, conexão com o banco, etc. Vamos começar nosso create-table.js carregando o pacote dotenv, que por sua vez vai carregar as variáveis que definirmos nele e depois definindo uma constante para a string de conexão com o banco e uma constante para o objeto que vai carregar a extensão mssql (e que mais tarde usaremos para conectar, executar SQL, etc).
|
1 2 3 4 5 |
require("dotenv").config(); const connStr = process.env.CONNECTION_STRING; const sql = require("mssql"); |
Agora criaremos uma função logo abaixo do código anterior e usaremos esse objeto sql para fazer uma conexão e, em caso de sucesso, imprimir uma mensagem de sucesso. Caso contrário, se der erro, uma mensagem de falha.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
async function createTable(){ try { await sql.connect(connStr); console.log("conectou!"); }catch(err){ console.error(err); } } createTable(); |
Se esse código lhe parece muito estranho, calma, é fácil de entender. O objeto sql permite que façamos coisas no banco de dados, uma delas é a conexão (connect). No entanto, o Node.js trabalha de maneira assíncrona, o que quer dizer que ele não espera pela conexão ser estabelecida, ele retorna uma promessa (promise) de que vai executar algo após a conexão for concluída. Sendo assim, o uso do await é para que ele só vá para a linha de baixo, após a conexão ser estabelecida. Caso dê algum erro no processo, o catch vai capturar o erro e exibir a mensagem.
Para executar esse arquivo, abra o console e na pasta do projeto digite:
|
1 2 3 |
node create-table.js |
Agora que sabemos como conectar no SQL Server através de Node.js, é hora de executarmos o comando que vai criar a tabela e popular ela, ao invés de simplesmente imprimir “conectou”. Sendo assim, vamos evoluir nossa função JS nesse arquivo pra fazer a criação da tabela de fato:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
async function createTable() { try { await sql.connect(connStr); const table = new sql.Table('Clientes'); table.create = true; table.columns.add('ID', sql.Int, { nullable: false, primary: true }); table.columns.add('Nome', sql.NVarChar(150), { nullable: false }); table.columns.add('CPF', sql.NChar(11), { nullable: false }); table.rows.add(1, 'teste1', '12345678901'); table.rows.add(2, 'teste2', '09876543210'); table.rows.add(3, 'teste3', '12312312399'); const request = new sql.Request(); await request.bulk(table); console.log('funcionou'); } catch (err) { console.error(err); } } |
Aqui, após a conexão que já fazíamos eu crio um objeto Table, defino suas colunas assim como faria por um editor visual e defino algumas linhas de exemplo para que já tenha dados quando for criado. Essa configuração toda é enviada para um objeto Request que transformará tudo em comandos SQL que enviaremos aos ervidor com a função bulk (que serve para enviar múltiplos comandos de uma só vez).
Mande rodar esse arquivo novamente e verá que ele criará o seu banco já com 3 linhas de dados. Se não quiser fazer isso dessa maneira, você pode fazer pela sua ferramenta de gerenciamento do SQL Server (como o Management Studio).
Se não sabe nada de SQL e quiser aprender mais, o vídeo abaixo pode ajudar (apesar de ser com MySQL, a base é a mesma).
Parte 3: Criando a API
Agora que já temos nosso banco de dados SQL Server pronto, com dados de exemplo e aprendemos como fazer a conexão nele, vamos criar uma API básica usando Express para conseguir criar um CRUD com Node.js + MS SQL Server no próximo passo. Se já sabe como montar uma API básica com Node + Express, pule esta parte.
Vamos criar um arquivo index.js na mesma pasta do projeto onde vamos criar o nosso servidor da API para tratar as requisições que chegarão em breve. Vamos começar bem simples, apenas carregando as variáveis do .env e definindo as constantes locais que serão usadas mais pra frente:
|
1 2 3 4 5 6 7 8 |
require("dotenv").config(); const express = require('express'); const app = express(); const port = process.env.PORT; const sql = require('mssql'); const connStr = process.env.CONNECTION_STRING; |
Agora, logo abaixo, vamos configurar nossa aplicação (app) Express para usar o body parser de formulários, permitindo que recebamos mais tarde POSTs nos formatos URLEncoded e JSON:
|
1 2 3 4 5 |
//configurando o body parser para pegar POSTS mais tarde app.use(express.urlencoded({ extended: true })); app.use(express.json()); |
Na sequência, vamos criar um roteador e dentro dele definir uma regra inicial que apenas exibe uma mensagem de sucesso quando o usuário requisitar um GET na raiz da API (/) para ver se está funcionando.
|
1 2 3 4 5 6 |
//definindo as rotas const router = express.Router(); router.get('/', (req, res) => res.json({ message: 'Funcionando!' })); app.use('/', router); |
Note que na última linha eu digo que requisições que chegarem na raiz devem ser mandadas para o router. Por fim, adicionamos as linhas abaixo no final do arquivo que dão o start no servidor da API:
|
1 2 3 4 |
//inicia o servidor app.listen(port, () => console.log('API funcionando!')); |
Teste sua API executando via console o seu index.js com o comando ‘node index.js’. Você deve ver a mensagem de ‘API funcionando!’ no console, e se acessar no navegador localhost:3000 deve ver o JSON default que deixamos na rota raiz!
Parte 4: Criando a listagem de clientes
Agora que temos uma API funcionando, vamos adicionar uma rota /clientes que listará todos os clientes do banco de dados. Para fazer isso, primeiro vamos criar uma função que vai gerenciar a conexão ao banco de dados, garantindo que teremos apenas uma sempre, o que chamamos de Singleton.
|
1 2 3 4 5 6 7 8 9 |
let connection = null; async function getConnection() { if (connection) return connection; connection = sql.connect(connStr); return connection; } |
Agora sempre que quisermos obter uma conexão para acessar o banco de dados podemos chamar essa função getConnection que, se já tiver uma conexão pronta, vai nos devolver ela. Na sequência vamos criar uma função que executará consultas SQL no banco usando a função de conexão, como abaixo:
|
1 2 3 4 5 6 7 |
async function execSQLQuery(sqlQry) { const req = getConnection().request() const { recordset } = await req.query(sqlQry); return recordset; } |
Esta função nós usaremos para consultar todos os clientes e para consultar apenas um também. Agora, vamos criar a rota /clientes logo abaixo da rota / (raiz):
|
1 2 3 4 5 6 |
router.get('/clientes', async (req, res) => { const results = await execSQLQuery('SELECT * FROM Clientes'); res.json(results); }) |
Agora, ao executarmos novamente nosso projeto e acessarmos a URL localhost:3000/clientes, veremos todos os clientes cadastrados no banco de dados (no passo 2, lembra?):
E com isso finalizamos a listagem de todos clientes na nossa API!
Parte 5: Criando a pesquisa de um cliente
Agora, se o usuário quiser ver apenas um cliente, ele deverá passar o ID do mesmo na URL, logo após o /clientes. Para fazer isso, vamos modificar nossa rota criada no passo anterior, /clientes, para aceitar um parâmetro opcional ID. Além disso, dentro do processamento da rota, se vier o ID, devemos fazer uma consulta diferente da anterior, como mostra o código abaixo, com os ajustes na mesma rota do passo anterior:
|
1 2 3 4 5 6 7 8 |
router.get('/clientes/:id?', async (req, res) => { let filter = ''; if (req.params.id) filter = 'WHERE ID=' + parseInt(req.params.id); const results = await execSQLQuery('SELECT * FROM Clientes ' + filter); res.json(results); }) |
O parseInt que coloquei é apenas uma proteção contra ataques de SQL Injection uma vez que neste caso o ID deve ser um inteiro válido. Não é a melhor forma de resolver isso, mas vai nos atender por enquanto sem ter de entrar em conceitos mais avançados. Manda rodar e teste no navegador, verá que está funcionando perfeitamente!
E com isso terminamos a pesquisa por cliente.
Parte 6: Excluindo um cliente
Para excluir um cliente vamos fazer um processo parecido com o de pesquisar um cliente, no entanto, mudaremos o verbo HTTP de GET para DELETE, como manda o protocolo. Adicione a nova rota logo após as demais:
|
1 2 3 4 5 6 |
router.delete('/clientes/:id', async (req, res) => { await execSQLQuery('DELETE Clientes WHERE ID=' + parseInt(req.params.id)); res.sendStatus(204); }) |
Note que desta vez o parâmetro id na URL não é opcional (não usei ? após :id). E dentro do processamento da requisição delete do router eu mando um SQL de DELETE passando o ID numérico.
Para testar essa rota você tem duas alternativas, ou usa o POSTMAN para forjar um DELETE, como abaixo:
Caso não faça a mínima ideia de como usar o Postman, eu ensino no vídeo abaixo.
Você deve obter uma resposta 200 ou 204 (caso não tenha dado erro) e se mandar listar todos clientes novamente, verá que o número 1 sumiu. Se quiser entender mais sobre esses códigos de retorno, leia esse artigo.
Parte 7: Adicionando um cliente
Agora vamos adicionar um novo cliente com um POST na rota /clientes. Adicione esta nova rota logo abaixo das anteriores.
|
1 2 3 4 5 6 7 8 9 |
router.post('/clientes', async (req, res) => { const id = parseInt(req.body.id); const nome = req.body.nome.substring(0, 150).replaceAll("'", ""); const cpf = req.body.cpf.substring(0, 11).replaceAll("'", ""); await execSQLQuery(`INSERT INTO Clientes(ID, Nome, CPF) VALUES(${id},'${nome}','${cpf}')`); res.sendStatus(201); }) |
Nela, eu pego as variáveis que devem vir junto ao POST, faço algumas validações de tamanho (para validações mais profissionais, leia este artigo), removo possíveis aspas simples (principal arma de atacantes) e depois junto elas a um comando de INSERT que vai ser executado no banco de dados.
Para testar esse POST, você usar o POSTMAN, como mostrado anteriormente:
Também podemos permitir outras maneiras de passar os dados com a requisição POST, como através de JSON. Isso exige mais configurações no Postman, porque nossa API em si já suporta JSON também.
Se testar agora vai ver que é possível inserir novos registros no banco de dados através de requisições POST.
Parte 8: Atualizando um cliente
E para finalizar o CRUD, vamos ver como podemos atualizar um cliente no banco de dados SQL Server através da nossa API Node.js. Para fazer updates podemos usar os verbos PUT ou PATCH. O protocolo diz que devemos usar PUT se pretendemos passar todos os parâmetros da entidade que está sendo atualizada, mas não vamos alterar jamais o ID, então usaremos PATCH nesta API.
Crie uma rota PATCH em /clientes esperando o ID do cliente a ser alterado.
|
1 2 3 4 5 6 7 8 9 |
router.patch('/clientes/:id', async (req, res) => { const id = parseInt(req.params.id); const nome = req.body.nome.substring(0, 150).replaceAll("'", ""); const cpf = req.body.cpf.substring(0, 11).replaceAll("'", ""); await execSQLQuery(`UPDATE Clientes SET Nome='${nome}', CPF='${cpf}' WHERE ID=${id}`); res.sendStatus(200); }) |
No código acima, pegamos o ID que veio na URL e as demais informações que vieram no corpo da requisição, fazendo as mesmas validações que já havia feito antes e depois monto o UPDATE com as variáveis locais e mando para nossa função de executar SQL.
Para testar uma requisição PATCH, você pode usar o POSTMAN:
O resultado é que o cliente cujo ID=4 vai ter o seu nome alterado para ‘fernando’. Note que se ele não existir, ocasionará um erro que será apresentado no corpo da resposta.
E com isso finalizamos o CRUD da nossa API Node.js que usa SQL Server como persistência de dados.
Bônus 1: Executando muitas operações SQL
Ao contrário dos bancos não-relacionais que estamos acostumados a usar com Node.js, o SQL Server leva mais a sério a escrita dos dados e o faz de maneira síncrona e um tanto lenta para uma plataforma veloz como o Node.js. Isso faz com que, se você for executar um loop de comandos no banco de dados, provavelmente vai dar erro pois o banco não conseguirá responder apropriadamente a uma enxurrada de requisições da sua aplicação Node.
Para resolver isso eu costumo encadear as execuções uma a uma, simulando uma execução assíncrona através de chamadas recursivas, como no exemplo abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function execute(items, i, conn){ if(!items[i]) return console.log("terminou"); conn.request() .query(`DELETE Usuario WHERE email='${items[i]}'`) .then(result => { console.log(result) execute(items, ++i, conn)//faz o próximo }) .catch(err => console.log(err)); } |
Neste exemplo eu possuía um array de itens que eram endereços de email que eu queria excluir do banco de dados. O i passado por parâmetro inicialmente é 0, quando chamarmos esta função a primeira vez e depois vai sendo incrementado recursivamente. Já o objeto conn é a conexão do banco de dados, que você pode omitir se estiver usando ela global.
O primeiro if do código garante a condição de parada da recursão e o catch na promise da query te contará se houver algum erro. Nada muito rebuscado, mas me foi bem útil!
Bônus 2: ORM
Se você não curte muito a ideia de ficar usando SQL no meio dos seus códigos JS, experimente usar alguma biblioteca ORM (Object-Relational Mapping) como o Sequelize.
Olá, tudo bem?
O que você achou deste conteúdo? Conte nos comentários.
Muito bom o artigo, assim como o seu blog é muito bom mesmo. Muito bem organizado.
Que bom que curtiu e agradeço o feedback!
Muito bom estou lendo seu livro agora pra melhorar meus conhecimentos em node. Como eu faria pra retornar os dados sem ser na forma de json exemplo preencher uma tabela dinamicamente? Tentei de varias formas mas estou tendo dificuldade para encontrar material de como integrar node com sql serve
ID
nome
função
login
senha
<%for(var i=0; i
Tenho só esse post por enquanto pois Node e SQL Server não é uma combinação muito comum. Minha sugestão é usar JSON mesmo, assim como ensino em todos tutoriais, mas você pode fazer sua API retornar uma string HTML se preferir, com a tabela pronta, se não gostar de JSON.
Estou tentando fazer a conexão com o sqlexpress e não consigo, pois preciso passar o “” no nome do servidor, ex.: servidorSQLEXPRESS e retorna um erro de conexão.
ConnectionError: Failed to connect
Já tentei escapar o e não rola.
Poderia me ajudar?
É local ou remoto? Eu uso SQL Server Express também em meus projetos e não costumo usar o sqlexpress, visto que é a default instance do meu servidor sempre.
Junior aconteceu um problema comigo
“ConnectionError: Failed to connect to localhostSQLEXPRESS:1433 – getaddrinfo ENOTFOUND localhostSQLEXPRESS”
Mudei o nome do server para localhost\SQLEXPRESS e funcionou.
No Adicionar e Atualizar o meu deu erro. Na hr que vou levantar o servidor no node, ele aqui um erro na linha “execSQLQuery” e exibe um erro “missing ) after argument list. Já verifiquei várias vzs e apresenta o msm erro
OBS: em casa fiz esse tutorial e funcionou normal, mas aqui no estágio está com esse erro…
Está faltando um fecha parênteses no seu código SQL.
Oi, Luiz. Boa noite. Primeiramente queria te agradecer pela atenção em responder. Então, verifiquei bastante o código. Quando fiz em casa eu copiei e colei. Quando refiz no estágio, eu digitei o código todo pois estava no pc e acompanhando seu tutorial pelo tablet, então no início da execSQLQuery eu iniciei com aspas simples e era crase. Por isso estava dando erro. Um amigo me alerto, pois tem mais tempo de programação.
Outra coisa. Meu chefe lá no estágio pediu para criar uma api em js (node) para se conectar com o mssql, mas ele não quer passar DMLs no código. Ele quer acessar SPs (procedures) e quer o retorno. Vc teria alguma material sobre isso ou poderia me indicar? Mto obrigado.
A crase é somente quando tem variáveis dentro da string.
Já tentou passar ‘exec proc1 param1’ ao invés de DML? Deve funcionar, trocando proc1 e param1 pelo nome da procedure e seus parâmetros.
Estou tentando fazer um insert em uma tabela, porem precido saber qual id acabei de inserir para usar em uma outra tabela como se fosse uma “fk”.
Exemplo:
pessoa:{
nome: “fulano”,
sobrenome: “Oi”,
contatos:{
telefone: “XX-XXXXXXX”,
celular: “XX-XXXXXXX”
}
}
Apos inserir a pessoa, preciso saber qual foi o id que acabei de inserir para na tabela de contatos colocar o vinculo no pessoa_id
Basta consultar o último documento inserido e pegar a informação que precisar: db.pessoas.find({}).sort({_id:-1}).limit(1)
Outra alternativa é você gerar o _id e passar para o documento pessoa durante a inserção. Assim você já vai ter esta informação consigo.
Opa valeu, a ideia deu certo, fiz a inserção no banco e na mesma query o select para pegar o ultimo registro. estou usando o MSSql.
Valeu
Bá, e eu te dei a dica usando MongoDB, hehehehe. No SQL Server tem um comandok para retornar o ID após o INSERT, acho que é SELECT @@IDENTITY ou algo assim, faz tempo que não uso.
Bom dia, eu gostaria de saber como faz para utilizar muitos comandos sql em um única transação com o mssql no node. Na documentação só da exemplo podendo passar um comando sql na transação.
Você passa quantos comandos quiser assim como faria nas ferramentas de consulta (SQL Server Management Studio, por exemplo), é uma string SQL. Geralmente se usa ; (ponto e vírgula) para separar os comandos ao invés de quebra de linha, mas não é comum fazer mais de um pois só conseguirá ter o retorno do último se executar uma única vez.
Muito obrigada mesmo, cara! Está perfeito! Me ajudou muito, tudo muito bem explicado.
Fico feliz que o tutorial tenha sido útil Camila!
Boa noite amigo.
Não consegui fazer a conexão com meu sqlserver local. Eu uso tudo somente local. Por exemplo. Tenho vários programas em C# e VB que utilizam o banco de dados local. Quando abro o Sql Server Management Studio o nome do meu servidor é NOTEBOOK\SQLEXPRESS. Coloquei na string de conexão a seguinte linha:
const connStr = ‘Server=NOTEBOOK/SQLEXPRESS;Database=Cliente;’;
Dá a seguinte mensagem: ConnectionError: Failed to connect to NOTEBOOK/SQLEXPRESS:1433 – getaddrinfo ENOTFOUND NOTEBOOK/SQLEXPRESS.
Tem ideia do que pode ser?
Muito obrigado.
Sim, o endereço do servidor está errado. Faz tempo que não uso SQL Server, mas experimente usar a barra pro outro sentido, ou ainda usar localhost ao invés de usar NOTEBOOK ou ainda .\ ao invés de NOTEBOOK.
Boa noite. Finalmente achei o que buscava, que era criar um servidor e com instrução SQL , que me é mais amigável.
Estou com um problema e não consigo passar do Insert em Clientes.
Ao manter seu código, tenho a seguinda mensagem de erro: “TypeError: Cannot read properties of undefined (reading ‘substring’)”
Se removo o substring, deixando, por exemplo, const nome = req.body.nome, a mensagem muda para ” “message”: “Invalid column name ‘NaN’.”
Consigo listar os clientes, pesquisar um específico, deletar, tudo legal.
Meu código está assim (eu precisei mudar o nome da tabela mesmo):
router.post(‘/clientes’, (req, res) =>{
const id = parseInt(req.body.id);
const nome = req.body.nome
const cpf = req.body.cpf
execSQLQuery(
INSERT INTO ClientesNode(ID, Nome, CPF) VALUES(${id},'${nome}','${cpf}'), res);})
Se puder me ajudar…
Um abraço.
Provavelmente seu req.body está chegando vazio. Experimente usar console.log para imprimir ele e verá. Quando isso acontece geralmente é por falta do body parser configurado no index.js ou porque o Postman não está devidamente configurado. De qualquer forma este tutorial estava muito antigo e com algumas coisas que não funcionam mais, acabei de atualizar todo ele, recomendo dar uma revisada geral agora, pois mudei bastante coisa.