Bug: migration status for duckdb file is stored in postgres

[mindreframer]

First: great extension! I love having self-contained features, that can be dropped into a phoenix app with little configuration. Also, the way you integrated DuckDB is very good and it really wont cause any performance issues for small/medium apps.

Also, it goes into direction Feature -> own DB, which I think is an intriguing architecture pattern.

Now some concerns from my side:

I have tried to reset the analytics data by just deleting the DB, hoping it would be recreated automatically.
This was not the case, I had to re-run the migration (after deleting the according schema_migrations row) again.

Maybe it makes sense to let the migration information be kept in the DuckDB file, and run it automatically? That way the setup becomes simpler (not need to copy migrations to your phx app) and it becomes more robust overall.

Thoughts?

Best,
Roman

[lalabuy948]

Hi @mindreframer ,

Thank you so much for kind words and considering to use my library.

When I was doing a lot of testing I had to do a lot what you described in this issue.
That's why I mentioned in README this:

Tip

iex -S mix phx.server PhoenixAnalytics.Migration.up()

For current CREATE TABLE IF NOT EXISTS migration, you suggestion would work perfectly, I could just inject it to Repo.start_link and done.

But what if in the future we decide to extend/change duckdb schema and we would need to provide upgrade migration.
One way would be to create migrator functionality inside DuckDB, but I'm not sure yet if that's way to go.

The main reason I decided to store migrations in Postgres/Sqlite/Mysql/etc to keep DuckDB schema state and application state in one place.

I'm happy to compare pros/cons and based on it proceed with solution.