Automatically generate Knexfile for migrations

[Apple]

Hello,

Please expose migrations directory .wappler/migrations inside the Docker container, as well as an appropriate Knexfile to facilitate automatic migrations.

This will facilitate future automated deployments (outside of Wappler’s Docker-Machine), something that would carry the following arguments:

knex --migrations-directory .wappler/migrations/db --migrations-table-name wappler_migrations --client pg --connection 'postgres://db_user:db_password@docker:9906/db-name' migrate:status

Related to:

How to run database migration through CLI?

Hi, I’m setting up my NodeJS production deployment using Caprover. Something I haven’t figured out, what’s the command you run to deploy migrations? So I can automatically run that command at each deployment. Otherwise, I may deploy and leave the app hanging due to missing tables On Wappler’s Web Server terminal: [Screenshot]

[JonL]

I missed this one.

Voted, but there is a simple workaround.

Go to your .dockerignore file in your app root and add an exclusion to the list of ignored folders.

So if the content is:

.wappler
**/.git
**/.svn

Change it to something like:

.wappler
!.wappler/migrations
**/.git
**/.svn

For reference:

Dockerfile reference

[Apple]

Awesome! Thanks for the hint!

The remaining work for the Wappler team is to automate the generation of a Knexfile, so we can run knex with this file as the configuration (instead of passing that whole bunch of parameters that may differ with each database setup). Or… we could use jq to extract the DB configs and write the Knexfile by ourselves on-the-fly

[JonL]

I think that one is going to be a bit more difficult (for the team) as the target config files are server model agnostic. Meaning that they have to work with php, asp and node.

[George]

This should be actually doable. We can map indeed the existing migrations to knex/migrations/db_name in docker and also create a knexfile.

Only have to sync all the db credentials in the knexfile as well, with the target names.

Will see what we can do - seems like a great idea to me, so you can fully automate your deployment and db synching.

[JonL]

If you can do that would be peachy!

[George]

I was wondering if there is really a need for knexfile - you can specify all the connection options via de cli parameters.

Also if we create a knexfile - it should be prefixed with connection name as there can be multiple connections. So for the db connection it should become something like db_knexfile.js
and migrations folder padded also with the connection name as sub folder like:

knex/db/migrations and knex/db/seeds

[Apple]

Creating a Knexfile is about making a slightly better user experience, it’s not truly necessary but appreciated

Regardless of the Knexfile implementation, would you be able to expose those migrations in the way you suggested?

Either you or me will have to build the Knexfile anyway. If I were to build the Knexfile I would need to extract the database type and etc. from the existing DB JSON files, because some of the parameters passed on Knex CLI such as database type need to be obtained from the DB JSON files. For PHP do you also use JSON files for DB configuration?

Edit: Feel free to leave Knexfile generation out, I want to get credentials from environment variables anyway, so there’s no guarantee the UI has access to those

[George]

Yes I also think that using environment variables for db connections might be better secure way to go.

Duplicating credentials in a knexfile will only introduce an extra security risk. Specially in PHP where the wwwroot is usually the project root.

So for now u think we will just do the migration paths mapping in docker only.

[karh]

Very much in need of automating this part as well.
I’m deploying new versions of my app with db changes multiple times a week now and forgetting to apply the db migrations in production. Causing big parts of the app to break.

Haven’t wrapped my head around everything that’s said in this thread yet so ELI5 please

@george are you still working on this feature request or does your last reply

@George

So for now u think we will just do the migration paths mapping in docker only.

mean that we gotta do what @JonL described?

And by including the folder in the docker image, we still need to create a command to apply the DB migrations, correct?

Ideally I can change target to ‘production’ and click the big rocket button to have everything deployed (including my db migrations). OR alernatively I use a command to deploy everything.

Either way the goal is to have 1 single action get everything deployed to production + the migrations done