sqitch deploy requires the uri to point to an existing schema, making it hard to start from an empty state

[FelixZY]

I'm setting up a sqitch project where I want to include the initial schema creation among the migration files:

deploy/schemas/appschema.sql

-- Deploy app:schemas/appschema to mysql
begin;

CREATE DATABASE IF NOT EXISTS appschema;

COMMIT;

I therefore have a sqitch.conf with

[target "dev"]
	uri = db:mysql://root:password@127.0.0.1:3306/
	registry = appschema_sqitch

However, this causes sqitch deploy to fail on a clean database setup (mysql container):

$ sqitch deploy
warning: Database name missing in URI "db:mysql://root:password@127.0.0.1:3306/"
"appschema_sqitch" failed to start: "No such file or directory

Expected behavior

URIs without a target database should be accepted. It should be up to the user to qualify their statements or call e.g. USE appschema in scripts in such cases.

---

Sqitch: v1.4.1
OS: Debian 11 (running in devcontainer under Ubuntu 20.04.6 LTS (focal) (64-bit) on Docker version 25.0.4, build 1a576c5)
Database: MySql 8.0.32

[FelixZY]

My current workaround is to target information_schema but that does come with risks, such as accidentally creating tables where they're not supposed to be.

[theory]

Sqitch does not create the database. It must already exist. Everything after that should be good tho.

[FelixZY]

@theory in mysql CREATE DATABASE is synonymous with CREATE SCHEMA. Is there a reason for not supporting this/projects that make use of multiple schemas? What is the motivation behind requiring a schema to be specified?

[FelixZY]

I would also add that the documentation indicates that it should be possible to exclude the schema from the uri:

• uri

The database connection URI for the target. Required. Its format is:

db:engine:[dbname]
db:engine:[//[user[:password]@][host][:port]/][dbname][?params][#fragment]

Some examples:

[...]
db:mysql://db.example.com/