mysql-migrator v2.0.0

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
```bash
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
```bash
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. :)