A way to completely reset Kratos DB

[frederikhors]

Preflight checklist

• [X] I could not find a solution in the existing issues, docs, nor discussions.
• [X] I agree to follow this project's Code of Conduct.
• [X] I have read and am following this repository's Contribution Guidelines.
• [ ] This issue affects my Ory Network project.
• [ ] I have joined the Ory Community Slack.
• [ ] I am signed up to the Ory Security Patch Newsletter.

Describe your problem

I was using Heroku for projects in development.

Heroku has a great option to reset Postgres DB: https://devcenter.heroku.com/articles/managing-heroku-postgres-using-cli#pg-reset.

Now I'm using other cloud services for Postgres hosting which don't have such feature.

Since during the development phases the DB (the identity schemas and so on) can change considerably I would like to automatically reset the DB during each deployment before being filled again by my new code.

How do I reset the DB (as if it was just created) if the hosting doesn't provide me a CLI/API to do it but only a web interface and of course the connection string?

Is there a way to completely reset Kratos DB?

Is there a way to roll down migrations to 0? Maybe with a command like:

kratos migrate sql down

Version

0.10.1

[aeneasr]

Exactly :)

$ kratos help migrate sql down
Run this command on a fresh SQL installation and when you upgrade Ory Kratos to a new minor version.

It is recommended to run this command close to the SQL instance (e.g. same subnet) instead of over the public internet.
This decreases risk of failure and decreases time required.

You can read in the database URL using the -e flag, for example:
    export DSN=...
    kratos migrate sql -e

### WARNING ###

Before running this command on an existing database, create a back up!

Usage:
  kratos migrate sql <database-url> [flags]

Flags:
  -c, --config strings   Path to one or more .json, .yaml, .yml, .toml config files. Values are loaded in the order provided, meaning that the last config file overwrites values from the previous config file.
  -h, --help             help for sql
  -e, --read-from-env    If set, reads the database connection string from the environment variable DSN or config file key dsn.
  -y, --yes              If set all confirmation requests are accepted without user interaction.

Alternatively connect using a postgres client and do DROP DATABASE ...; CREATE DATABASE ...

[frederikhors]

Thanks.

I tried right now. It doesn't work.

I launch it like this:

./kratos -c ./config.yml migrate sql down -e --yes

it prints:

Successfully applied SQL migrations!

and the tables are the same (no changes to DB).

If I launch:

./kratos -c ./config.yml migrate sql -e --yes

the message is the same and the migrations are correctly executed.

[frederikhors]

@aeneasr can you at least confirm that down doesn't work? I'm not asking for an ETA. 😄

[github-actions[bot]]

Hello contributors!

I am marking this issue as stale as it has not received any engagement from the community or maintainers for a year. That does not imply that the issue has no merit! If you feel strongly about this issue

• open a PR referencing and resolving the issue;
• leave a comment on it and discuss ideas on how you could contribute towards resolving it;
• leave a comment and describe in detail why this issue is critical for your use case;
• open a new issue with updated details and a plan for resolving the issue.

Throughout its lifetime, Ory has received over 10.000 issues and PRs. To sustain that growth, we need to prioritize and focus on issues that are important to the community. A good indication of importance, and thus priority, is activity on a topic.

Unfortunately, burnout has become a topic of concern amongst open-source projects.

It can lead to severe personal and health issues as well as opening catastrophic attack vectors.

The motivation for this automation is to help prioritize issues in the backlog and not ignore, reject, or belittle anyone.

If this issue was marked as stale erroneously you can exempt it by adding the backlog label, assigning someone, or setting a milestone for it.

Thank you for your understanding and to anyone who participated in the conversation! And as written above, please do participate in the conversation if this topic is important to you!

Thank you 🙏✌️

[frederikhors]

Nope.