<https://github.com/cashapp/sqldelight/discussions/2470#discussioncomment-1003652>

# squarelibraries

[jw]

https://github.com/cashapp/sqldelight/discussions/2470#discussioncomment-1003652

[Jorge Castillo]

Is this answering my question? Not following 🤔

[mbonnin]

For Jdbc + Sqlite, you'll have to call Database.Schema.create but jdbc doesn't "record" what version of the schema is currently used so if you run it twice, you'll get an error the second time

[mbonnin]

Most likely the same issue for postgre Jdbc + postgre

[Jorge Castillo]

I'm using it but seems to make no difference

[Jorge Castillo]

@Bean
  fun provideDataSource(): DataSource =
    DataSourceBuilder.create()
      .apply {
        driverClassName(config.database.driver)
        url(config.database.url)
        username(config.database.username)
        password(config.database.password)
      }.build()

  @Bean
  fun provideJdbcDriver(dataSource: DataSource): JdbcDriver =
    dataSource.asJdbcDriver()

  @Bean
  fun provideSubscriptionsDb(
    jdbcDriver: JdbcDriver,
    dateTimeAdapter: ColumnAdapter<LocalDateTime, String>
  ): SubscriptionsDb =
    SubscriptionsDb(
      jdbcDriver.apply { SubscriptionsDb.Schema.create(this) },
      SubscriptionEntity.Adapter(dateTimeAdapter)
    )

[mbonnin]

Ah, that's interesting...

[mbonnin]

Maybe postgre is different then 🤷

[mbonnin]

No matter what, in all cases you don't want to run "create" everytime, whether it creates table or not

[mbonnin]

I'd expect it to create tables though 🤔

[Jorge Castillo]

Yeah that makes sense. I think it'd make more sense to setup flyway or similar

[Jorge Castillo]

Still it is a CREATE TABLE IF NOT EXISTS

[Jorge Castillo]

tho

[mbonnin]

True

[Jorge Castillo]

I've just set up flyway like this and the corresponding V1__base.sql file but still getting the same error:

@Bean
  fun provideFlyway(dataSource: DataSource): Flyway =
    Flyway.configure().dataSource(dataSource).baselineOnMigrate(true).load()

  @Bean
  fun provideJdbcDriver(dataSource: DataSource, flyway: Flyway): JdbcDriver =
    dataSource.asJdbcDriver().apply {
      flyway.migrate()
    }

[Jorge Castillo]

I'm starting to wonder whether the order of .sq files might affect, since queries to create tables are automatically added to the Schema following the order of the files.

E.g: Keeping in mind files are sorted in alphabetical order in the file system, if you have the following sq files:

• RepositoryEntity.sq
• SubscriptionEntity.sq
• UserEntity.sq
And SubscriptionEntity.sq references a table created in UserEntity.sq like the userEntity table in my case, when the query to create the subscriptionEntity table is called, the userEntity table is still not created. Is order something that could affect declarations to break in any way?

[Jorge Castillo]

Order of things generated in the Schema:

public override fun create(driver: SqlDriver): Unit {
      driver.execute(null, """
          |CREATE TABLE IF NOT EXISTS repositoryEntity (
          |    repository_id SERIAL PRIMARY KEY,
          |    owner VARCHAR NOT NULL,
          |    repository VARCHAR NOT NULL
          |)
          """.trimMargin(), 0)
      driver.execute(null, """
          |CREATE TABLE IF NOT EXISTS subscriptionEntity (
          |    subscription_id SERIAL PRIMARY KEY,
          |    subscribed_at DATE NOT NULL, -- TIMESTAMPTZ is not supported by sqldelight
          |    user_id SERIAL NOT NULL,
          |    repository_id SERIAL NOT NULL,
          |    FOREIGN KEY (user_id) REFERENCES userEntity(user_id) ON DELETE CASCADE,
          |    FOREIGN KEY (repository_id) REFERENCES repositoryEntity(repository_id) ON DELETE CASCADE
          |)
          """.trimMargin(), 0)
      driver.execute(null, """
          |CREATE TABLE IF NOT EXISTS userEntity (
          |    user_id SERIAL PRIMARY KEY,
          |    slack_user_id VARCHAR NOT NULL,
          |    slack_channel_id VARCHAR
          |)
          """.trimMargin(), 0)
    }

[mbonnin]

Can you put a breakpoing in there? If it hits, maybe there something else after that that resets the DB?