Summary: in this tutorial, you will learn how to create a new table in MySQL database from a Node.js application.
This tutorial picks up where the connecting to the MySQL Database Server from the Node.js tutorial left off.
Creating tables
To create a table from Node.js, you use these steps:
- Connect to the MySQL database server.
- Call the
query()
method on theconnection
object to execute aCREATE TABLE
statement. - Close the database connection.
Create a new file called create_table.js
in the project directory and add the following code to the file:
let mysql = require('mysql');
let connection = mysql.createConnection({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});
// connect to the MySQL server
connection.connect((err) => {
if (err) return console.error(err.message);
const createTodosTable = `create table if not exists todos(
id int primary key auto_increment,
title varchar(255) not null,
completed bool not null default false
)`;
connection.query(createTodosTable, (err, results, fields) => {
if (err) return console.log(err.message);
});
// close the connection
connection.end((err) => {
if (err) return console.log(err.message);
});
});
Code language: JavaScript (javascript)
How it works.
First, use the mysql
module to connect to the MySQL server:
let mysql = require('mysql');
Code language: JavaScript (javascript)
Second, create a database connection with the parameters come from the .env
configuration file:
let connection = mysql.createConnection({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});
Code language: JavaScript (javascript)
Here’s the contents of the .env
file:
DB_HOST=localhost
DB_PORT=3306
DB_USER=user
DB_PASSWORD=password
DB_NAME=todoapp
Third, connect to the MySQL server:
connection.end((err) => {
if (err) return console.log(err.message);
});
Code language: JavaScript (javascript)
If an error occurs when making a connection to the database, display the error message.
Fourth, define an SQL query to create a table named todos. The todos
table has three columns:
id
is the auto-increment primary key.title
is the todo’s title with a maximum length of 255.completed
is a boolean value that indicates the status of thetodo
. It defaults tofalse
.
const createTodosTable = `create table if not exists todos(
id int primary key auto_increment,
title varchar(255)not null,
completed bool not null default false
)`;
Code language: JavaScript (javascript)
Fifth, execute the CREATE TABLE
statement using the query()
method:
connection.query(createTodosTable, function (err, results, fields) {
if (err) return console.log(err.message);
});
Code language: JavaScript (javascript)
The query()
method accepts an SQL statement and a callback. The callback function takes three arguments:
error
: stores the detailed error if an error occurred during the execution of the statement.results
: holds the results of the query.fields
: holds results field information if any.
Finally, close the connection to the database:
// close the connection
connection.end(function (err) {
if (err) return console.log(err.message);
});
Code language: JavaScript (javascript)
To execute the program that uses the .env
, you use the following command:
node --env-file .env create_table.js
Code language: JavaScript (javascript)
Verifying the table creation
First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL Server:
mysql -h host -u root -p todoapp;
Second, list all tables in the todoapp
database:
show tables;
Output:
+-------------------+
| Tables_in_todoapp |
+-------------------+
| todos |
+-------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
In this tutorial, you have learned how to create a new table in a MySQL database.