Database Schema in the Repository

1. Database schema as part of the product

If you are thinking right now, “Well, the schema already exists, Hibernate created it, everything works — why touch it?”, you are thinking perfectly naturally. At the start of a project, that is exactly how it should be: you want to see as quickly as possible how save() turns into INSERT, not write kilometers of DDL. But here is what changes by day 23.

While the project was small, the schema really could be something “in the background”: one or two tables, a couple of columns added — and that was fine. Now the schema is no longer background noise. We have several subsystems (catalog, inventory, ordering), relationships, indexes for reads, and most importantly — data expectations. Once a project reaches this stage, any “accidental” database change starts living its own life, and fairly quickly you discover that you do not have one project, but two: Java code in Git and some mysterious “real database on your machine”.

This is the moment when the database schema stops being “a side effect of starting the application” and becomes part of the product on par with the code. Roughly like application.yml: nobody in their right mind keeps the config only in their head and passes it along verbally with the phrase “just set the same URL there”. The same is true for the schema — it just pretended for a long time that it was “not your problem”.

Schema in a local database: risks

A schema “lives in a local database” when the main source of truth is the current state of a specific PostgreSQL instance on your laptop. Not in the repository, not as a set of files, not as reproducible change history, but as something that emerged historically. Today it is like this, tomorrow you “slightly adjust a column”, the day after tomorrow you add an index, and then you forget what you did.

The most unpleasant thing about this approach is not even that you will forget. The most unpleasant thing is that you get a “snowflake” effect: the database becomes unique, beautiful, one of a kind... and useless for the team. You cannot guarantee that a clean database on another machine will end up in the same state. You cannot quickly bring up the environment in CI. You cannot switch branches cleanly, because in one branch “the column already exists”, and in another “it does not yet, but the database remembers”. And yes, sooner or later this turns into the classic engineering meme: “it works on my machine”.

The irony is that the problem almost never shows up immediately. It shows up at the moment when you are already tired, just want to commit the task and go drink tea. That is exactly when the database decides to remind you that it is also a “project participant” and has its own opinion.

2. Manual changes through the SQL console: fast, but no history

A manual ALTER TABLE is like tightening a screw with a screwdriver when a door is creaking. In the moment it feels nice: the squeak disappears, you win. But if you do not write down which screw you tightened and by how much, tomorrow the door will start squeaking again — for somebody else.

For example, you decided to add a note to a product. Makes sense: the field is small, you want to store something like “the supplier asked not to show it on the storefront until Friday” (yes, that kind of thing happens in real life too, not only in textbooks). You open the SQL console and do this:

-- Add a new column to the table: this change "lives" only in your database,
-- until you formalize it as a migration in the repository.
alter table product add column note varchar(255);

And that is it. On your database the column exists, you add the field in code, restart — and the happy developer moves on.

Now for the plot twist: your colleague (or you tomorrow, but after docker compose down -v) runs the project on a clean database. And discovers that the project is “basically the same”, but the note column is missing. The application either fails to start, or the first read/write request fails. And suddenly you turn from “the person who added a useful feature” into “the person who left a survival quest behind”.

From Git’s point of view everything is perfect: the code is committed, the build is green, the tests (if any) passed. But the database is not Git. It does not know what you did. Its memory is state. And state without history is hard to move between people and machines.

3. ddl-auto=update: a learning mode with risks

Now we are getting to the topic where it is very easy to say “Hibernate is bad” — and very easy to be wrong. Hibernate is not bad. Hibernate simply does exactly what you asked it to do. The problem is what exactly you asked it to do.

At an early stage you might have used something like:

spring:
  jpa:
    hibernate:
      # Learning mode: Hibernate tries to "catch up" the schema to the entity at startup.
      # Convenient for demos, but dangerous as a permanent strategy in a growing project.
      ddl-auto: update

