Support manual modification of the _sql_migrations table.

[hushoujier]

I have found these related issues/pull requests

none

Description

I currently have an old project that I want to refactor using Rust and SQLx.

This project already has a database that includes data structures and data. To use sqlx for migration management of the existing database, I wrote up migration files and down migration files that are consistent with the current state of the existing database.

But when I execute sqlx migrate run, it repeatedly creates tables, indexes, and other statements, causing errors.

The up migration file and down migration file after removing sensitive information are as follows:

0001_init.up.sql

-- Sqlite
CREATE TABLE "example_author" ("author_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(64) NOT NULL UNIQUE);
CREATE TABLE "example_reader" ("reader_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(64) NOT NULL UNIQUE);
CREATE TABLE "example_book" ("book_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(128) NOT NULL UNIQUE, "price" real NOT NULL, "description" text DEFAULT '' NULL, CONSTRAINT "example_book_check" CHECK ("price" >= 0.0));
CREATE TABLE "example_book_author_relation" ("book_author_relation_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "publish_datetime" datetime NOT NULL, "author_id" bigint NOT NULL REFERENCES "example_author" ("author_id") DEFERRABLE INITIALLY DEFERRED, "book_id" bigint NOT NULL REFERENCES "example_book" ("book_id") DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE "example_book_reader_relation" ("book_reader_relation_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "viewed_datetime" datetime NOT NULL, "book_id" bigint NOT NULL REFERENCES "example_book" ("book_id") DEFERRABLE INITIALLY DEFERRED, "reader_id" bigint NOT NULL REFERENCES "example_reader" ("reader_id") DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX "example_book_author_relation_author_id_85d41095" ON "example_book_author_relation" ("author_id");
CREATE INDEX "example_book_author_relation_book_id_7d9100b8" ON "example_book_author_relation" ("book_id");
CREATE INDEX "example_book_reader_relation_book_id_3d79974d" ON "example_book_reader_relation" ("book_id");
CREATE INDEX "example_book_reader_relation_reader_id_646686e1" ON "example_book_reader_relation" ("reader_id");

-- Omit many INSERT INTO statements

0001_init.down.sql

-- Sqlite
DROP TABLE "example_book_reader_relation";
DROP TABLE "example_book_author_relation";
DROP TABLE "example_book";
DROP TABLE "example_reader";
DROP TABLE "example_author";

Prefered solution

Add command：sqlx migrate fake --type add --target-versions [version1 version2 ...]

The meaning of this command：The [version1 version2 ...] do not execute the SQL statements in the migration files; instead, add new migration record and set the status of the migration files to success and write to the _sqlx_migrations table.

Add command：sqlx migrate fake --type update --target-versions [version1 version2 ...]

The meaning of this command：The [version1 version2 ...] do not execute the SQL statements in the migration files; instead, update migration record and set the status of the migration files to success and update to the _sqlx_migrations table.

Add command：sqlx migrate fake --type delete --target-versions [version1 version2 ...]

The meaning of this command：delete the [version1 version2 ...] migration record from the _sqlx_migrations table.

Is this a breaking change? Why or why not?

This is not a breaking change.

This is about adding new features rather than modifying existing ones, so it will not break any current functionality.