CODEWITHSUNDEEP
flyway
Peter Branforn
Peter Branforn

Reputation: 1677

Flyway usage: what exactly is the migration concept?

I looked at the Flyway samples and documentation and tried to understand if it is useful in my environment. The following conceptual detail is unclear to me: How does Flyway manage the changes between database versions? It obviously does NOT compare database life-instances (see answer here:Can Flyway find out and generate migration files from datamodel?)

In detail my setup looks like this: I create SQL create and insert scripts when coding (automatically and manually). This means every version of my database is represented by a number of insert/create statements. In my world I execute these scripts through a database tool (sqlplus from Oracle). Each run would setup the database _from_scratch_ (!).

Can I put these very same scripts 1 to 1 inside the "migration" path of Flyway? What happens if the target database is way older than the last "migration step" I did (or flyway did not yet exist when it was installed)?

Update:

I got some input from another Flyway user:

It seems like each "migration" (version of the database) has to be hand-written SQL/Java code and contains only "updates" from the previous "migration" of database.

If this is true, I wonder how this can be used with traditional coding technics: in my world SQL statements are generated automatically and contain all database init/create statements, not just "updates" to some previous version. If my SQL code generator could do that, then I wouldn't even need a tool like Flyway :-).

Upvotes: 6

Views: 5474

Answers (2)

vkp
vkp

Reputation: 1

DECLARE
    story_name VARCHAR2(100) := 'Inserting data into SYS_PARM table with ACS Transactions related information';
    expected_pre_result PLS_INTEGER := 0;
    expected_post_result PLS_INTEGER := 7; -- Assuming there are 7 records to be inserted
    prevalidation_failed_msg VARCHAR2(250) := 'Prevalidation for ' || story_name || ' failed. Expected result: ' || expected_pre_result;
    postvalidation_failed_msg VARCHAR2(250) := 'Postvalidation for ' || story_name || ' failed. Expected result: ' || expected_post_result;
    pre_result PLS_INTEGER := 0;
    post_result PLS_INTEGER := 0;
    prevalidation_failed EXCEPTION;
    postvalidation_failed EXCEPTION;
    unexpected_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(prevalidation_failed, -20000);
    PRAGMA EXCEPTION_INIT(postvalidation_failed, -20001);
    PRAGMA EXCEPTION_INIT(unexpected_error, -20002);
BEGIN
    -- Pre-validation SQL: Check the initial count of records in SYS_PARM table
    SELECT COUNT(*) INTO pre_result FROM MCSENDPERF_OWNER.SYS_PARM;
    
    -- Test for expected pre_result
    IF pre_result = expected_pre_result THEN
        -- Insert data into SYS_PARM table
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'PARTNER_ID', '123,456,8831');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'AMOUNT', '120');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'PRCSSR_ID', '90000022922');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'DAILY_LIMIT_CURR_NUM_CD_TXT', '986');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'ACT_NUM', '541311313131331');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'MAX_ACS_TRANSACTION_COUNT', '10');
        INSERT INTO MCSENDPERF_OWNER.SYS_PARM (PARM_TYPE_NAM, PARM_NAM, PARM_VAL) VALUES ('PERF_POC_BATCH', 'SNGL_DUAL_MSG_CD', 'SINGLE');
        
        -- Post-validation SQL: Check the count of records in SYS_PARM table after insertion
        SELECT COUNT(*) INTO post_result FROM MCSENDPERF_OWNER.SYS_PARM;
        
        -- Test for expected post_result
        IF post_result = expected_post_result THEN
            -- Post-validation check passed
            DBMS_OUTPUT.PUT_LINE('Data inserted successfully into SYS_PARM table.');
        ELSE
            -- Post-validation check failed
            RAISE_APPLICATION_ERROR(-20001, postvalidation_failed_msg || '. Actual result was ' || post_result);
        END IF;
        
        -- Commit the transaction
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Change applied');
    ELSE
        -- Pre-validation check failed
        RAISE_APPLICATION_ERROR(-20000, prevalidation_failed_msg || '. Actual result was ' || pre_result);
    END IF;
EXCEPTION
    WHEN prevalidation_failed THEN
        ROLLBACK;
        RAISE;
    WHEN postvalidation_failed THEN
        ROLLBACK;
        RAISE;
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Unknown error occurred. Rollback executed');
        RAISE_APPLICATION_ERROR(-20002, 'An unexpected error was encountered for ' || story_name || '. SQLERRM: ' || SQLERRM);
END;

Upvotes: 0

Lance Kind
Lance Kind

Reputation: 1091

Your question about "how to handle a DB that has a longer history than there are migration scripts?" You need to create a V1_ migration/sql script that matches/recreates your latest DB schema. Something that can take a blank DB to what you have today. Create/generate that sql script using your existing DB tools and then put it in flyways migration directory. (And test V1 by using flyway against a clean DB and see if you get what you expect.) http://flywaydb.org/documentation/existing.html

After that point in time, all later versions must be added in as you work. When you decide you need a new table, in your dev environment, write a new V*_.sql that modifies your schema to the way you need it.

This blog goes over this situation for a Spring/SQL application. https://blog.synyx.de/2012/10/database-migration-using-flyway-and-spring-and-existing-data/

Upvotes: 4

Related Questions