{"id":5765,"date":"2017-06-15T00:19:16","date_gmt":"2017-06-15T07:19:16","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5765"},"modified":"2024-01-03T17:56:46","modified_gmt":"2024-01-04T00:56:46","slug":"create-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-nodejs\/create-table\/","title":{"rendered":"Creating Tables in MySQL from Node.js"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new table in MySQL database from a Node.js application.<\/p>\n\n\n\n

This tutorial picks up where the connecting to the MySQL Database Server from the Node.js<\/a> tutorial left off.<\/p>\n\n\n\n

Creating tables<\/h2>\n\n\n\n

To create a table from Node.js, you use these steps:<\/p>\n\n\n\n

    \n
  1. Connect to the MySQL database server.<\/a><\/li>\n\n\n\n
  2. Call the query()<\/code> method on the connection<\/code> object to execute a CREATE TABLE<\/code><\/a> statement.<\/li>\n\n\n\n
  3. Close the database connection.<\/li>\n<\/ol>\n\n\n\n

    Create a new file called create_table.js<\/code> in the project directory and add the following code to the file:<\/p>\n\n\n

    let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\n\/\/ connect to the MySQL server<\/span>\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  const<\/span> createTodosTable = `create table if not exists todos(\n                          id int primary key auto_increment,\n                          title varchar(255) not null,\n                          completed bool not null default false\n                      )`<\/span>;\n\n  connection.query(createTodosTable, (err, results, fields) => {\n    if<\/span> (err) return<\/span> console<\/span>.log(err.message);\n  });\n\n  \/\/ close the connection<\/span>\n  connection.end((err<\/span>) =><\/span> {\n    if<\/span> (err) return<\/span> console<\/span>.log(err.message);\n  });\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, use the mysql<\/code> module to connect to the MySQL server:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, create a database connection with the parameters come from the .env<\/code> configuration file:<\/p>\n\n\n
    let<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here’s the contents of the .env<\/code> file:<\/p>\n\n\n
    DB_HOST=localhost\nDB_PORT=3306\nDB_USER=user\nDB_PASSWORD=password\nDB_NAME=todoapp<\/code><\/span><\/pre>\n\n\n
    Third, connect to the MySQL server:<\/p>\n\n\n
    connection.end((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.log(err.message);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    If an error occurs when making a connection to the database, display the error message.<\/p>\n\n\n\n
    Fourth, define an SQL query to create a table named todos. The todos<\/code> table has three columns:<\/p>\n\n\n\n