MySQL NODEJS: A Complete Guide to Creating Databases and Related Tables

Question

I'm setting up a mysql/nodejs application.

I want to drop the database, recreate the database, and create the tables every time the server restarts.

If I specify a connection to the database:

let con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "pass",
  database: "my_db",
});

I can create tables, indexes, and insert values, but only the first time. Every time after that it tells me that everything has been created.

On the other hand, if I don't specify the database when creating the connection, I can drop the database, create a new one, but when I try to create the table, I get an error saying I don't have a database associated with the table .

Is there any way to solve this problem?

Answer 1

You don’t need to use a database in the connection

var pool  = mysql.createPool({
      connectionLimit : 10,
      host            : 'example.org',
      user            : 'bobby',
      password        : 'pass'
    });

After that you can create the database

pool.getConnection(function(err, connection){
    if(err){
        return cb(err);
    }
    connection.query("CREATE DATABASE mydb", function(err, data){
        connection.release();
        cb(err, data);
    });
});

Then use

connection.changeUser({database : "mydb"});

Connect to the newly created database

pool.getConnection(function(err, connection){
    if(err){
        return cb(err);
    }
    connection.changeUser({database : "mydb"});
  let createTodos = `create table if not exists mytable(
                          id int primary key auto_increment,
                          title varchar(255)not null,
                          testdata tinyint(1) not null default 0
                      )`;

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

This is just to show the idea and split it into separate functions.