CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE TABLE user_profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
first_name VARCHAR(100),
last_name VARCHAR(100),
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);
spring:
flyway:
enabled: true
locations: classpath:db/migration
baseline-on-migrate: true
baseline-version: 0
validate-on-migrate: true
clean-disabled: true
schemas: public
table: flyway_schema_history
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
content TEXT,
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
published_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at);
CREATE OR REPLACE VIEW user_statistics AS
SELECT
u.id,
u.username,
COUNT(p.id) as post_count,
MAX(p.published_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.status = 'PUBLISHED'
GROUP BY u.id, u.username;
package db.migration;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.PreparedStatement;
public class V4__Data_Migration extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
String sql = "UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL";
try (PreparedStatement statement = context.getConnection().prepareStatement(sql)) {
statement.execute();
}
}
}
Flyway manages database schema evolution through versioned SQL scripts. Migration files follow naming convention—V1__initial_schema.sql, V2__add_users_table.sql. Flyway tracks applied migrations in a schema history table. Migrations run automatically on application startup or via Maven/Gradle. Repeatable migrations use R__ prefix for views, procedures. Baseline existing databases with flyway.baseline-on-migrate. Validation ensures applied migrations match filesystem. Flyway supports SQL and Java-based migrations. The tool enables version control for database changes, making deployments repeatable and auditable. Multiple environments use same migrations with different data. Flyway integrates seamlessly with Spring Boot via auto-configuration. Proper migration strategy is crucial for production database management.
