Schema diff issues when using TimescaleDB

alex-oleshkevich commented 1 year ago

Hi. I use TimescaleDB and I have an issue when I run atlas migrate diff.

Error: sql/migrate: taking database snapshot: sql/migrate: connected database is not clean: found schema "_timescaledb_cache"

Is there any option to ignore this error?

rotemtam commented 1 year ago

A few options:

Use a schema bound connection (search_path) in the connection string
Use the --exclude flag

crossworth commented 1 year ago

Hello @alex-oleshkevich, currently there is no support for TimescaleDB, there is a open issue about it #1347 and plans to add support for extensions in the near future.

It's possible to do a few operation but I do not recommend it, I will only list it here so we can keep track of the current state of support.

atlas migrate diff can't be used used to compare the state correctly
atlas migrate inspect kinda works, but won't return any information about the TimescaleDB partitions or settings.
atlas migrate apply kinda works for simple DDL changes.

Ignoring the error

Generally speaking, is possible to ignore the not clean error with extensions that create other schemas using the search_path or the exclude flag. TimescaleDB creates a few schemas.

Dev database

A few operations requires a dev database, we have to use a dev database that already has the extension (ref https://github.com/ariga/atlas/issues/1976#issuecomment-1675030008).

`atlas migrate diff`

Creating a diff does not work as expected, since Atlas can not detect the TimescaleDB Hypertable, it generates information about the table as a normal one.

Now, generating a diff using the search_path on dev database is not possible:

So we have to use the --exclude flag and provide the TimescaleDB schemas.

`atlas migrate inspect`

Inspect kinda works, is does generate the state, but it wont return anything related to TimescaleDB (like the hypertable params).

`atlas migrate apply`

Apply works for simple DDL changes, like adding a new column.

ariga / atlas