My custom views are deleted every restart

[johnnuy]

DBSync 12.0.0 on Postgres 14.1 with Ubuntu 20.04.

I have added some custom views into the cexplorer schema for my application, but these views are removed when I restart the dbsync after patching the OS. I tried adding my views to the schema files, but this causes dbsync to not start up.

How can I integrate my own custom views such that they get recreated when dbsync is restarted?

[erikd]

There is currently no way to do this.

[johnnuy]

How do I put in a feature request for this?

[erikd]

I do not consider this something that should be in db-sync. In my opinion you should figure out a way to manage this outside of db-sync.

Recently db-sync added some extra checksumming an validation of the schema files (that the schema files available at run time are identical to the ones that were present at compile time). This was necessary to detect a whole host of problems with people updating db-sync and not updating the schema files. However, this checksumming and validation means it is not possible for users to add extra schema files or manipulate the schema in any way.

[johnnuy]

I disagree with your opinion. Software should be extendable.

[rdlrt]

You can add a seperate schema to extend with secondary views as desired, it will not be touched :-)

Koios does something similar for views and RPCs, also helps if you're collaborating together instead of individual parallel streams

[johnnuy]

I just modified the migration scripts at the source and recompiled, now the migration scripts include my views and the hashes will align so it works.

[erikd]

Modifying the migration scrips and recompiling is more difficult than it should be.

Currently, migrations are applied in an order specified by the alphapbet sort of the schema files. Currently the schema directory looks like:

> ls -1 schema/
migration-1-0000-20190730.sql
migration-1-0001-20190730.sql
migration-1-0002-20190912.sql
migration-1-0003-20200211.sql
migration-1-0004-20201026.sql
migration-1-0005-20210311.sql
migration-1-0006-20210531.sql
migration-1-0007-20210611.sql
migration-1-0008-20210727.sql
migration-1-0009-20210727.sql
migration-2-0001-20211003.sql
migration-2-0002-20211007.sql
migration-2-0003-20211013.sql
migration-2-0004-20211014.sql
migration-2-0005-20211018.sql
migration-3-0001-20190816.sql
migration-3-0002-20200521.sql
migration-3-0003-20200702.sql
migration-3-0004-20200810.sql
migration-3-0005-20210116.sql
migration-3-0006-20210116.sql
migration-3-0007-20211022.sql
migration-3-9999-20200728.sql

and currently the db-sync executable checks the md5sum of the files on disk against the ones that were present when db-sync was compiled.

The current code could be modified to only check schema files of the form migration-X-yyyyyyyy.sql where X <= 3

That means anyone could add whatever extra migrations they like as long as they use stage 4 or above. If it breaks, whoever added the extra migrations gets to keep all the pieces.

Does that make sense? Would it help?

[johnnuy]

Yes that would make sense, and yes that would help! Would save me the 20 minutes it takes for me to compile from source when I add a new view.

thanks