顾 静

顾 静

1656538800

如何在 Node 中使用 AJAX 从 MySQL 数据库中检索数据

 MySQL 是一个基于 SQL（结构化查询语言）的关系型数据库管理系统，在 MySQL 中管理数据并没有那么困难。

在本详细指南中，我们将了解如何在 Node js 应用程序中使用 AJAX 从 MySQL 数据库中检索数据。我们将构建一个 HTML 下拉元素，我们将从数据库中获取记录。

我们将从 NPM 注册表中安装一些包，这些包将帮助我们将 Node 连接到 MySQL 数据库并构建与数据库交互的 API 路由。

此外，我们将创建节点脚本，这将帮助我们运行节点服务器。

让我们了解如何通过节点环境使用 MySQL 数据库。

如何使用 Node Js 和 jQuery Ajax 从 MySQL 数据库中获取数据

  • 第 1 步：创建节点应用程序
  • 第 2 步：添加 NPM 模块
  • 步骤 3：在数据库中创建表
  • 第 4 步：数据库连接
  • 第 5 步：创建服务器文件
  • 第 6 步：在 HTML 中呈现和显示数据
  • 第 7 步：服务节点项目

创建节点应用

键入 mkdir 命令，后跟您的项目名称，然后按 Enter 创建一个新文件夹。

mkdir node-vlog

然后，进入应用程序文件夹。

cd node-vlog

使用 npm init 命令，该命令会创建package.json文件，您的项目的元信息将保存在该文件中。

npm init

在项目的根目录中创建一个app.js文件，在脚本部分注册文件名，以便可以使用命令行工具调用此节点脚本。

{
  "main": "app.js",
}

添加 NPM 模块

我们将从 npm 注册表安装以下给定的包，执行命令并同时安装模块。

npm install ejs express cors mysql body-parser nodemon

在数据库中创建表

在您的数据库中，您需要有一个包含一些记录的表，以便您可以从数据库中获取数据。

如果您没有创建表，请从 sql 选项卡运行给定的 sql 命令。

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

数据库连接

在您的节点项目中，创建database.js文件，然后在给定的代码中，添加您的数据库凭据，以便将数据库连接到节点应用程序。

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

创建服务器文件

打开app.js脚本文件，在这个文件中放入给定的代码。

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

以下脚本负责设置 ejs 视图引擎，定义 cors 设置，声明将与数据库通信的路由并定义应用程序端口。

在 HTML 中呈现和显示数据

在您的应用程序的根目录，创建index.ejs文件，该文件将处理您的节点应用程序的视图。它将显示您从数据库中获取的记录。

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

服务节点项目

最终，您需要调用建议的命令来启动节点应用程序。

nodemon

您需要使用此 url 来测试应用程序：

http://localhost/:5555

 

结论

在本指南中，我们查看了将结果从 MySQL 数据库获取到 Node js 应用程序的过程。

我们介绍了如何从 MySQL 数据库中获取数据并使用外部依赖项在 Node js 应用程序的 HTML 选择下拉列表中显示记录。

我们希望您喜欢本指南并与他人分享。 

来源：https ://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax 

What is GEEK

Buddha Community

如何在 Node 中使用 AJAX 从 MySQL 数据库中检索数据
Joe Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

顾 静

顾 静

1656538800

如何在 Node 中使用 AJAX 从 MySQL 数据库中检索数据

 MySQL 是一个基于 SQL（结构化查询语言）的关系型数据库管理系统，在 MySQL 中管理数据并没有那么困难。

在本详细指南中，我们将了解如何在 Node js 应用程序中使用 AJAX 从 MySQL 数据库中检索数据。我们将构建一个 HTML 下拉元素，我们将从数据库中获取记录。

我们将从 NPM 注册表中安装一些包，这些包将帮助我们将 Node 连接到 MySQL 数据库并构建与数据库交互的 API 路由。

此外，我们将创建节点脚本，这将帮助我们运行节点服务器。

让我们了解如何通过节点环境使用 MySQL 数据库。

如何使用 Node Js 和 jQuery Ajax 从 MySQL 数据库中获取数据

  • 第 1 步：创建节点应用程序
  • 第 2 步：添加 NPM 模块
  • 步骤 3：在数据库中创建表
  • 第 4 步：数据库连接
  • 第 5 步：创建服务器文件
  • 第 6 步：在 HTML 中呈现和显示数据
  • 第 7 步：服务节点项目

创建节点应用

键入 mkdir 命令，后跟您的项目名称，然后按 Enter 创建一个新文件夹。

mkdir node-vlog

然后，进入应用程序文件夹。

cd node-vlog

使用 npm init 命令，该命令会创建package.json文件，您的项目的元信息将保存在该文件中。

npm init

在项目的根目录中创建一个app.js文件，在脚本部分注册文件名，以便可以使用命令行工具调用此节点脚本。

