Create Table in MySQL using Nodejs

MySQL queries may be run directly from the Node.js program. In this guide, you'll learn how to use a node.js script to build a new table in a MySQL database.

To build a basic table in a database, MySQL uses the following query.

CREATE TABLE users (user_id INT(100), user_name VARCHAR(255), email VARCHAR(255))

Now, let's dive into the tutorial and try making the same table for a Node.js script. Let's get a file called create_MySQL_table.js.

Node.js MySql Create Table

You can make a table with node.js by following these steps:

1. Initially, you have to establish the connection between NodeJs and MySQL
2. To run a CREATE TABLE argument, use the query() method on the relation object.
3. Disconnect from the client.

We took the following example to demonstrate how to bind to the todo app database and run the CREATE TABLE command:

let mysql = require('mysql');
let connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todoapp'
});

// connect to the MySQL server
connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  let createTodos = `create table if not exists todos(
                          title varchar(255)not null,
                          completed tinyint(1) not null default 0
                      )`;

  connection.query(createTodos, function(err, results, fields) {
    if (err) {
      console.log(err.message);
    }
  });

  connection.end(function(err) {
    if (err) {
      return console.log(err.message);
    }
  });
});

An SQL statement and a callback are accepted by the query() process. Three arguments are passed to the callback function:

• error: it stores the error details if any occurs during the execution of the argument.
• results: holds the query's results.
• fields: if any includes information about the effects fields

Let’s execute the program:

> node query.js

If you haven't made any mistakes in execution the query was completed successfully and without errors.

Let's see if the todos table was successfully generated in the database:

>mysql -u root -p todoapp;
Enter password: *********

mysql> show tables;
+-------------------+
| Tables_in_todoapp |
+-------------------+
| todos             |
+-------------------+
1 row in set (0.08 sec)

Create Primary Key in New Table

When constructing a table, make sure to have a column for each record with a specific key.

This can be accomplished by creating an INT AUTOINCREMENT PRIMARY KEY tab, which will insert a unique number for each record. For each record, start at 1 and increment by one on every iteration/addition.

let mysql = require('mysql');
let connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todoapp'
});

// connect to the MySQL server
connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  let createTodos = `create table if not exists todos(
                          id int primary key auto_increment,
                          title varchar(255)not null,
                          completed tinyint(1) not null default 0
                      )`;

  connection.query(createTodos, function(err, results, fields) {
    if (err) {
      console.log(err.message);
    }
  });

  connection.end(function(err) {
    if (err) {
      return console.log(err.message);
    }
  });
});

Add Columns to existing Table

By using the ALTER TABLE query, you can conveniently add a column to a current MySQL table.

let mysql = require('mysql');
let connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todoapp'
});

// connect to the MySQL server
connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  let createTodos = `ALTER table if not exists todos(
                          id int primary key auto_increment,
                          title varchar(255)not null,
                          completed tinyint(1) not null default 0
                      )`;

  connection.query(createTodos, function(err, results, fields) {
    if (err) {
      console.log(err.message);
    }
  });

  connection.end(function(err) {
    if (err) {
      return console.log(err.message);
    }
  });
});

Conclusion

Finally, congratulations you are now one step closer to mastering the skills in Node.js as you learned how to make a new table in a MySQL database using Node.js.