MySQL database Nodejs, initial tables query

Question

I am creating a Nodejs MySQL database website. I have decided what schemas I need for a bunch of tables. As of right now I am using the mysql workbench to create tables and connecting to mysql database. Should I store all the create table schemas in a folder somewhere in Nodejs and run it using node and mysql package?. Should I use it to create new tables in production as I will be wiping all data fairly often? I have found very little directions online on how this is usually done.

Any advice will be greatly appreciated

madz · Accepted Answer · 2019-05-05 21:18:39Z

11

All your concerns have been previously answered by Database Migration concept.

Database migration takes care of initialization of your schema with tables. It also takes care of production.

Check out db-migrate. Its a popular db migration tool in node.js.

It helps you create automation scripts. Also check out this excellent tutorial for a step by step guide

answered May 5, 2019 at 21:18

madz

1,88322 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jedu Over a year ago

This was pretty helpful but I want to do more than just track schema change made to the database. Is there a way to also revert back the data. If I change data of my dev database while I am testing various things and than want to pull all the data and the schema of the database that is deployed

madz Over a year ago

db migration also takes care of synchronising multiple databases including dev and production. But reverting data back is completely another concern. You must have a backup plan which can be done with most databases directly. For example schedule backup every day and apply a previous backup if necessary.

jedu Over a year ago

Thank you for the info I will look into it further

Rashomon · Accepted Answer · 2019-05-05 21:40:56Z

Knex is good solution for database migrations:

You have to set up your enviroment connection/database at knexfile.js:

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  migrations: {
    directory: 'migrations'
  }
});

Then at /migrations create the desired initial tables:

exports.up = function(knex) {
  return knex.schema
    .createTable('user', function (table) {
       table.increments('id');
       table.string('first_name', 255).notNullable();
       table.string('last_name', 255).notNullable();
    })
    .createTable('product', function (table) {
       table.increments('id');
       table.decimal('price').notNullable();
       table.string('name', 1000).notNullable();
    });
};

exports.down = function(knex) {
  return knex.schema
      .dropTable("product")
      .dropTable("user");
};

To create the tables use the following command:

$> knex migrate:latest

Find more information at https://knexjs.org/#Installation-migrations

Collectives™ on Stack Overflow

MySQL database Nodejs, initial tables query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related