{
  "main": "app.js",
}

添加 NPM 模块

我们将从 npm 注册表安装以下给定的包，执行命令并同时安装模块。

npm install ejs express cors mysql body-parser nodemon

在数据库中创建表

在您的数据库中，您需要有一个包含一些记录的表，以便您可以从数据库中获取数据。

如果您没有创建表，请从 sql 选项卡运行给定的 sql 命令。

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

数据库连接

在您的节点项目中，创建database.js文件，然后在给定的代码中，添加您的数据库凭据，以便将数据库连接到节点应用程序。

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

创建服务器文件

打开app.js脚本文件，在这个文件中放入给定的代码。

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

以下脚本负责设置 ejs 视图引擎，定义 cors 设置，声明将与数据库通信的路由并定义应用程序端口。

在 HTML 中呈现和显示数据

在您的应用程序的根目录，创建index.ejs文件，该文件将处理您的节点应用程序的视图。它将显示您从数据库中获取的记录。

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

服务节点项目

最终，您需要调用建议的命令来启动节点应用程序。

nodemon

您需要使用此 url 来测试应用程序：

http://localhost/:5555

 

结论

在本指南中，我们查看了将结果从 MySQL 数据库获取到 Node js 应用程序的过程。

我们介绍了如何从 MySQL 数据库中获取数据并使用外部依赖项在 Node js 应用程序的 HTML 选择下拉列表中显示记录。

我们希望您喜欢本指南并与他人分享。 

来源：https ://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax 

Adolphus Tillman

Adolphus Tillman

1656511645

How to Retrieve Data From The MySQL Database using AJAX in Node

 MySQL is a relational database management system based on SQL – Structured Query Language, and managing data in MySQL is not that difficult.

In this detailed guide, we will find out how to retrieve data from the MySQL database using AJAX in the Node js app. We will build an HTML dropdown element in which we will get the records from the database.

We will install some packages from the NPM registry, and these packages will help us connect Node to the MySQL database and build the API routes that will interact with the database.

Furthermore, we will create the node script, which will help us run the node server.

Let us find out how to work with MySQL database through a node environment.

How to Get Data from MySQL Database using Node Js and jQuery Ajax

  • Step 1: Create Node App
  • Step 2: Add NPM Modules
  • Step 3: Create Table in Database
  • Step 4: Database Connection
  • Step 5: Create Server File
  • Step 6: Render and Show Data in HTML
  • Step 7: Serve Node Project

Create Node App

Type the mkdir command followed by your project name and hit enter to create a new folder.

mkdir node-vlog

Then, move into application folder.

cd node-vlog

Use the npm init command, this command creates the package.json file, where your project’s meta information stays.

npm init

In the project’s root create an app.js file, register the file name in scripts section so that this node script can be invoked using the command-line tool.

{
  "main": "app.js",
}

Add NPM Modules

We are going to install the given below packages from the npm registry, execute the command and install the modules simultaneously.

npm install ejs express cors mysql body-parser nodemon

Create Table in Database

In your database, you need to have a table with some records, so that you can get the data from the database.

If you don’t have table created, run the given sql command from the sql tab.

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

Database Connection

In your node project, make the database.js file and then inside the given code, add your database credentials in order to connect the database to the node app.

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

Create Server File

Open the app.js script file, in this file put the given code.

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

The following script is responsible to set up the ejs view engine, define the cors setting, declare the route that will communicate with database and define the application port.

Render and Show Data in HTML

At the root of your application, make the index.ejs file, this file will handle the view of your node app. It will display the records that you get from the database.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

Serve Node Project

Ultimately, you need to evoke the suggested command to start the node application.

nodemon

You require to use this url to test the app:

http://localhost/:5555

 

Conclusion

In this guide, we looked at the process of getting the results from the MySQL database into the Node js app.

We covered how to fetch the data from the MySQL database and display the records in the HTML select dropdown in a Node js app using the external dependencies.

We hope you liked this guide and share it with others. 

Source: https://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax 

Georgette Sipes

Georgette Sipes

1626336180

Node.js Tutorials #4 - Node & MySQL - How to use Mysql database with Node.js

Node.js Tutorials #4 - Node & MySQL - How to use Mysql database with Node.js
Sample code in Node with writing SELECT, INSERT, UPDATE query with parameters

Node tutorials playlist - https://www.youtube.com/playlist?list=PLC8jf7P7lriyXGwAdPsmyBsZenxh-gJzl

00:00 Node & MySQL - Install MySQL
01:37 Node & MySQL - SELECT query
11:06 Node & MySQL - INSERT query
11:06 Node & MySQL - Multiple INSERT queries
21:59 Node & MySQL - UPDATE query
26:35 Node & MySQL - DELETE query