In learning mode this gives a quick result: you change the entity — and on application startup Hibernate tries to bring the database schema into alignment. Convenient? Yes. Fast? Yes. Does it remove the fear of “I do not understand DDL at all”? Also yes.

But as soon as the project stops being “just a demo”, this mode starts showing unpleasant properties.

First, changes happen during application startup. That means you start the service — and at that moment it may change the database. That no longer looks like a controlled process; it looks more like “it decided and did it on its own”. In a small educational database that seems harmless, but in a real one it is a direct invitation to chaos.

Second, ddl-auto=update does not give you a readable history. You cannot open the repository and see: “Ah, that is when the note column was added and why.” You do not have a file you can review, discuss, roll back (although rolling back migrations is a separate story), or apply to a clean database in CI.

Third, such changes are hard to synchronize between branches. Imagine you switched to another branch where the entity still does not contain note. Hibernate is not required to “remove the column” back again (and sometimes it is good that it is not required to). As a result, the database starts containing traces of several development branches at once, and you get an “archaeological layer” of columns, indexes, and constraints whose origin and usefulness are unclear.

The most dangerous part is that ddl-auto=update looks like “the migration problem has been solved”. As if, why need Flyway if Hibernate updates everything itself? The answer is simple and a little sad: because updating a schema is not just “add a column”, but also manage changes as a process, not as a side effect of starting the application.

4. Code and database drift

Let us make the situation as concrete as possible. You added the column manually, and in code you also added the field:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;

@Entity
class Product {

    @Column(name = "note") // The mapping expects the product table to contain a note column
    private String note;   // If the column was "forgotten" in a migration, there will be drift

    // omit the other fields
}

On your database this works, because you already ran ALTER TABLE.

But if tomorrow someone brings up a clean database (or you delete the volume yourself), then Hibernate with ddl-auto=none or ddl-auto=validate will say: “The column does not exist.” And honestly, that is the best possible outcome, because failing at startup is a soft warning. Much worse is when the application starts, but the error appears in the middle of a business operation.

Usually, drift shows up in three ways.

First — the application does not start, because schema validation detected a mismatch between the mapping and the tables. This is the most honest scenario: it broke early, and we quickly understood what was wrong.

Second — the application starts, but on the first INSERT or SELECT it starts failing because the SQL generated by Hibernate refers to a column that does not exist. At that point you get something like “column note does not exist”. That error is harder to understand, especially for a beginner: it feels like “I wrote Java code, what does a column have to do with it?”

Third — even worse: the application sort of works, but your different environments have different schemas, and bugs appear only “on Petya’s dev box”, “in CI”, “on another machine” or “on the test environment”. That is the level of pain where people start hating databases as a class... although the database is not really to blame.

The important idea is this: JPA mapping is an expectation about the database structure, not the structure itself. A mapping is a map, and the database is the territory. You can draw a map with a bridge over a river, but if the bridge has not been built in the real world yet, you will still get wet.

5. Schema in the repository: a single source of truth

When we say “the schema should live in the repository”, we do not mean “the repository should contain a printout of SQL on 300 pages”. We mean a simple engineering discipline: any schema change must be expressed in a file stored in Git and applied in a predictable way.

That immediately changes the project’s life.

First, a new developer (or you on a new laptop) gets the project, runs the standard set of commands, and the database comes up in the required state automatically. Not because “everyone has it configured that way”, but because the repository contains the history of changes. And yes, this is one of the rare cases where the phrase “automatically” means “according to clear rules that live next to the code”.

Second, schema changes become visible. They can be discussed, reviewed, argued about, improved. In other words, the schema stops being magic and returns to a normal engineering process.

Third, reproducibility appears. And this is not some abstract word from books, but a concrete ability: “delete the database and bring it up again without panic.” If you can do that — the project is alive. If you cannot — the project is held together by luck and the memory of a specific person.

To reinforce the idea, let us compare three approaches not by “who is cooler”, but by engineering properties:

