Skip to content

npm data migration and data seeding package for mysql, mariadb

License

Notifications You must be signed in to change notification settings

helloakn/mysql-migrator

Repository files navigation

mysql-migrator

npm migration package for mysql, mariadb

Star Count Licence Language js-standard-style

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"
  },
...

(back to top)

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

(back to top)

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

(back to top)

Thank You for Visiting to my repo. :)

Releases

No releases published

Packages

No packages published