Step 3 — SQL as SSOT

JPA's ddl-auto=update is convenient but dangerous in production. We use SQL files as the single source of truth.

File pattern

sql/
├── 001_create_users.sql
├── 002_create_posts.sql
└── 003_create_comments.sql

Each file is idempotent with CREATE TABLE IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_created_at ON users (created_at DESC);

Don't let Spring auto-migrate

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # check, don't change

Run SQL manually with psql -f sql/001_create_users.sql or via an admin UI button.

Entities map an existing schema

@Entity
@Table(name = "users")
public class UserEntity {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(name = "password_hash", nullable = false)
    private String passwordHash;

    private Instant createdAt;
    private Instant updatedAt;
}

The SQL decides; the entity just reads.

Try it

Create sql/002_create_posts.sql with a posts table (id, user_id FK, title, body, created_at), then map PostEntity and add JpaRepository<PostEntity, Long>.

Going deeper

• SQL = SSOT
• PostgreSQL first contact

Next

Step 4 designs a REST API that exposes this data.