launchbadge
sqlx-cli: Drop all current connections when dropping the database
I'm frequently using the following sequence sqlx database drop; sqlx database create; sqlx migrate run when resetting the database due to testing and every so often I'm getting the error: error returned from database: database "untitled" is being accessed by other users error. This can be solved by dropping all current connections to the database by executing
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();
within the PostgreSQL CLI. It would however be convenient if sqlx-cli would do this automatically. Would you guys be open for such a feature, I'd even be willing to work on this.
I could see this added as a --force flag but it probably shouldn't do it by default. It would need superuser permissions and could break running applications that the user had forgotten about.
sqlx database drop; sqlx database create; sqlx migrate run
By the way, that can be expressed as just sqlx database reset
I could see this added as a --force flag but it probably shouldn't do it by default. It would need superuser permissions and could break running applications that the user had forgotten about.
Sounds good, out of curiosity why would this need superuser permissions though? I've (only) tested this on my local machine and no permissions were needed whatsoever.
By the way, that can be expressed as just sqlx database reset
Thanks, I must have missed that!
I meant superuser permissions on the database, not like sudo or anything like that. The default postgres user is a superuser, but if you're on a managed multitenant Postgres instance you likely don't have permission to execute pg_terminate_backend().
