<\/p>\n
1. What are database migrations<\/strong><\/h5>\nDatabase migration allows you to make updates to your data model be executing a script in your application. This could be JavaScript or Typescript. The benefit is that your database would always be in sync with the models in your application. Also, when something changes in your database, you will be able to generate the changes. Another benefit is that you can used migrations to initialize a new database when application is deployed.<\/p>\n
These script in turn executes SQL statement which run against your database and make the needed changes.<\/p>\n
<\/p>\n
2. Setup Node.js Connection to PostgreSQL<\/strong><\/h5>\nNow we would create a PostgreSQL database, a Node.js application and connect the two. Follow the steps below to get this done.<\/p>\n
Step 1<\/strong> – Create a database in PostgreSQL using PgAdmin. I named it migrationdb. Add a table named Users. Add some data<\/p>\nStep 2<\/strong> – Create a Node.js application.<\/p>\nStep 3<\/strong> – home.js. The content is as follows:<\/p>\nconst<\/span> {Client} =<\/span> require('pg'<\/span>)\r\n\r\nconst<\/span> client =<\/span> new<\/span> Client({\r\n host:<\/span> \"localhost\"<\/span>,\r\n port : 5432<\/span>,\r\n user:<\/span> \"postgres\"<\/span>,\r\n password:<\/span> \"password\"<\/span>,\r\n database:<\/span> \"migrationdb\"<\/span>\r\n})\r\n\r\nclient.connect();\r\n\r\nlet<\/span> query =<\/span> `<\/span>Select *<\/span> from \"Users\"<\/span>`<\/span>;\r\n\r\nclient.query(query, (err, res)=><\/span>{\r\n if<\/span>(!<\/span>err){\r\n console.log(res.rows);\r\n } else<\/span>{\r\n console.log(err.message)\r\n }\r\n client.end;\r\n})\r\n<\/pre>\nAt his point, you can run the index.js file using the node command, you will see that the data is displayed in the console. Now, we would add migration.<\/p>\n
<\/p>\n
3. Create Migration using db-migrate<\/strong><\/h5>\nTo create migration, we need to install the db-migrate module. To do that,<\/p>\n
Step 1<\/strong> – Run the commands below one after the other:<\/p>\nnpm install -<\/span>g db-<\/span>migrate\r\nnpm install -g db-migrate-pg\r\n<\/pre>\nNormally db-migrate also needs to connect to the database.<\/p>\n
Step 2<\/strong> – And to make this connection, you need to create a database.json file with the content<\/p>\n{\r\n \"dev\"<\/span>: {\r\n \"driver\"<\/span>: \"pg\"<\/span>,\r\n \"user\"<\/span>: \"postgres\"<\/span>,\r\n \"password\"<\/span>: \"password\"<\/span>,\r\n \"host\"<\/span>: \"localhost\"<\/span>,\r\n \"database\"<\/span>: \"migrationdb\"<\/span>\r\n }\r\n }\r\n<\/pre>\n <\/p>\n
Step 3<\/strong> – Now run the command db-migrate –sql-file<\/p>\ndb-migrate create initialize --sql-file\r\n<\/pre>\nIf the command runs successfully, you will notice that a migrations folder is created. This folder contains a sub-folder named sqls which contains two files ending with ..initialize-up.sql and initialize-down.sql. This is where you will write your sql queries.<\/p>\n
<\/p>\n
4. Creating SQL Scripts and Running Migrations<\/strong><\/h5>\nNow if you know how to write SQL statements you can just write the create table command in the ..initialize-up.sql file. Then write the drop table command in the intialize-down.sql file. But now, let’s make life easy! Let generate the scripts. Follow the steps.<\/p>\n
Step 1<\/strong> – In PgAdmin, right-click on the Users table and choose Scripts > Create as shown below:<\/p>\n![\"How](\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/07\/Screenshot-2021-07-29-at-16.53.08.png\")
<\/a>How to generate PostgreSQL scripts from PgAdmin<\/figcaption><\/figure>\n <\/p>\n
Step 2<\/strong> – Copy the generated code into your …initialize-up.sql file. It should look like this:<\/p>\n-- Table: public.Users<\/span>\r\n\r\n-- DROP TABLE public.\"Users\";<\/span>\r\n\r\nCREATE<\/span> TABLE<\/span> public<\/span>.\"Users\"<\/span>\r\n(\r\n id bigint<\/span>,\r\n firstname character<\/span> varying<\/span> COLLATE<\/span> pg_catalog.\"default\"<\/span>,\r\n lastname character<\/span> varying<\/span> COLLATE<\/span> pg_catalog.\"default\"<\/span>\r\n)\r\n\r\nTABLESPACE pg_default;\r\n\r\nALTER<\/span> TABLE<\/span> public<\/span>.\"Users\"<\/span>\r\n OWNER<\/span> to<\/span> postgres;\r\n<\/pre>\n <\/p>\n
Step 3<\/strong> – Write the ‘Drop Table’ statement in the …initialize_down.sql file.<\/p>\nStep 4<\/strong> – Now, you can delete the table from your database using PgAdmin<\/p>\nStep 5<\/strong> – In node.js, run the command below:<\/p>\ndb-migrate up initialize\r\n<\/pre>\n <\/p>\n
Now we would create a PostgreSQL database, a Node.js application and connect the two. Follow the steps below to get this done.<\/p>\n
Step 1<\/strong> – Create a database in PostgreSQL using PgAdmin. I named it migrationdb. Add a table named Users. Add some data<\/p>\n Step 2<\/strong> – Create a Node.js application.<\/p>\n Step 3<\/strong> – home.js. The content is as follows:<\/p>\n At his point, you can run the index.js file using the node command, you will see that the data is displayed in the console. Now, we would add migration.<\/p>\n <\/p>\n To create migration, we need to install the db-migrate module. To do that,<\/p>\n Step 1<\/strong> – Run the commands below one after the other:<\/p>\n Normally db-migrate also needs to connect to the database.<\/p>\n Step 2<\/strong> – And to make this connection, you need to create a database.json file with the content<\/p>\n <\/p>\n Step 3<\/strong> – Now run the command db-migrate –sql-file<\/p>\n If the command runs successfully, you will notice that a migrations folder is created. This folder contains a sub-folder named sqls which contains two files ending with ..initialize-up.sql and initialize-down.sql. This is where you will write your sql queries.<\/p>\n <\/p>\n Now if you know how to write SQL statements you can just write the create table command in the ..initialize-up.sql file. Then write the drop table command in the intialize-down.sql file. But now, let’s make life easy! Let generate the scripts. Follow the steps.<\/p>\n Step 1<\/strong> – In PgAdmin, right-click on the Users table and choose Scripts > Create as shown below:<\/p>\n <\/p>\n Step 2<\/strong> – Copy the generated code into your …initialize-up.sql file. It should look like this:<\/p>\n <\/p>\n Step 3<\/strong> – Write the ‘Drop Table’ statement in the …initialize_down.sql file.<\/p>\n Step 4<\/strong> – Now, you can delete the table from your database using PgAdmin<\/p>\n Step 5<\/strong> – In node.js, run the command below:<\/p>\n <\/p>\nconst<\/span> {Client} =<\/span> require('pg'<\/span>)\r\n\r\nconst<\/span> client =<\/span> new<\/span> Client({\r\n host:<\/span> \"localhost\"<\/span>,\r\n port : 5432<\/span>,\r\n user:<\/span> \"postgres\"<\/span>,\r\n password:<\/span> \"password\"<\/span>,\r\n database:<\/span> \"migrationdb\"<\/span>\r\n})\r\n\r\nclient.connect();\r\n\r\nlet<\/span> query =<\/span> `<\/span>Select *<\/span> from \"Users\"<\/span>`<\/span>;\r\n\r\nclient.query(query, (err, res)=><\/span>{\r\n if<\/span>(!<\/span>err){\r\n console.log(res.rows);\r\n } else<\/span>{\r\n console.log(err.message)\r\n }\r\n client.end;\r\n})\r\n<\/pre>\n3. Create Migration using db-migrate<\/strong><\/h5>\n
npm install -<\/span>g db-<\/span>migrate\r\nnpm install -g db-migrate-pg\r\n<\/pre>\n{\r\n \"dev\"<\/span>: {\r\n \"driver\"<\/span>: \"pg\"<\/span>,\r\n \"user\"<\/span>: \"postgres\"<\/span>,\r\n \"password\"<\/span>: \"password\"<\/span>,\r\n \"host\"<\/span>: \"localhost\"<\/span>,\r\n \"database\"<\/span>: \"migrationdb\"<\/span>\r\n }\r\n }\r\n<\/pre>\ndb-migrate create initialize --sql-file\r\n<\/pre>\n
4. Creating SQL Scripts and Running Migrations<\/strong><\/h5>\n
<\/a>-- Table: public.Users<\/span>\r\n\r\n-- DROP TABLE public.\"Users\";<\/span>\r\n\r\nCREATE<\/span> TABLE<\/span> public<\/span>.\"Users\"<\/span>\r\n(\r\n id bigint<\/span>,\r\n firstname character<\/span> varying<\/span> COLLATE<\/span> pg_catalog.\"default\"<\/span>,\r\n lastname character<\/span> varying<\/span> COLLATE<\/span> pg_catalog.\"default\"<\/span>\r\n)\r\n\r\nTABLESPACE pg_default;\r\n\r\nALTER<\/span> TABLE<\/span> public<\/span>.\"Users\"<\/span>\r\n OWNER<\/span> to<\/span> postgres;\r\n<\/pre>\ndb-migrate up initialize\r\n<\/pre>\n