Example create table in mysql with nodejs

Creating a Table

To create a table in MySQL, use the "CREATE TABLE" statement.

Make sure you define the name of the database when you create the connection

Example: Create a table named "customers"

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

con.connect(function(err) {

  if (err) throw err;

  console.log("Connected!");

  var sql = "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))";

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log("Table created");

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlCreateTable.js

Connected!

Table created

 

Primary Key

When creating a table, you should also create a column with a unique key for each record.

This can be done by defining a column it as "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

Example: Create primary key when creating the table

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

 

con.connect(function(err) {

  if (err) throw err;

  console.log("Connected!");

  /*Create a table where the field "id" is primary key:*/

  var sql = "CREATE TABLE customer (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))";

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log("Table created");

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlCreateTable.js

Connected!

Table created

 

If the table already exists, use the ALTER TABLE keyword:

Example: Create primary key on an existing table

 

var mysql = require('mysql');

var con = mysql.createConnection({

    host: "localhost",

    user: "root",

    password: "",

    database: "mydb"

});

con.connect(function (err) {

    if (err)

        throw err;

    console.log("Connected!");

    //Add primary key to an existing table:

    var sql = "ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY";

    con.query(sql, function (err, result) {

        if (err)

            throw err;

        console.log("Table altered");

    });

});

 

Run and result:

 

C:\Nodejs>node mysqlAlterTable.js

Connected!

Table altered