Persistence with PostgreSQL
Database Migrations with Flyway
আপনি একটি free preview lesson দেখছেন।
আমাদের database schema এখন আর শুধু design নয়।
আমরা ঠিক করেছি:
products
inventory
orders
order_items
এবং প্রতিটি table-এর:
columns
primary keys
foreign keys
CHECK constraints
data types
define করেছি।
কিন্তু production system-এ একটি database schema একবার create করে তারপর forever unchanged থাকে না।
Application evolve করবে।
আজ হয়তো:
products
--------
id
name
price
active
কয়েক মাস পরে requirement আসতে পারে:
Product needs a new attribute
অথবা:
Order needs additional persisted state
তখন existing production database-কে safely নতুন schema-তে evolve করতে হবে।
Problem হলো production database-এ তখন already real data থাকবে।
আমরা simply করতে পারি না:
DROP DATABASE
recreate everything
এখানে প্রয়োজন:
Database Migration
এই course-এ আমরা database schema evolution manage করব:
Flyway
দিয়ে।
Flyway versioned migrations নির্দিষ্ট version order-এ apply করে এবং কোন migrations apply হয়েছে, তাদের checksum কী, এবং execution successful হয়েছিল কিনা—এসব schema history table-এ track করে। Applied versioned migration পরিবর্তন না করে নতুন migration দিয়ে roll forward করাই recommended practice।
এই lesson-এর goal:
Database schema-কে version-controlled code হিসেবে manage করা, Flyway দিয়ে deterministicভাবে evolve করা, এবং Hibernate নয়—migration history-কে production schema change-এর source of truth বানানো।
The Database Is Part of the Application
আমরা application code Git-এ রাখি:
Java source
configuration
tests
Database schema-ও application-এর behaviour-এর অংশ।
For example:
CHECK (available_quantity >= 0)
না থাকলে database integrity বদলে যায়।
Similarly:
FOREIGN KEY (order_id)
REFERENCES orders(id)
না থাকলে relational guarantees বদলে যায়।
তাই schema changes-ও হওয়া উচিত:
version controlled
reviewable
repeatable
deployable
The Wrong Way: Manual Production SQL
Imagine an engineer writes in Slack:
Please run this on production:
ALTER TABLE orders ...
Someone manually runs it।
Then maybe:
production
→ changed
but:
staging
→ unchanged
Another developer's local database:
→ different again
Soon nobody knows:
Which schema is correct?
This is:
schema drift
and it makes deployment unpredictable।
Migration as Code
Instead, a database change becomes a file committed with application code।
For example:
src/main/resources/db/migration/
may contain:
V1__create_products.sql
V2__create_inventory.sql
V3__create_orders.sql
V4__create_order_items.sql
Spring Boot's Flyway integration uses classpath:db/migration as the default migration location, and Flyway versioned SQL files conventionally follow the V<VERSION>__<DESCRIPTION>.sql pattern. Current Spring Boot documentation also notes PostgreSQL deployments use Flyway's PostgreSQL database module.
Understanding the Filename
Consider:
V3__create_orders.sql
Break it down:
V
→ versioned migration
3
→ migration version
__
→ separator
create_orders
→ human-readable description
.sql
→ SQL migration
Flyway requires each versioned migration to have a unique version and applies pending migrations according to version order.
Why Not Name It create_tables.sql?
Without versions:
create_tables.sql
change_orders.sql
fix_products.sql
there is no durable migration sequence।
We need to know:
V1
then
V2
then
V3
then
V4
because later migrations often depend on earlier ones।
Our Initial Migration History
Our current schema naturally evolves like:
V1
→ Product persistence
V2
→ Inventory persistence
V3
→ Order persistence
V4
→ OrderItem persistence
This also follows the dependencies:
Product must exist
before Inventory references Product
and:
Order + Product must exist
before OrderItem references them
V1__create_products.sql
Our first migration:
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC NOT NULL,
active BOOLEAN NOT NULL,
CONSTRAINT products_price_non_negative
CHECK (price >= 0)
);
Once Flyway applies this migration, PostgreSQL contains:
products
with our accepted Product integrity rules।
V2__create_inventory.sql
Next:
CREATE TABLE inventory (
product_id BIGINT PRIMARY KEY,
available_quantity INTEGER NOT NULL,
CONSTRAINT inventory_product_fk
FOREIGN KEY (product_id)
REFERENCES products(id),
CONSTRAINT inventory_quantity_non_negative
CHECK (available_quantity >= 0)
);
This migration depends on:
products
already existing।
Because:
V1 < V2
the sequence expresses that dependency clearly।
V3__create_orders.sql
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id TEXT NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT orders_status_valid
CHECK (
status IN (
'UNPAID',
'PAID',
'CANCELLED'
)
)
);
No Customer table is introduced।
customer_id still represents:
external identity
V4__create_order_items.sql
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL,
unit_price NUMERIC NOT NULL,
CONSTRAINT order_items_pk
PRIMARY KEY (
order_id,
product_id
),
CONSTRAINT order_items_order_fk
FOREIGN KEY (order_id)
REFERENCES orders(id),
CONSTRAINT order_items_product_fk
FOREIGN KEY (product_id)
REFERENCES products(id),
CONSTRAINT order_items_quantity_positive
CHECK (quantity > 0),
CONSTRAINT order_items_unit_price_non_negative
CHECK (unit_price >= 0)
);
Now an empty database can deterministically evolve through:
V1
↓
V2
↓
V3
↓
V4
into our current application schema।
Why Split the Initial Schema?
Could we instead create:
V1__create_initial_schema.sql
containing all four tables?
Yes।
That would also be valid।
For this course, separate migrations make schema evolution easier to follow because each capability appears incrementally:
Product
Inventory
Order
OrderItem
The important principle is not:
One table must equal one migration.
The important principle is:
One migration should represent a coherent database change.
Migration Boundaries Follow Changes, Not Tables
Later we might have:
V5__add_order_lookup_index.sql
which doesn't create a table at all।
Another migration might modify:
multiple related tables
if one feature requires an atomic/coherent schema change।
Migration files represent:
schema evolution steps
not database-object categories।
Adding Flyway to Spring Boot
With current Spring Boot Flyway integration, PostgreSQL needs Flyway support plus the PostgreSQL-specific Flyway database module. Spring Boot can then run Flyway migrations during application startup.
Conceptually in Gradle:
dependencies {
implementation(
"org.springframework.boot:spring-boot-starter-data-jpa"
)
implementation(
"org.springframework.boot:spring-boot-starter-flyway"
)
runtimeOnly(
"org.postgresql:postgresql"
)
runtimeOnly(
"org.flywaydb:flyway-database-postgresql"
)
}
Dependency versions should remain managed consistently by the Spring Boot dependency-management setup rather than hard-coded independently unless we have a reason to override them।
Migration Location
Project structure becomes:
src/
└── main/
├── java/
│ └── ...
└── resources/
├── application.yml
└── db/
└── migration/
├── V1__create_products.sql
├── V2__create_inventory.sql
├── V3__create_orders.sql
└── V4__create_order_items.sql
db/migration is Flyway's conventional Spring Boot classpath location, so we do not need custom migration-location configuration for this normal setup.
Startup Flow
When application starts, conceptually:
Spring Boot starts
↓
DataSource available
↓
Flyway checks migration history
↓
pending migrations identified
↓
migrations executed in order
↓
JPA/Hibernate starts against migrated schema
↓
application becomes ready
Spring Boot auto-configures Flyway migration execution and calls Flyway's migration process when the required Flyway modules are present.
The Schema History Table
Flyway creates a schema history table to remember migration state।
Conceptually it records information such as:
migration version
description
script
checksum
execution result
when it was applied
Flyway uses this history to distinguish migrations that are:
already applied
pending
failed
missing
out of order
and to validate that applied migration files still correspond to recorded history.
Why This Matters
Imagine production already has:
V1
V2
V3
applied।
A new deployment includes:
V1
V2
V3
V4
V5
Flyway does not rerun:
V1
V2
V3
as new versioned migrations।
It identifies:
V4
V5
as pending and applies them according to version order. Versioned migrations are designed to run once per target database.
This Makes Deployment Incremental
Production evolves:
schema version 3
to:
schema version 5
without destroying existing rows।
That is the central purpose of migration tooling।
Checksums Protect Migration History
Suppose:
V2__create_inventory.sql
was already applied to staging and production।
Later someone edits it:
available_quantity BIGINT
instead of:
available_quantity INTEGER
Now Git contains one definition while production applied another।
Flyway stores checksums for migrations and validation can detect this kind of history mismatch.
The Golden Rule
Once a versioned migration has reached a permanent/shared downstream environment:
Do not rewrite its history casually.
Instead, create a new migration।
Flyway's own guidance recommends creating a new versioned migration and rolling forward rather than modifying already-applied versioned migrations.
Example: We Made a Schema Mistake
Suppose production has already applied:
V1__create_products.sql
Then we discover Product needs a new column:
description
Wrong:
edit V1
Correct:
V5__add_product_description.sql
containing something like:
ALTER TABLE products
ADD COLUMN description TEXT;
Now every environment follows the same history।
Why Not Rewrite V1?
Because environments may be at different points:
Developer A
→ already ran V1
CI
→ already ran V1
staging
→ already ran V1
production
→ already ran V1
Changing V1 does not magically re-run it everywhere।
Instead it creates:
migration history disagreement
Local Development Exception?
If a migration has never left your machine and nobody else depends on it, rewriting it during early development may be harmless।
But once it is:
shared
merged
applied in CI
applied in non-production
applied in production
treat it as immutable history։
A good team habit is to prefer forward migrations early as well।
Roll Forward
Our normal database-change strategy is:
old schema
↓
new migration
↓
new schema
For example:
V4
↓
V5
↓
V6
This is:
roll forward
We don't attempt to make Git history pretend previous production schema never existed।
Rollback Is More Complicated Than Application Rollback
Suppose a deployment includes:
application code
+
V8 migration
and we discover a bug։
Rolling back application container to the previous image may be easy।
Rolling back database state may not be।
Why?
A migration may have:
transformed data
dropped information
changed constraints
added required fields
Database rollback strategy therefore needs release planning, not blind reversal।
We'll discuss deployment/rollback more deeply in Module 12।
Design Migrations for Compatibility
A powerful production technique is to make schema evolution compatible across application versions where possible։
For example, instead of immediately:
rename old_column
we may sometimes:
add new column
then:
deploy compatible code
then later:
remove old column
This reduces the chance that an old application instance and new schema cannot coexist during rollout।
The exact technique depends on the change।
Not Every Migration Is Safe Just Because Flyway Can Run It
Flyway can execute:
ALTER TABLE ...
but Flyway does not decide whether that operation:
locks a huge production table
rewrites millions of rows
causes downtime
breaks older application versions
Migration tooling provides ordering/history/execution।
Backend engineers still need to design safe SQL।
Migrations Are Production Code
Review:
V8__change_orders.sql
with the same seriousness as:
CreateOrderUseCase.java
A bad Java deployment can break requests।
A bad migration can damage:
persistent data
which may be much harder to recover।
Never Hide Destructive Changes
Migration names should make intent clear।
Prefer:
V8__drop_legacy_order_column.sql
over:
V8__fix.sql
The description should help reviewers understand:
what changes
without opening every file first।
One Migration, One Clear Intent
Prefer a migration that has a coherent purpose।
For example:
V10__add_order_history_index.sql
rather than a random file containing:
add index
rename unrelated Product column
insert test Product
drop old constraint
change admin data
just because all of those changes happened on the same day।
Coherent migrations are easier to:
review
debug
understand during incidents
Don't Use Migration Versions as Application Versions
We may eventually have:
V37__...
while our API is still:
/api/v1
and application release might be:
2.8.4
These numbers mean different things।
Flyway V37
→ 37th schema evolution point
/api/v1
→ major public HTTP contract
application 2.8.4
→ deployment/release version
Never couple them।
Hibernate Must Not Compete With Flyway
Once Flyway owns schema evolution, we do not want Hibernate simultaneously deciding:
I'll add this column
I'll alter this table
I'll recreate this constraint
That would give us two schema owners։
Spring Boot's database-initialization guidance recommends using one higher-level migration mechanism rather than mixing it with other schema initialization approaches.
Our ownership is:
Flyway
→ change database schema
Hibernate
→ map/use database schema
Hibernate Configuration
A production-style direction:
spring:
jpa:
hibernate:
ddl-auto: validate
Meaning conceptually:
Hibernate
→ check mapping compatibility
Hibernate
→ do not silently migrate schema
Flyway remains responsible for schema creation and changes।
Why Not ddl-auto=update?
update feels convenient:
change Entity
restart app
database changes automatically
But then our schema history is no longer fully represented by:
db/migration
A production database may evolve based on whichever entity mapping happened to deploy։
That makes changes less:
explicit
reviewable
repeatable
So we don't use Hibernate auto-update as our production migration strategy।
Why validate Is Useful
Suppose migration creates:
customer_id
but JPA entity accidentally maps:
customer_identifier
We want startup to expose that mismatch rather than silently generating a second schema interpretation।
Validation gives us an early signal:
migration and mapping disagree
Then we fix:
migration or mapping
deliberately।
Don't Use create-drop
Another common development configuration:
ddl-auto: create-drop
creates schema on startup and destroys it later।
That can be convenient for throwaway experiments।
But it teaches the wrong persistence workflow for this production-style project।
We want local development to exercise:
the same migration history
used elsewhere।
Local Development
Our existing local workflow:
docker compose up -d
starts PostgreSQL।
Then:
./gradlew bootRun
starts the application।
Flyway sees an empty local database:
no migrations applied
and runs:
V1
V2
V3
V4
Afterward JPA can operate against the migrated schema।
Next Startup
Application restarts।
Flyway sees:
V1–V4 already successful
and:
nothing pending
So it does not recreate the tables।
Application starts against the existing database state।
Then We Add V5
Developer pulls new code containing:
V5__add_something.sql
Next startup:
V1–V4
→ already applied
V5
→ pending
Flyway applies only V5।
This gives developers schema updates automatically through code version changes।
Avoid Manually Editing Local Schema
Suppose you need a new column।
Don't manually run:
ALTER TABLE ...
in your local database and stop there।
Create:
V5__...
so:
your machine
other developers
CI
staging
production
can all obtain the same change।
What If Local Database Is Disposable?
During early development you may occasionally delete and recreate local PostgreSQL volume।
That's fine।
But the important test becomes:
Can an empty database reach the current schema by running all migrations?
If yes, our migration history is reproducible।
Reproducibility Is Powerful
New engineer joins the team।
They don't need a 30-step document:
create these tables
run this patch
ask Sakib for that SQL
manually add this index
They need:
start PostgreSQL
start application
Migration history reconstructs the schema।
CI
In CI, integration tests should also start from a controlled PostgreSQL environment।
With Testcontainers:
new PostgreSQL container
↓
Spring Boot starts
↓
Flyway migrations run
↓
tests execute
Spring Boot supports Testcontainers for integration tests that need real backend services, and it also supports test-specific Flyway migrations when additional test setup is genuinely required.
This Tests the Migration Itself
If:
V4__create_order_items.sql
contains broken SQL, tests should fail before pretending the persistence layer works।
This is valuable because migration correctness is part of application correctness।
Don't Maintain a Separate Test Schema by Hand
Avoid:
production-schema.sql
test-schema.sql
that slowly diverge।
Integration tests should ideally build schema using:
the real migrations
so we're testing the same schema evolution the application deploys।
Test Data Is Different From Schema
Schema migration:
create table
add column
add constraint
create index
Test data:
Product P-1
Order O-1
are different concerns।
Prefer tests to create the data they need explicitly through:
repositories
fixtures
SQL setup
rather than putting random test records into production migration files।
Spring Boot supports test-only Flyway migration locations/files when a scenario genuinely benefits from them, but they remain separate from packaged production migrations.
Never Put Demo Data in Production Migration by Accident
Avoid:
INSERT INTO products (
name,
price,
active
)
VALUES (
'Test Product',
10,
true
);
inside a production schema migration unless that row is genuine required reference/business data।
Otherwise production receives development fixtures।
Reference Data Can Be a Migration
Sometimes data is truly required application state।
For example, if a system requires a fixed set of reference codes, migrating that data can be appropriate।
Flyway's versioned migrations are commonly used not only for structural schema changes but also for reference-data changes and data corrections.
Our current Order Management application does not yet need reference-data migrations।
Don't invent them।
Data Migrations
Schema changes sometimes require transforming existing data।
Example future requirement:
new non-null column
for a table already containing:
1,000,000 rows
We cannot simply add:
new_column TEXT NOT NULL
without considering existing rows।
We may need:
add nullable column
↓
backfill existing rows
↓
update application
↓
enforce NOT NULL later
depending on the migration and rollout strategy।
This is where migration design becomes real production engineering।
Migrations Must Consider Existing Data
An empty local database answers:
Does the final schema create successfully?
Production asks an additional question:
Can the existing data safely evolve into that schema?
These are not always the same problem।
Repeatable Migrations
Flyway also supports:
repeatable migrations
with names such as:
R__refresh_view.sql
Unlike versioned migrations, repeatable migrations can be reapplied when their checksum changes, and they run after pending versioned migrations. They are commonly used for replaceable database objects such as views or stored routines.
Do we need them now?
No.
Why We Don't Need Repeatables Yet
Our schema consists of:
tables
constraints
foreign keys
which fit naturally into versioned migrations।
We don't currently have:
stored procedures
database views
replaceable functions
that justify repeatable migrations।
Don't use every Flyway feature merely because it exists।
Java-Based Migrations
Flyway also supports Java migrations, and Spring Boot can discover JavaMigration implementations.
But our schema is PostgreSQL SQL।
Plain SQL migrations are preferable because the database change is:
direct
readable
reviewable by database engineers
easy to execute conceptually
Use Java migration only when a real migration requires logic that is substantially clearer in Java।
Don't Build Migration Logic Into Application UseCases
Avoid:
if (!columnExists()) {
alterDatabase();
}
inside application startup or a UseCase।
Schema evolution belongs to:
Flyway
not business workflows।
Migration Failure
Suppose:
V6
contains invalid SQL।
Migration fails։
What should application do?
Generally we do not want application to continue pretending it can operate against a schema that failed to reach the expected version।
A migration failure should fail deployment/startup visibly so engineers can investigate।
Don't Catch Flyway Failure and Continue
Bad mindset:
Migration failed.
Log warning.
Start application anyway.
Then JPA/application code may access:
missing table
wrong column
old constraint
and produce much more confusing runtime failures।
Failing early is safer।
Fixing a Failed Migration
Response depends on:
which environment
whether the database operation was transactional
what changes were already applied
whether the environment is disposable
Do not blindly:
edit history table
or:
run repair
just to make the error disappear।
Flyway's repair changes schema-history metadata and is intended for deliberate repair of known history inconsistencies; validation errors should first be understood and underlying incomplete/incorrect changes addressed.
repair Is Not "Make It Green"
This is dangerous:
Flyway validate failed
↓
run repair automatically
without understanding why।
Validation might be telling you:
someone modified an applied migration
That is valuable information।
Repair should be an intentional operational action, not a default response։
clean Is Dangerous Around Real Data
Flyway has database-management operations beyond normal migration workflows, but destructive reset operations should never become part of routine production startup.
Our production deployment path is:
validate/migrate forward
not:
wipe database
recreate schema
A production database is durable business state।
Treat destructive database commands accordingly։
Migration Ordering in a Team
Imagine two engineers branch from:
V10
Engineer A creates:
V11__add_product_field.sql
Engineer B also creates:
V11__add_order_field.sql
When branches merge:
duplicate migration version
must be resolved।
One migration should receive a new version:
V12
before merge।
Flyway requires unique versions for versioned migrations.
Timestamp Versions?
Flyway supports more than simple integer versions, and Redgate notes timestamp-like versions can reduce migration-number conflicts in teams working concurrently.
Example style:
V202608092300__add_order_field.sql
Could be useful in a large team։
But our course project is simple।
We'll use:
V1
V2
V3
style unless team workflow creates a real conflict problem।
Simple Is Enough Until It Isn't
Avoid premature process complexity।
If:
five engineers constantly create migrations in parallel
we can revisit versioning convention।
For now:
next integer migration version
is clear and readable।
Migration and Pull Requests
Any ticket requiring a schema change should include:
migration file
in the same engineering change।
For example:
BACKEND-104
Product persistence
might include:
ProductEntity
Repository implementation
V1__create_products.sql
integration tests
Reviewers can then examine:
Java assumptions
SQL schema
mapping
together।
Schema Change Is Part of Definition of Done
If a feature needs:
new column
but PR only changes:
@Entity
without a migration, it isn't production-ready।
Likewise, a migration adding a column that no code understands yet may require rollout planning।
Code and database changes need to evolve coherently।
Migration Naming Should Describe Intent
Good:
V7__add_customer_order_index.sql
V8__add_payment_reference.sql
Bad:
V7__changes.sql
V8__fix.sql
Months later, descriptive names reduce archaeological work।
Migration Should Be Deterministic
Running a migration should not depend on:
today's date
random application state
developer's machine
external HTTP service
unless a rare migration genuinely requires such dependency and is designed deliberately।
Schema migrations should be reproducible।
Avoid Environment-Specific Core Schema
Don't create:
production migrations
staging migrations
developer migrations
for the same core schema।
Core migration history should produce the same structural application schema everywhere।
Environment differences belong in:
configuration
not alternate versions of business tables।
Test-Only Data Can Be Separate
A test environment may legitimately need extra test data.
Spring Boot supports test-specific Flyway migrations in src/test/resources or additional test migration locations without packaging those migrations into the production application.
That is different from changing the core schema by environment।
One Source of Schema Truth
For this project:
src/main/resources/db/migration/
becomes the historical source of:
how the database reached its current schema
To understand current schema, engineers may inspect:
all migrations
or query the live database।
To understand why a change happened, they can also inspect:
ticket
PR
RFC/ADR where relevant
Migrations Are Append-Only History
A useful mental model:
V1
V2
V3
V4
V5
...
is like an append-only history of schema evolution।
We don't continually rewrite:
V1
so it looks like the database was always in today's shape։
Production wasn't always in today's shape।
The migration history tells that story։
But Don't Create Hundreds of Migrations Before First Release for No Reason
During very early development, before anything is shared or deployed, teams sometimes consolidate migrations to keep initial history clean।
That's a workflow decision।
Once downstream environments depend on versions, immutability matters far more than cosmetic history cleanliness।
Again:
context matters
Flyway and JPA Startup Contract
Our desired relationship:
Flyway migration
↓
schema current
↓
Hibernate validation
↓
JPA repositories ready
This establishes clear ownership։
If application mapping and database schema disagree:
startup should fail
rather than silently mutate production।
Example application.yml
Conceptually:
spring:
datasource:
url: ${DB_URL}
username: ${DB_USERNAME}
password: ${DB_PASSWORD}
jpa:
hibernate:
ddl-auto: validate
We don't need to configure a custom Flyway location because:
classpath:db/migration
is the standard Spring Boot location.
Do We Need Separate Flyway Credentials?
Spring Boot can use the application's primary DataSource for Flyway by default, while it also supports separate Flyway connection settings when needed.
For our current application:
same datasource
is enough।
A future production platform may deliberately give:
migration user
→ DDL permissions
and:
runtime application user
→ narrower DML permissions
but that operational hardening is not required to understand the initial migration workflow।
Don't Add a Second DataSource Without Need
Keep:
Spring datasource
→ PostgreSQL
→ Flyway + JPA
simple initially।
Infrastructure complexity should follow security/operations requirements rather than speculation।
Migration Review Questions
Before merging a migration, ask:
Does this migration represent a real requirement?
Does it preserve existing data?
Can it run on a database containing production rows?
Could it lock or rewrite a large table?
Does the new application require it before startup?
Can the previous application version tolerate it
during deployment if necessary?
Are constraints explicit?
Is the filename/version unique?
Has an already-applied migration been edited?
Does the JPA mapping match the migrated schema?
Can integration tests build a fresh database
from the full migration history?
Common Mistake 1 — Manual Production Schema Changes
Every environment eventually becomes different।
Common Mistake 2 — Editing an Applied Migration
Checksum/history validation exists specifically to detect this kind of drift.
Common Mistake 3 — Hibernate ddl-auto=update and Flyway Both Own Schema
Choose one migration authority।
For us:
Flyway
owns changes।
Common Mistake 4 — create-drop Used as Integration Strategy
Tests should prove real migrations can build the PostgreSQL schema।
Common Mistake 5 — Random Development Data in Production Migration
Schema history is not a demo-data loader।
Common Mistake 6 — Migration Designed Only for Empty Database
Production migrations must consider existing rows।
Common Mistake 7 — repair Used Automatically
Understand history mismatch before changing Flyway metadata।
Common Mistake 8 — Every Change Added to the Latest Old Migration
Create a new migration instead of rewriting shared history।
Common Mistake 9 — Environment-Specific Core Schemas
Local, test, staging, and production should follow the same core schema evolution।
Common Mistake 10 — Migration Treated as Deployment Plumbing
Database migrations are application code affecting durable business data and deserve review/testing accordingly।
Our Migration Strategy
For the Order Management Backend:
Migration tool
→ Flyway
Migration style
→ versioned SQL
Location
→ src/main/resources/db/migration
Initial history
→ V1 products
→ V2 inventory
→ V3 orders
→ V4 order_items
Schema owner
→ Flyway migrations
Hibernate
→ maps and validates schema
Production change strategy
→ new forward migration
Integration tests
→ run real migrations against PostgreSQL
Project Structure
Our repository now moves toward:
order-management/
├── src/
│ ├── main/
│ │ ├── java/
│ │ │ └── io/liveklass/ordermanagement/
│ │ └── resources/
│ │ ├── application.yml
│ │ └── db/
│ │ └── migration/
│ │ ├── V1__create_products.sql
│ │ ├── V2__create_inventory.sql
│ │ ├── V3__create_orders.sql
│ │ └── V4__create_order_items.sql
│ └── test/
│ └── ...
├── build.gradle
└── ...
This is now enough to recreate the current database schema from an empty PostgreSQL instance։
Engineering Principle
The core principle:
A production database schema should evolve through version-controlled migrations, not through memory, manual SQL, or ORM guesses.
Another:
Once a migration becomes shared history, fix the schema by moving forward with another migration rather than rewriting what production already applied.
And:
Flyway owns schema evolution; Hibernate owns object-relational mapping. Giving each tool one clear responsibility makes database behaviour predictable.
Summary
In this lesson, we learned that:
- Production database schemas evolve and need explicit migration history.
- Flyway manages ordered, versioned database migrations and records migration state/checksums in a schema history table.
- Versioned migrations are applied once per target database in version order.
- Spring Boot conventionally discovers Flyway migrations under
classpath:db/migration. - PostgreSQL Flyway integration uses the appropriate database-specific Flyway module with current Spring Boot integration.
- Our initial migration history is
V1__create_products.sql,V2__create_inventory.sql,V3__create_orders.sql, andV4__create_order_items.sql. - Migration versions describe schema evolution and are independent from API versions and application release versions.
- Migration files should represent coherent database changes rather than arbitrary collections of SQL.
- Applied shared migrations should not normally be edited; corrections should be introduced as new forward migrations.
- Flyway checksums and validation help detect migration-history drift.
- Flyway's schema history makes migration state inspectable across environments.
- Local, CI, staging, and production should follow the same core migration history.
- Integration tests should build PostgreSQL state through the real migrations rather than a separately maintained test schema.
- Migrations must be designed for databases containing existing data, not only empty local databases.
- Database migrations can have deployment and locking implications even when their SQL is syntactically valid.
- Repeatable migrations exist for replaceable database objects and rerun when their checksum changes, but our current schema does not need them.
- Flyway supports Java migrations, but plain SQL is sufficient and clearer for our current PostgreSQL schema.
- Migration failure should be treated as a deployment/startup failure rather than ignored.
- Flyway
repairis an intentional history-repair operation, not a command to run automatically whenever validation fails. - Hibernate should not independently mutate production schema once Flyway owns migrations.
- Our intended Hibernate direction is schema validation rather than automatic schema update.
- Every schema-changing ticket should include the required migration and persistence tests as part of the same engineering change.
- Database migration files deserve the same review discipline as application code because they modify durable business state.
Next lesson:
Indexes and Query Performance
There we will identify the actual queries our API performs, use those access patterns to decide which PostgreSQL indexes are justified, understand composite-index ordering, and learn why “add an index to every column” can make a database slower rather than faster.