Creating Tables in MySQL from Node.js

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:

1. Connect to the MySQL database server.
2. Call the query() method on the connection object to execute a CREATE TABLE statement.
3. 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 the todo. It defaults to false.

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.jsCode 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.