无法使用Liquibase填充数据库。

huangapple go评论17阅读模式
英文:

Cannot populate database with liquibase

问题

我正在构建一个小的Spring Boot应用程序。我正在使用Spring Data JPA来创建数据库模式,并使用Liquibase来填充测试数据。

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/book-db
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.username=admin
spring.datasource.password=lTIDDYz3n3jD3BeYaAJz
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

根据文档,如果我有Gradle依赖项并且在默认路径下有主ChangeLog,就不需要为Liquibase进行配置。

db.changelog-master.yaml:

databaseChangeLog:
  - changeSet:
      id: 1
      author: jb
      changes:
      - sqlFile:
          path: db/migration/insert-books.sql

insert-books.sql:

--liquibase formatted sql
--changeset admin:1
delete from book;
insert into book (id, title)
values (nextval('seq'), 'Functional Programming for Mortals');
commit;

我尝试过带有commit和不带commit。成功创建了表databasechangelogdatabasechangelog,并包含迁移(insert-books)。

迁移已成功执行,因为如果我添加一个无效的插入(到不存在的表),我会得到异常:

ERROR: relation "xxx" does not exist

如何使用Liquibase在insert-books.sql脚本中填充数据库数据?

英文:

I'm building a small spring-boot application. I'm using spring-data-jpa to create the database schema and liquibase to populate it with test data.

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/book-db
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.username=admin
spring.datasource.password=lTIDDYz3n3jD3BeYaAJz
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

According to the documentation no configuration for liquibase is required if I have a gradle dependency and master changeLog under the default path.

db.changelog-master.yaml:

databaseChangeLog:
      - changeSet:
            id: 1
            author: jb
            changes:
            - sqlFile:
                  path: db/migration/insert-books.sql

insert-books.sql:

--liquibase formatted sql
--changeset admin:1
delete from book;
insert into book (id, title)
values (nextval('seq'), 'Functional Programming for Mortals');
commit;

I have tried it with and without commit. The tables databasechangelog and databasechangelog are created successfully and contain the migration (insert-books).

The migration goes through, because if I add an invalid insert (to some table that does not exist), I get the exception:

ERROR: relation "xxx" does not exist

How to populate the database with data in insert-books.sql script using liquibase?

答案1

得分: 1

不要同时使用liquibase和JPA来管理数据库结构。如果要使用liquibase,请将JPA(Hibernate)设置为仅验证模式并在liquibase内管理模式。

spring.jpa.hibernate.ddl-auto=validate

您的解决方案存在操作顺序的问题。当应用程序启动时,首先运行liquibase,它插入数据,然后启动JPA,并从头开始创建模式。

尝试在运行应用程序之前删除模式,我敢打赌迁移(liquibase)会失败。

Liquibase必须负责模式,有一种方法可以将liquibase添加到现有的数据库中,但这再次使liquibase成为模式的所有者:

https://stackoverflow.com/questions/16455624/using-liquibase-on-the-existing-database

英文:

Don't use both, liquibase and JPA, to manage the DB structure. If you want to use liquibase, set JPA (Hibernate) to just validate the schema and manage the schema within liquibase.

spring.jpa.hibernate.ddl-auto=validate

The problem with your solution is in the order of operations. When your application starts, it first runs liquibase, which inserts the data, then JPA is started and the schema is created from scratch.

Try dropping the schema before running the app, I bet the migration (liquibase) will fail.

Liquibase has to be in charge of the schema, there is a way to add liquibase to an existing database, but it again makes liquibase the owner of the schema:

https://stackoverflow.com/questions/16455624/using-liquibase-on-the-existing-database

huangapple
  • 本文由 发表于 2020年7月30日 17:31:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/63170232.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定