This is not usually recommended to directly connect to the database in JavaScript and perform addition, deletion, modification and query (CRUD) operations, because JavaScript (especially front-end JavaScript) cannot directly connect to the database for security and architectural design considerations. Generally speaking, the database should be interacted with the database through backend services (such as Express, etc.), and the front-end communicates with the backend through API requests.
Here is a complete example showing how to use CRUD with MySQL database:
Install the necessary dependencies
First, make sure you have installed it. Then use the following command to initialize a new project and install the dependency:
npm init -y npm install express mysql2 body-parser
Create a database (MySQL)
Create a database and table in MySQL, for example:
CREATE DATABASE sportsms;
USE sportsms;
CREATE TABLE athletes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
event VARCHAR(100)
);Create an application
Create a file named , the sample code is as follows:
const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;
// Use body-parser to parse JSON request body(());
// Create a database connection poolconst db = ({
host: 'localhost',
user: 'root', // MySQL username password: '', // MySQL password database: 'sportsms' // Database name});
// Test database connection((err, connection) => {
if (err) {
('Database connection failed:', );
return;
}
('Successfully connected to the database');
();
});
// Create contestant (addition)('/athletes', (req, res) => {
const { name, age, event } = ;
const query = 'INSERT INTO athletes (name, age, event) VALUES (?, ?, ?)';
(query, [name, age, event], (err, result) => {
if (err) {
return (500).json({ error: 'Database Error', details: err });
}
(201).json({ id: , name, age, event });
});
});
// Get all contestants (check)('/athletes', (req, res) => {
('SELECT * FROM athletes', (err, results) => {
if (err) {
return (500).json({ error: 'Database Error', details: err });
}
(results);
});
});
// Get a single player (check)('/athletes/:id', (req, res) => {
const { id } = ;
('SELECT * FROM athletes WHERE id = ?', [id], (err, results) => {
if (err) {
return (500).json({ error: 'Database Error', details: err });
}
if ( === 0) {
return (404).json({ error: 'The contestant was not found' });
}
(results[0]);
});
});
// Update the contestant (change)('/athletes/:id', (req, res) => {
const { id } = ;
const { name, age, event } = ;
const query = 'UPDATE athletes SET name = ?, age = ?, event = ? WHERE id = ?';
(query, [name, age, event, id], (err, result) => {
if (err) {
return (500).json({ error: 'Database Error', details: err });
}
if ( === 0) {
return (404).json({ error: 'The contestant was not found' });
}
({ id, name, age, event });
});
});
// Delete the contestant (delete)('/athletes/:id', (req, res) => {
const { id } = ;
('DELETE FROM athletes WHERE id = ?', [id], (err, result) => {
if (err) {
return (500).json({ error: 'Database Error', details: err });
}
if ( === 0) {
return (404).json({ error: 'The contestant was not found' });
}
(204).send();
});
});
// Start the server(port, () => {
(`The server is running，access http://localhost:${port}`);
});
Start the server
On the command line, run the following command to start the server:
node
Test API
You can use tools such as Postman or directly use curl to test the API. Here are example requests for each operation:
Add - Create contestants:
POST /athletes
Request Body (JSON):
{
"name": "John Doe",
"age": 25,
"event": "100m Sprint"
}Check - Get all contestants:
GET /athletes
Response (JSON):
[
{
"id": 1,
"name": "John Doe",
"age": 25,
"event": "100m Sprint"
}
]Check - Get a single player:
GET /athletes/1
Response (JSON):
{
"id": 1,
"name": "John Doe",
"age": 25,
"event": "100m Sprint"
}Change - Update player information:
PUT /athletes/1
Request Body (JSON):
{
"name": "John Doe",
"age": 26,
"event": "200m Sprint"
}Delete - Delete the contestant:
DELETE /athletes/1
This is a complete example of a simple connection to MySQL and performing add-on, delete, modify, and check (CRUD) operations. You can use JavaScript to send HTTP requests to interact with the backend. Note that it is not safe to process database operations directly on the front end, and the front end should interact with the backend only through API requests.
This is the end of this article about using CRUD operations on databases. For more related database CRUD operations, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!