Notice: we are not discussing a specific tool yet. We are discussing the principle: a single source of truth and reproducible history. The tool (Flyway) will appear next as the mechanism that knows how to apply this history and record the fact that it was applied.

How this is connected to our shop-data-jpa right now

Our project is already complex enough that “schema as a side effect” has become dangerous. We have catalog tables, order tables, inventory tables, and relationships between them. We have already written queries, hit N+1, optimized reads. That means we have started thinking about data seriously. And that means the database schema is no longer temporary.

Imagine a simple goal: a student or a new developer clones the repository, starts PostgreSQL via docker compose up, launches the application — and gets a working project. Not “working after they manually create the tables”, and not “working if they first import someone else’s dump”. Working simply because the repository contains everything needed.

If the schema lives only in the local database, you cannot guarantee this goal. More than that, you cannot even guarantee it for yourself: it is enough to delete the volume once, switch branches, or move to another machine — and you will again be “restoring civilization from the ruins” with the phrase “I think there was a table called product here...”.

And one more important point that seems minor until it becomes painful. Soon we will strengthen the project at the schema level: add indexes, introduce constraints, add audit fields. These are all inevitable changes. And if you do not move the schema into “lives in the repository” mode, every such change will turn into a mini-detective story: “Did we already add this? Who has it in their database? Why does it work for me but not for you?”

Readiness check: kill the database and bring it up again

There is a very simple thought experiment that helps you understand whether it is time for migrations. It sounds harsh, but it is honest: imagine that your database disappears. For example, you accidentally run:

# Removes containers and the volume with data/schema: after this the DB will be "clean".
docker compose down -v

And that is it, the volume with the data and schema has flown off to digital heaven.

If after that you can say “okay, I will bring it up again, the schema will be restored automatically because it is described in the project” — you are on the right path. If you start remembering: “so, I created a table by hand somewhere, then added a column, then an index...” — then the source of truth is in your head and in a specific database, and that is a bad place to store truth.

In an educational project this experiment is especially useful because it quickly shows what exactly you are building. Are you building a backend project with a managed schema, or are you building a set of Java classes that “kind of work on your laptop”.

And yes, it is normal that at an early stage you have the second option. Our job now is to move carefully to the first.

6. Typical mistakes with a schema in a local database

Mistake #1: assuming that “if the application starts, everything is fine”.
Application startup on your machine says only one thing: on your machine, your database matched your code at that exact moment. It does not guarantee anything for a clean database, another machine, another branch, or CI. For it to be “fine”, the schema must be reproducibly brought up from the repository into the same state.

Mistake #2: leaving ddl-auto=update as a permanent mode “because it is convenient”.
The convenience of update is like the habit of keeping important documents “on the desktop” — at first it seems faster that way. Then the desktop turns into an archaeological museum, and the document you need right now somehow cannot be found. ddl-auto can be a bridge at the beginning, but it should not be the foundation of the project.

Mistake #3: making a manual ALTER TABLE, then “forgetting” to turn it into part of the project.
Manual SQL is not a crime by itself. The problem starts when it remains only in the history of your local database. If you made a manual change, it should receive “official status” in the repository, otherwise you just added an invisible dependency: for the project to work, someone has to repeat your manual step.

Mistake #4: keeping two sources of truth at the same time and hoping they will “sort it out somehow”.
When part of the schema lives in migrations (or at least in deliberate DDL scripts), and part lives in ddl-auto=update and manual changes, the project turns into a game of “guess who last touched the database”. The schema should have one primary source of truth, otherwise you will constantly catch drift and strange errors out of nowhere.

Mistake #5: ignoring the fact that the schema is code too, just SQL.
Sometimes it seems that the real code is Java, and SQL is just “infrastructure stuff”. But in a data-layer project, the SQL schema is part of the system contract. If you treat it as something secondary, the system will definitely take advantage of that and start breaking in the most unpleasant places.