mysql-migrator

npm migration package for mysql, mariadb

Table Of contents

• Installation
• Setup
  • FOR ES6
  • FOR CommonJs
  • Update package.json
• Table Migration
  • create migration
  • code sample for table migration file
  • run migration file
    • up
    • rollback
• Data Seeding
  • create seeding
  • code sample for data seeding file
  • run seeding file
    • up
    • rollback

Installation

npm install mysql-migrator

Setup

create migrator.js with the following code.

FOR ES6

import { Migrator, Output } from 'mysql-migrator'

import path from 'path'
import { fileURLToPath } from 'url'

const __filename = fileURLToPath(import.meta.url)
const __dirname = path.dirname(__filename)

const dbConfig = {
  host: 'localhost',
  user: 'your-user-name',
  port: 3306,
  password: 'your-password',
  database: 'your-database'
}
const migrationsPath = __dirname + '/migrations'
const migrator = new Migrator(dbConfig, migrationsPath)
const result = await migrator.init()
Output(result)
process.exit()

FOR CommonJs

const { Migrator, Output } = require('mysql-migrator')

const dbConfig = {
  host: 'localhost',
  user: 'your-user-name',
  port: 3306,
  password: 'your-password',
  database: 'your-database'
}
const migrationsPath = __dirname + '/migrations'
const migrator = new Migrator(dbConfig, migrationsPath)
const result = await migrator.init()
Output(result)
process.exit()

Update package.json

...
  "scripts": {
    ...
    "migrate": "node migrator.js"
  },
...

Table Migration

create migration file

node migrator.js migration:create create_table_user

or

npm run migrate migration:create create_table_user

code sample for table migration file

there are two function up and rollback functions.

• up is for upgrading
• rollback is for downgrading. to use this function when you need to roll back to previous batch.

//write sql statement to create or modify
module.exports = {
 up: async (tbl) => {
   return await tbl.create('tblUser', {
     id: 'int NOT NULL PRIMARY KEY AUTO_INCREMENT',
     name: 'varchar(254) NOT NULL'
   })
 },
 rollback: async (tbl) => {
   return await tbl.dropTable('tblUser')
 }
}

run migration file

up

node migrator.js migration:up

or

npm run migrate migration:up

rollback

node migrator.js migration:rollback

or

npm run migrate migration:rollback

Data Seeding

create seeding file

node migrator.js seeding:create user_data_seeding

or

npm run migrate seeding:create user_data_seeding

code sample for data seeding file

there are two function up and rollback functions.

• up is for upgrading
• rollback is for downgrading. to use this function when you need to roll back to previous batch.

//write sql statement to create or modify
module.exports = {
 up: async (_query) => {
   const queryString = 'INSERT INTO tblUser VALUES(1,"hello")'
   await _query(queryString)
 },
 rollback: async (_query) => {
   const queryString = 'TRUNCATE TABLE tblUser'
   await _query(queryString)
 }
}

run seeding file

up

node migrator.js seeding:up

or

npm run migrate seeding:up

rollback

node migrator.js seeding:rollback

or

npm run migrate seeding:rollback

Thank You for Visiting to my repo. :)