Node.js Automation: Connecting to MySQL and Storing Data

![[Node.JS 자동화] Node.js 자동화 프로그래밍, 반복 작업을 AI와 함께 자동화하는 실전 개발(프롤로그)](/uploads/editor/Node-JS-%EC%9E%90%EB%8F%99%ED%99%94-Node-js-%EC%9E%90%EB%8F%99%ED%99%94-%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%B0%8D-%EB%B0%98%EB%B3%B5-%EC%9E%91%EC%97%85%EC%9D%84-AI%EC%99%80-%ED%95%A8%EA%BB%98-%EC%9E%90%EB%8F%99%ED%99%94%ED%95%98%EB%8A%94-%EC%8B%A4%EC%A0%84-%EA%B0%9C%EB%B0%9C-%ED%94%84%EB%A1%A4%EB%A1%9C%EA%B7%B8-20260702-e78eab.webp)
Why Do We Need a Database?
Automation programs continuously generate data.
For example,
collecting news
writing articles with AI
generating images
recording visitor information
storing logs.
If this data is stored only in memory, it disappears when the program stops running.
This is why databases are used to safely store data.
In automation programs, a database essentially serves as the heart of the system.
What Is MySQL?
MySQL is one of the most widely used relational database management systems (RDBMS).
It has been used by many companies and developers for a long time,
offering speed, stability,
and being free to use.
Most websites, blogs, online stores, and forums currently run on MySQL.
This course will be based on MySQL for all projects.
How to Use MySQL in Node.js
In Node.js, several libraries can be used,
but one of the most commonly used is mysql2.
It's easy to install and fast, and supports Promises for clean async/await usage.
Let's start by installing it.
npm install mysql2
Once installed, you're ready to use it in your project.
Loading mysql2
First, load the mysql2 library.
const mysql = require("mysql2/promise");
Using the Promise version makes asynchronous code much easier to read.
This course will focus on the Promise approach.
Connecting to the Database
Now let's connect to a MySQL server.
const mysql = require("mysql2/promise");
const db = await mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "blogscore"
});
Once connected, you're ready to run SQL queries.
In real projects, it's better not to hardcode passwords in your code but to use a .env file instead.
Why Use a .env File?
In professional development, database passwords and API keys are never written directly into the code.
Instead, they're stored separately in a .env file.
For example:
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=1234
DB_NAME=blogscore
This way, important information stays hidden even if the source code is shared publicly.
Querying Data with SELECT
The most commonly used SQL command is SELECT.
To retrieve posts, for example:
const [rows] = await db.query(
"SELECT * FROM blogscore_board"
);
console.log(rows);
The retrieved data is stored in an array within rows.
Using WHERE Conditions
You can also retrieve specific data.
To get the post with ID 1, for example:
const [rows] = await db.query(
"SELECT * FROM blogscore_board WHERE id = ?",
[1]
);
The question mark (?) is a safe placeholder for values.
Storing Data with INSERT
To save new data, use the INSERT command.
For example:
await db.query(
"INSERT INTO blogscore_board(title) VALUES(?)",
["Node.js Course"]
);
This saves the new data.
In automation programs, this is commonly used to store AI-generated content or collected news.
Updating Data with UPDATE
To modify existing data, use UPDATE.
For example:
await db.query(
"UPDATE blogscore_board SET hit = hit + 1 WHERE id = ?",
[1]
);
This can increment views or change status.
Deleting Data with DELETE
To remove unnecessary data, use DELETE.
await db.query(
"DELETE FROM blogscore_board WHERE id = ?",
[1]
);
In professional work, it's crucial to always double-check the WHERE condition when using DELETE.
Forgetting it could delete all data by mistake.
Why Use Prepared Statements?
When writing SQL, it's safer to use prepared statements than directly concatenating strings.
For example:
"WHERE id = ?"
This helps prevent SQL injection attacks.
In professional work, almost all SQL is written this way.
What Is a Connection Pool?
As projects grow, creating new database connections repeatedly becomes inefficient.
That's why connection pools are commonly used.
A pool pre-creates multiple connections and reuses them as needed.
This improves speed and reduces server load.
In upcoming projects, we'll use connection pooling for development.
How Is This Used in Automation Programs?
The automation program we're building will rely heavily on MySQL.
For example:
Storing AI-generated articles
Storing news
Storing keywords
Storing image information
Storing operation logs
Managing scheduled tasks
Managing user information
All this data will be stored in MySQL.
Node.js and MySQL are a very compatible pair, often used together.
Data Schema Design Is Important Too
A good program isn't just about clean code—it's also about how data is structured.
A well-designed database schema improves:
query speed,
easier maintenance,
and simpler addition of new features.
Making a poor initial design choice makes it harder to fix as the project grows.
In upcoming real-world projects, we'll also learn about database design strategies.
Common SQL Commands in Automation
The four most commonly used SQL commands in automation are:
SQL | Purpose |
|---|---|
SELECT | Retrieve data |
INSERT | Save new data |
UPDATE | Edit existing data |
DELETE | Delete data |
Mastering these four commands is enough to develop most automation programs.
Conclusion
In this lesson, we explored how to connect Node.js with MySQL and learned the basics of querying and storing data.
A database is an essential component in any automation program.
In future lessons, AI-generated articles, news content, and keywords will all be stored in MySQL as part of building a complete automation system.
Next, we'll learn how to use the OpenAI API for AI chat and automated article generation. You'll see firsthand how Node.js, Axios, and MySQL connect with AI technology.
FAQ
Why is a database important in automation programs?
A database safely stores data generated by automation processes, ensuring information remains available even after the program stops running.
What is .env file used for?
.env files store sensitive information like database passwords separately from source code to prevent accidental exposure when sharing or publishing code.
Why use prepared statements in SQL?
Prepared statements prevent SQL injection attacks by using placeholders for values instead of directly concatenating strings.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [Node.JS 자동화] 5강. MySQL 연동과 데이터 저장하기