Navigate back to the homepage

Using Sequelize Migrations With An Existing Database

Bulkan Evcimen
January 20th, 2014 · 1 min read

Photo by Kolar.io on Unsplash

Install

Im sure you know know how to install packages but here is the command for the sake of completeness

npm install sequelize async

The first migration

First initialize the migrations structure

sequelize --init

Then create the initial migration, but dont edit this file as we will use it create the SequelizeMeta table.

sequelize -c initial

Create another migration

sequelize -c create-tables

Dump the database

Now dump your database without the data. With mysqldump

mysqldump -d --compact --compatible=mysql323 ${dbname}|egrep -v "(^SET|^/\*\!)".

We need to remove the lines beginning or containing SET

Save this dump to the migrations folder and name it initial.sql

Edit the last migration that was created to look like;

1var async = require('async')
2 , fs = require('fs');
3
4module.exports = {
5 up: function(migration, DataTypes, done) {
6 var db = migration.migrator.sequelize;
7
8 async.waterfall([
9 function(cb){
10 fs.readFile(__dirname + '/initial.sql', function(err, data){
11 if (err) throw err;
12 cb(null, data.toString());
13 });
14 },
15
16 function(initialSchema, cb){
17 // need to split on ';' to get the individual CREATE TABLE sql
18 // as db.query can execute on query at a time
19 var tables = initialSchema.split(';');
20
21 function createTable(tableSql, doneCreate){
22 db.query(tableSql).complete(doneCreate);
23 }
24
25 async.each(tables, createTable, cb);
26 }
27 ], done);
28 },
29
30 down: function(migration, DataTypes, done) {
31 migration.showAllTables().success(function(tableNames){
32
33 // Dont drop the SequelizeMeta table
34 var tables = tableNames.filter(function(name){
35 return name.toLowerCase() !== 'sequelizemeta';
36 });
37
38 function dropTable(tableName, cb){
39 migration.dropTable(tableName).complete(cb);
40 }
41
42 async.each(tables, dropTable, done);
43 });
44 }
45}

Explanation

On the migrations up function we use async.waterfall to orchestrate a the async calls;

  • read in the initial.sql file
  • need to split the initial.sql and retrieve each CREATE TABLE queries as db.query can execute on query at a time
  • using async.each run each of these queries

On the migrations down function we just remove all tables that is not the SequelizeMeta table. For some reason migration.dropAllTables() remove this table and messes up the migrations. Not sure if this is the correct behavior.

Hope this helps

More articles from Bulkan

Using Custom Events With LimeJS

Photo by Jason Dent on Unsplash LimeJS is an open source JavaScript HTML5 game creation framework built using Google Closure. In this…

April 29th, 2013 · 2 min read

Export Test Cases From Quality Center Using Python

Photo by Dieter de Vroomen on Unsplash Here is a Python script that will export out test cases in a folder from Quality Center into a CSV…

May 10th, 2011 · 1 min read
© 2007–2020 Bulkan
Link to $https://twitter.com/bulkanevcimenLink to $https://github.com/bulkanLink to $https://instagram.com/bulkan.evcimen