Docker creates a database different from what i specify

Question

I've been struggling with this issue for a good while now and I couldn't find any solution for this so I need your help. I'm using postgres image within Docker container and Java Spring. So basically my database in the docker container. So I created this V1 migration with flyway and wrote this SQL code to create a table

CREATE TABLE customer(
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    age INT NOT NULL
)

And I thought it's supposed to create this exact table in my docker container. So I get inside of docker container and i type \d customer i see a different database like this

Have a look. The table is different from what i created

As you can see it has "varchar" instead of TEXT, and it also has constraints that i didn't specify when i was creating the table.

I thought it might be because of the entity i have (the code snippet below). But i strongly doubt it's because of that

@Entity
@Data
public class Customer {

    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "customer_id_sequence"
    )
    @SequenceGenerator(
            name = "customer_id_sequence",
            sequenceName = "customer_id_sequence")
    private Integer id;

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

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

    @Column(nullable = false, name = "age")
    private Integer age;

if i for example refresh flyway history and create the same table with another column "country"

CREATE TABLE customer(
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    age INT NOT NULL,
    country TEXT NOT NULL 
)

and when i save it i get the same issue. have a look, it doesn't have the "country" column.

Still the same different table

Here's my docker compose:


services:
  db:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: smite
      POSTGRES_PASSWORD: 110011
      PGDATA: /data/postgres
    volumes:
      - db:/data/postgres
    ports:
      - "5332:5432"
    networks:
      - db
    restart: unless-stopped

networks:
  db:
    driver: bridge

volumes:
  db:

1) Per [No images](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors/285557#285557) do not use images for textual information, Copy and paste the `\d ` information as text update to your question. 2) Look at the Postgres log to see what is actually hitting the database. — Adrian Klaver, Jun 03 '23 at 15:23
`varchar(255)` is a default column definition which `Hibernate` uses by default for `String` fields, so, it is clear that`Hibernate` somehow modifies DB schema upon start of application. You need to pay attention to [hibernate.hbm2ddl.auto](https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl) setting - I believe you need to set it to `none` because schema is managed by `Flyway` — Andrey B. Panfilov, Jun 04 '23 at 02:57
It's seem you setup springboot to generate the database schema. So, it may overide the flyway behavior — binhgreat, Jun 05 '23 at 05:05

David Atkinson · Answer 1 · 2023-06-03T22:15:45.523

0

Text and varchar are equivalent in this context.

You did add a constraint when specifying the ID column as a primary key. Databases don’t store the exact SQL you use to create tables so when you script it out later it might look different although it will be semantically identical.

Edit: Thanks to @adrian Klaver for pointing out that the actual order of the columns differ from the CREATE TABLE SQL that was passed to Flyway. My current suspicion is that Flyway is executing the scripts successfully (otherwise the OP would have noticed an error), but the tables are being subsequently recreated/altered by a separate process, possibly an ORM, which is resulting in the different column ordering and the "missing" country column.

edited Jun 03 '23 at 22:15

answered Jun 03 '23 at 08:14

David Atkinson

5,759
2
28
35

the thing is that i tried adding another column "country", but the database turned out to be the same as it was without it. Like it's purposely keeping this database with these specific columns somewhere.. i just need to know how to delete this old db – Vee Romanoff Jun 03 '23 at 08:27
Have you tried Flyway clean? This removes all objects from your database (so use with care!) – David Atkinson Jun 03 '23 at 12:30
`Text` and `varchar` may be equivalent but Postgres will not change an explicitly specified `text` field to `character varying` in the table definition. Nor will it reorder the columns as shown by the OP. That is being done by something else, most probably Flyway. Correct your answer. – Adrian Klaver Jun 03 '23 at 15:20
I doubt it will be Flyway as this just applies SQL "as is". I'm starting to doubt whether Flyway is applying the change to the database at all in this context. It would be useful to see the flyway schema history table, which will tell us what scripts Flyway has run. Or maybe Flyway is making a change but an ORM is also modifying the same objects? @adran - I hadn't spotted the column order. Curiouser and curiouser. – David Atkinson Jun 03 '23 at 21:31

Docker creates a database different from what i specify

1 Answers1