flyway-migrations

Name: flyway-migrations
Author: rrezartprebreza

174stars33forks

How to install flyway-migrations?

rrezartprebreza/spring-boot-skills/flyway-migrations

$npx -y skills add rrezartprebreza/spring-boot-skills --skill flyway-migrations

Installs into the current project.

Summary

Use when creating database migrations, schema changes, seed data, or any SQL that modifies database structure. Covers Flyway naming conventions, versioning, and safe migration patterns.

Files · 1

View on GitHub

SKILL.md

1	# Flyway Migrations
2
3	## File Naming Convention
4
5	```
6	src/main/resources/db/migration/
7
8	V{version}__{description}.sql ← versioned (run once)
9	R__{description}.sql ← repeatable (run when checksum changes)
10	U{version}__{description}.sql ← undo (requires Flyway Teams)
11
12	Examples:
13	V1__create_users_table.sql
14	V2__create_orders_table.sql
15	V2.1__add_order_status_index.sql
16	V3__add_customer_email_to_orders.sql
17	R__create_reporting_views.sql
18	```
19
20	Rules:
21	- Double underscore `__` between version and description
22	- Underscore `_` for spaces in description
23	- Sequential versions — never go back and fill gaps
24	- Never modify a migration that has already run in any environment
25
26	## Example Migrations
27
28	```sql
29	-- V1__create_users_table.sql
30	CREATE TABLE users (
31	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
32	email VARCHAR(255) NOT NULL UNIQUE,
33	password VARCHAR(255) NOT NULL,
34	role VARCHAR(50) NOT NULL DEFAULT 'USER',
35	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
36	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
37	);
38
39	CREATE INDEX idx_users_email ON users(email);
40
41	-- V2__create_orders_table.sql
42	CREATE TABLE orders (
43	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
44	user_id UUID NOT NULL REFERENCES users(id),
45	status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
46	total_amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
47	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
48	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
49	);
50
51	CREATE INDEX idx_orders_user_id ON orders(user_id);
52	CREATE INDEX idx_orders_status ON orders(status);
53	CREATE INDEX idx_orders_created ON orders(created_at DESC);
54
55	-- V3__add_shipping_address_to_orders.sql
56	-- Adding a column — always nullable or with default (safe for existing rows)
57	ALTER TABLE orders
58	ADD COLUMN shipping_address TEXT,
59	ADD COLUMN shipped_at TIMESTAMPTZ;
60	```
61
62	## Safe Migration Patterns
63
64	```sql
65	-- ✅ Safe: add nullable column
66	ALTER TABLE orders ADD COLUMN notes TEXT;
67
68	-- ✅ Safe: add column with default
69	ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;
70
71	-- ✅ Safe: add index CONCURRENTLY (no table lock in Postgres)
72	-- ⚠️ BUT: CONCURRENTLY cannot run inside a transaction, and Flyway wraps every
73	-- migration in one by default → the migration FAILS. Opt that one script out
74	-- with a sidecar config file:
75	-- V4__add_orders_email_index.sql.conf → executeInTransaction=false
76	-- Keep the CONCURRENTLY statement alone in its own migration file.
77	CREATE INDEX CONCURRENTLY idx_orders_email ON orders(customer_email);
78
79	-- ✅ Safe: rename via add + backfill + drop (multi-step)
80	-- Step 1 (V5): add new column
81	ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
82	-- Step 2 (V5): backfill
83	UPDATE orders SET customer_email = (SELECT email FROM users WHERE users.id = orders.user_id);
84	-- Step 3 (V5): add constraint after data is there
85	ALTER TABLE orders ALTER COLUMN customer_email SET NOT NULL;
86	-- Step 4 (later V6, after code is deployed): drop old column
87	ALTER TABLE orders DROP COLUMN user_id;
88
89	-- ❌ Dangerous: rename column directly (breaks running app)
90	ALTER TABLE orders RENAME COLUMN user_id TO customer_id;
91
92	-- ❌ Dangerous: NOT NULL without default on large table (locks table)
93	ALTER TABLE orders ADD COLUMN priority INT NOT NULL; -- will fail on existing rows
94	```
95
96	## application.yml
97
98	```yaml
99	spring:
100	flyway:
101	enabled: true
102	locations: classpath:db/migration
103	baseline-on-migrate: true # for existing databases
104	validate-on-migrate: true
105	out-of-order: false # enforce sequential execution
106	```
107
108	## Seed Data (test/dev only)
109
110	```java
111	// Use Spring profiles, not Flyway, for seed data
112	@Component
113	@Profile("dev")
114	@RequiredArgsConstructor
115	public class DevDataSeeder implements ApplicationRunner {
116	private final UserRepository userRepository;
117
118	@Override
119	public void run(ApplicationArguments args) {
120	if (userRepository.count() == 0) {
121	userRepository.save(User.createAdmin("admin@dev.local", "password123"));
122	}
123	}
124	}
125	```
126
127	## Team Workflow: Concurrent Migrations
128	- Multiple developers creating migrations simultaneously will cause version conflicts
129	- Solution: use a shared tracker (Slack channel, wiki page) or timestamp-based versions (`V20260414_1__`)
130	- If two migrations target the same version, one developer must bump theirs
131	- Run `flyway info` before committing to check for version gaps or duplicates
132	- In CI/CD: run `flyway validate` as a pre-deploy step to catch conflicts early
133	- Never set `out-of-order: true` in production — it masks migration ordering bugs
134
135	## Gotchas
136	- Agent names files `V1_create_users.sql` (single underscore) — must be double `__`
137	- Agent modifies existing migration files — never edit a

People who liked this also viewed

