I once join a team that using postgres and sequelize as their ORM, and they have dozens of migration files, when i try to migrate those files, i got a lot of error that saying the same problem coming from migration files.
some error like:
parent: error: column "column_name" of relation "table_name" does not exist
If i try to fix it one by one it will be a lot of work and time consuming, and if in the future we dont maintenance the migration files regurarly, the same issues will come.
Their solution was giving onboarding member dump files for each databases that used on this project from full timer developer. one of the dump files size was around 900 Mb and when i try to restore the dump files, the data that it contain took around 8 Gb of my SSD. I personally doesnt want my storage get decreased by the large volume of the data from dump files, moreover the process of restoring these files can be time consuming.
As another solution, i make a workaround flow to overcome this issue:
- Assume that there is dump folder contains database structure (tables only)
- Execute a command to restore the dump file to the database
- Execute a command to update the dump file.
- If migrate or undo from sequelize then do point 3
This is the solution flowchart:
With this, we can overcome the issue:
- Error prone migration process: this solution provides alternative method that is less prone to errors.
- Cumbersome manual fixes: as stated, fixing errors on each migration files individually would take a lot of work and time consuming. This solution reduces the efforts to fix each migration files.
- Lack of maintenance: This flow providing a mechanism for updating the database structure from and to a dump file.
- Quickly get started: With a single command, we can get the project set up with a new blank database, avoiding the need to restore large dump files and saving significant storage space on our systems.
NOTE:
This is just experimenting from my side, and i think there is maybe a better way to handle this problem. Please let me know what do you think about this solution, like: is there any cons on this solution?, how we can improve it?, etc.
Now that we got the idea, how do we implement it with codes? keep reading!
Installation
First thing first we need to install postgresql, make sure its up and running you can also use docker but in this blog i will use postgresql v14 installed using brew.
Now we need to install sequelize, sequlize cli and postgres driver using npm.
npm i sequelize pg && npm i -D sequelize-cli
After installing sequelize now we need to create empty sequelize project using sequelize-cli
npx sequelize-cli init
This will create following folders
- config, contains config file, which tells CLI how to connect with database
- models, contains all models for your project
- migrations, contains all migration files
- seeders, contains all seed files
Also dont forget to tell sequelize how to connect to database by adjusting config/config.json
.
Lastly we need a folder to store our dump files, lets name it dump
.
Now we are ready to create our first migration. Let's create a model named User
:
npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string
Implementation
We are ready for the implementation, let's create shell script named make
and preparing our commands. It will look like this:
#!/bin/sh
case ${1} in
"db:dump:update")
;;
"db:dump:restore")
;;
"db:migrate")
;;
"db:migrate:undo")
;;
"db:migrate:undo:all")
;;
Now our script can handle the commands we need. Let's build the logic one by one:
db:dump:update
This command will create dump file from a database
#!/bin/sh
case ${1} in
"db:dump:update")
if test ! -z "${2}";then # Check second argument which will be used as `database_name`
pg_dump -U postgres -d "${2}" -s -f "dump/pg-${2}-tables.sql"; # Tell postgres to create dump file from a database (tables only)
{
echo "CREATE DATABASE ${2} WITH TABLESPACE = pg_default;";
echo '\\c' "${2};";
cat "dump/pg-${2}-tables.sql";
} > temp && mv temp "dump/pg-${2}-tables.sql"; # Concat value of the echos to our dump file (Create database and connect to the created database)
echo "[!]Bash: UPDATE DUMP ${2} COMPLETED";
else
echo "[!]Bash: This command must give parameter. usage ${0} ${1} 'database_name'"
fi
;;
...
...
db:dump:restore
This command will handle database restoration from a dump file
#!/bin/sh
case ${1} in
...
"db:dump:restore")
if test ! -z "${2}";then # Check second argument which will be used as `database_name`
psql -U postgres -a -f "dump/pg-${2}-tables.sql"; # Tell postgres to restore this dump file
echo "[!]Bash: RESTORE DUMP ${2} COMPLETED";
else
echo "[!]Make: This command must give parameter. usage ${0} ${1} 'database_name'"
fi
;;
...
db:migrate
Handle flow sequelize migration command
#!/bin/sh
case ${1} in
...
"db:migrate")
if test ! -z "${2}";then
if [[ ! -e "dump/pg-${2}-tables.sql" ]]; then # Check dump file if not exist then create one
touch dump/pg-${2}-tables.sql
fi
if psql -lqt | cut -d \| -f 1 | grep ${2}; then # Check is database exist if not then create one
echo "[!]Bash: Database ${2} exists"
else
echo "[!]Bash: Database ${2} doesnt exist";
createdb -U postgres ${2}
echo "[!]Bash: Database ${2} created"
fi
sequelize-cli db:migrate --debug --url "postgres://postgres:postgres@localhost:5432/${2}" # Sequelize command to migrate migration files into the database
npm run db:dump:update ${2} # Update dump file from database
else
echo "[!]Bash: This command must give ENV parameter. usage: ${0} ${1} 'database_name'";
fi
;;
...
db:migrate:undo
Handle flow sequelize undo migration command
#!/bin/sh
case ${1} in
...
"db:migrate:undo")
if test ! -z "${2}";then
sequelize-cli db:migrate:undo --debug --url "postgres://postgres:postgres@localhost:5432/${2}" # Sequelize undo migration
npm run db:dump:update ${2} # Update dump file from database
else
echo "[!]Bash: This command must give ENV parameter. usage: ${0} ${1} 'database_name'";
fi
;;
...
db:migrate:undo:all
Handle flow sequelize undo migration command
#!/bin/sh
case ${1} in
...
"db:migrate:undo:all")
if test ! -z "${2}";then
sequelize-cli db:migrate:undo:all --debug --url "postgres://postgres:postgres@localhost:5432/${2}" # Sequelize undo all migration
npm run db:dump:update ${2} # Update dump file from database
else
echo "[!]Bash: This command must give ENV parameter. usage: ${0} ${1} 'database_name'";
fi
;;
...
Final Touch
for the final touch we can store our bash script commands to package.json
:
{
...
"scripts": {
"db:migrate": "./make db:migrate",
"db:migrate:undo": "./make db:migrate:undo",
"db:migrate:undo:all": "./make db:migrate:undo:all",
"db:dump:update": "./make db:dump:update",
"db:dump:restore": "./make db:dump:restore"
},
...
}
Now we can use the commands using npm.
Execute Commands
All settled up, let's migrate our User
model we create earlier on installation section to a database named test
, by execute:
npm run db:migrate test
Output:
Every time you want to set a project with blank new database, you can restore the dump file, by execute:
npm run db:dump:restore test
Let's test the migration undo command, by execute:
npm run db:migrate:undo test
Output:
That's it, you can access full code here, i hope this information helps you in some way. Lastly i want to know how your team handle this issue? please share your experience on the comment section.
Top comments (0)