Basics of Node.js Tutorials #1 - Starting with Node - https://youtu.be/qO8X0t6YePE
Basics of Node.js Tutorials #2 - Node & Express - GET request - https://youtu.be/7Ryp2YeM_W8
Basics of Node.js Tutorials #3 - Node & Express - POST, PUT, DELETE request - https://youtu.be/3ItPpS_r2a8
Basics of Node.js Tutorials #4 - Node & MySQL - How to use Mysql database with Node.js - https://youtu.be/8Xdz0RP-kD4
Basics of Node.js Tutorials #5 - Node with Express & MySQL - Using Express with MySQL DB in Node.js - https://youtu.be/4DMxw123kw4
Node.js Tutorials #6 - Node & Express Middleware - Using Middleware with Express in Node.js - https://youtu.be/qch1mJQeYvU
Node.js Tutorials #7 - Node & Cookies - Using Cookes with Express in Node.js - Cookie-parser - https://youtu.be/Gm2VNzP4yok
Node.js Tutorials #8 - Node & JSONWebToken (JWT) - Using JSONWebToken (JWT) with Express in Node.js - https://youtu.be/UT1k0DYBBPk

#node.js #mysql #database #node

Rui Silva

Rui Silva

1656537720

Como Recuperar Dados Do Banco De Dados MySQL Usando AJAX No Node

 MySQL é um sistema de gerenciamento de banco de dados relacional baseado em SQL – Structured Query Language, e gerenciar dados no MySQL não é tão difícil.

Neste guia detalhado, descobriremos como recuperar dados do banco de dados MySQL usando AJAX no aplicativo Node js. Construiremos um elemento suspenso HTML no qual obteremos os registros do banco de dados.

Instalaremos alguns pacotes do registro NPM, e esses pacotes nos ajudarão a conectar o Node ao banco de dados MySQL e construir as rotas de API que irão interagir com o banco de dados.

Além disso, criaremos o script do nó, que nos ajudará a executar o servidor do nó.

Vamos descobrir como trabalhar com banco de dados MySQL através de um ambiente de nó.

Como obter dados do banco de dados MySQL usando Node Js e jQuery Ajax

  • Etapa 1: criar aplicativo de nó
  • Etapa 2: adicionar módulos NPM
  • Etapa 3: criar tabela no banco de dados
  • Etapa 4: conexão com o banco de dados
  • Etapa 5: criar arquivo de servidor
  • Etapa 6: renderizar e mostrar dados em HTML
  • Etapa 7: servir o projeto do nó

Criar aplicativo de nó

Digite o comando mkdir seguido pelo nome do seu projeto e pressione Enter para criar uma nova pasta.

mkdir node-vlog

Em seguida, mova para a pasta do aplicativo.

cd node-vlog

Use o comando npm init, este comando cria o arquivo package.json , onde ficam as meta-informações do seu projeto.

npm init

Na raiz do projeto, crie um arquivo app.js , registre o nome do arquivo na seção de scripts para que esse script de nó possa ser invocado usando a ferramenta de linha de comando.

{
  "main": "app.js",
}

Adicionar módulos NPM

Vamos instalar os pacotes abaixo do registro npm, executar o comando e instalar os módulos simultaneamente.

npm install ejs express cors mysql body-parser nodemon

Criar tabela no banco de dados

Em seu banco de dados, você precisa ter uma tabela com alguns registros, para que possa obter os dados do banco de dados.

Se você não tiver a tabela criada, execute o comando sql fornecido na guia sql.

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

Conexão de banco de dados

Em seu projeto de nó, crie o arquivo database.js e, em seguida, dentro do código fornecido, adicione suas credenciais de banco de dados para conectar o banco de dados ao aplicativo de nó.

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

Criar arquivo do servidor

Abra o arquivo de script app.js , neste arquivo coloque o código fornecido.

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

O script a seguir é responsável por configurar o mecanismo de visualização ejs, definir a configuração do cors, declarar a rota que irá se comunicar com o banco de dados e definir a porta da aplicação.

Renderizar e mostrar dados em HTML

Na raiz da sua aplicação, crie o arquivo index.ejs , este arquivo irá lidar com a visualização da sua aplicação node. Ele exibirá os registros que você obtém do banco de dados.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

Projeto de nó de serviço

Por fim, você precisa evocar o comando sugerido para iniciar o aplicativo do nó.

nodemon

Você precisa usar este URL para testar o aplicativo:

http://localhost/:5555

Conclusão

Neste guia, analisamos o processo de obtenção dos resultados do banco de dados MySQL no aplicativo Node js.

Cobrimos como buscar os dados do banco de dados MySQL e exibir os registros no menu suspenso de seleção de HTML em um aplicativo Node js usando as dependências externas.

Esperamos que você tenha gostado deste guia e o compartilhe com outras pessoas. 

Fonte: https://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax