Writing flyway migration scripts to create database tables

Once the database has been configured, for creating tables in postgres DB we will use flyway. The following properties should be configured in application.properties file to enable flyway migration:

spring.flyway.url=jdbc:postgresql://localhost:5432/postgres
spring.flyway.user=postgres
spring.flyway.password=postgres
spring.flyway.table=public
spring.flyway.baseline-on-migrate=true
spring.flyway.outOfOrder=true
spring.flyway.locations=classpath:/db/migration/main
spring.flyway.enabled=true

For adding the flyway scripts the following folder structure should be maintained:

Now, the migration files should be added in the main folder. Specific nomenclature should be followed while naming the file. The file name should be in the following format:

V[YEAR][MONTH][DAY][HR][MIN][SEC]__modulecode_ …_ddl.sql

Example: V20180920110535__tl_tradelicense_ddl.sql

We can reuse the flyway docker image and script already created in other services. Links for these files are attached below,copy paste this two files in db folder (this is required only while building the service on jenkins and deploying it to the DIGIT cluster and can be skipped for local development):

Flyway Docker Image

Script to run flyway migration

For this sample service, we will be using the following psql script to create the required tables -

CREATE TABLE eg_vt_registration(
 id character varying(64),
 tenantId character varying(64),
 assemblyConstituency character varying(64),
 applicationNumber character varying(64),
 applicantId character varying(64),
 dateSinceResidence bigint,
 createdBy character varying(64),
 lastModifiedBy character varying(64),
 createdTime bigint,
 lastModifiedTime bigint,
 CONSTRAINT uk_eg_tl_TradeLicense UNIQUE (id)
);
CREATE TABLE eg_vt_address(
   id character varying(64),
   tenantId character varying(64),
   doorNo character varying(64),
   latitude FLOAT,
   longitude FLOAT,
   buildingName character varying(64),
   addressId character varying(64),
   addressNumber character varying(64),
   type character varying(64),
   addressLine1 character varying(256),
   addressLine2 character varying(256),
   landmark character varying(64),
   street character varying(64),
   city character varying(64),
   locality character varying(64),
   pincode character varying(64),
   detail character varying(64),
   registrationId character varying(64),
   createdBy character varying(64),
   lastModifiedBy character varying(64),
   createdTime bigint,
   lastModifiedTime bigint,
   CONSTRAINT uk_eg_tl_address PRIMARY KEY (id),
   CONSTRAINT fk_eg_tl_address FOREIGN KEY (registrationId) REFERENCES eg_vt_registration (id)
     ON UPDATE CASCADE
     ON DELETE CASCADE
);