6 picks

Type

flyway-migrations

174stars33forks

View on GitHub

How to install flyway-migrations?

rrezartprebreza/spring-boot-skills/flyway-migrations

$npx -y skills add rrezartprebreza/spring-boot-skills --skill flyway-migrations

Installs into the current project.

Summary

Use when creating database migrations, schema changes, seed data, or any SQL that modifies database structure. Covers Flyway naming conventions, versioning, and safe migration patterns.

Files · 1

View on GitHub

SKILL.md

1	# Flyway Migrations
2
3	## File Naming Convention
4
5	```
6	src/main/resources/db/migration/
7
8	V{version}__{description}.sql ← versioned (run once)
9	R__{description}.sql ← repeatable (run when checksum changes)
10	U{version}__{description}.sql ← undo (requires Flyway Teams)
11
12	Examples:
13	V1__create_users_table.sql
14	V2__create_orders_table.sql
15	V2.1__add_order_status_index.sql
16	V3__add_customer_email_to_orders.sql
17	R__create_reporting_views.sql
18	```
19
20	Rules:
21	- Double underscore `__` between version and description
22	- Underscore `_` for spaces in description
23	- Sequential versions — never go back and fill gaps
24	- Never modify a migration that has already run in any environment
25
26	## Example Migrations
27
28	```sql
29	-- V1__create_users_table.sql
30	CREATE TABLE users (
31	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
32	email VARCHAR(255) NOT NULL UNIQUE,
33	password VARCHAR(255) NOT NULL,
34	role VARCHAR(50) NOT NULL DEFAULT 'USER',
35	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
36	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
37	);
38
39	CREATE INDEX idx_users_email ON users(email);
40
41	-- V2__create_orders_table.sql
42	CREATE TABLE orders (
43	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
44	user_id UUID NOT NULL REFERENCES users(id),
45	status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
46	total_amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
47	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
48	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
49	);
50
51	CREATE INDEX idx_orders_user_id ON orders(user_id);
52	CREATE INDEX idx_orders_status ON orders(status);
53	CREATE INDEX idx_orders_created ON orders(created_at DESC);
54
55	-- V3__add_shipping_address_to_orders.sql
56	-- Adding a column — always nullable or with default (safe for existing rows)
57	ALTER TABLE orders
58	ADD COLUMN shipping_address TEXT,
59	ADD COLUMN shipped_at TIMESTAMPTZ;
60	```
61
62	## Safe Migration Patterns
63
64	```sql
65	-- ✅ Safe: add nullable column
66	ALTER TABLE orders ADD COLUMN notes TEXT;
67
68	-- ✅ Safe: add column with default
69	ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;
70
71	-- ✅ Safe: add index CONCURRENTLY (no table lock in Postgres)
72	-- ⚠️ BUT: CONCURRENTLY cannot run inside a transaction, and Flyway wraps every
73	-- migration in one by default → the migration FAILS. Opt that one script out
74	-- with a sidecar config file:
75	-- V4__add_orders_email_index.sql.conf → executeInTransaction=false
76	-- Keep the CONCURRENTLY statement alone in its own migration file.
77	CREATE INDEX CONCURRENTLY idx_orders_email ON orders(customer_email);
78
79	-- ✅ Safe: rename via add + backfill + drop (multi-step)
80	-- Step 1 (V5): add new column
81	ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
82	-- Step 2 (V5): backfill
83	UPDATE orders SET customer_email = (SELECT email FROM users WHERE users.id = orders.user_id);
84	-- Step 3 (V5): add constraint after data is there
85	ALTER TABLE orders ALTER COLUMN customer_email SET NOT NULL;
86	-- Step 4 (later V6, after code is deployed): drop old column
87	ALTER TABLE orders DROP COLUMN user_id;
88
89	-- ❌ Dangerous: rename column directly (breaks running app)
90	ALTER TABLE orders RENAME COLUMN user_id TO customer_id;
91
92	-- ❌ Dangerous: NOT NULL without default on large table (locks table)
93	ALTER TABLE orders ADD COLUMN priority INT NOT NULL; -- will fail on existing rows
94	```
95
96	## application.yml
97
98	```yaml
99	spring:
100	flyway:
101	enabled: true
102	locations: classpath:db/migration
103	baseline-on-migrate: true # for existing databases
104	validate-on-migrate: true
105	out-of-order: false # enforce sequential execution
106	```
107
108	## Seed Data (test/dev only)
109
110	```java
111	// Use Spring profiles, not Flyway, for seed data
112	@Component
113	@Profile("dev")
114	@RequiredArgsConstructor
115	public class DevDataSeeder implements ApplicationRunner {
116	private final UserRepository userRepository;
117
118	@Override
119	public void run(ApplicationArguments args) {
120	if (userRepository.count() == 0) {
121	userRepository.save(User.createAdmin("admin@dev.local", "password123"));
122	}
123	}
124	}
125	```
126
127	## Team Workflow: Concurrent Migrations
128	- Multiple developers creating migrations simultaneously will cause version conflicts
129	- Solution: use a shared tracker (Slack channel, wiki page) or timestamp-based versions (`V20260414_1__`)
130	- If two migrations target the same version, one developer must bump theirs
131	- Run `flyway info` before committing to check for version gaps or duplicates
132	- In CI/CD: run `flyway validate` as a pre-deploy step to catch conflicts early
133	- Never set `out-of-order: true` in production — it masks migration ordering bugs
134
135	## Gotchas
136	- Agent names files `V1_create_users.sql` (single underscore) — must be double `__`
137	- Agent modifies existing migration files — never edit a

People who liked this also viewed

6 picks

Type