How to create database and asociate table with it in Mysql NODEJS

Question

I am setting up mysql/nodejs app.

I would like to delete database, recreate it and create table on each server restart.

If I specify connection with database:

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

I can create table, indexes, insert values but just first time. Every subsequent time it will tell me that everything is already created.

If on the other hand I do not put database when I create connection, I can delete database, create new one, but when I try to create a table I get an error that I don't have a database associated with this table.

Any idea how to get around this?

nbk · Accepted Answer · 2021-07-31 14:18:35Z

you don't need the 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);
    });
});

and use

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

to 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 split ito seperate function only to show the idea.

Harsh Gundecha · Accepted Answer · 2021-07-31 12:55:59Z

0

Doing it manually.
- If the user you are connected with is having all the privileges like a superuser, you can code it to drop database every time the server starts up (like in server.js or app.js depending upon your design) by executing the following.
```
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
```
- PS: This is not something to be done in production environment so make sure you either remove it before final deployment or make it run as per env when env!=production.
Using a DB migration library for example -> https://db-migrate.readthedocs.io/en/latest/Getting%20Started/commands/#dbdrop
- DB dropping/recreating is not the primary use case of migration(migration = making changes to DB automatically when server boots), migration in itself is a process which is automated by these libraries.

edited Jul 31, 2021 at 12:55

answered Jul 31, 2021 at 12:50

Harsh Gundecha

1,23511 silver badges16 bronze badges

2 Comments

SrdjaNo1 Over a year ago

But I can't code it up? Meaning I drop and create database with con.query and then once that is finished I create a table and somehow associate it with this database?

Harsh Gundecha Over a year ago

Right, you need to put it in that sequence where first the conditional dropping happens and later the creation, also check the updated answer.

Collectives™ on Stack Overflow

How to create database and asociate table with it in Mysql NODEJS

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related