Hope that the sqlx migrate run command can have the functionality to only write to the _sql_migrations table without executing SQL statements.

[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 run --fake --target-version [version]

The meaning of this command：In the interval [1, version], do not execute the SQL statements in the migration files; instead, set the status of the migration files to success and write to the _sqlx_migrations table.

--fake: type bool default false

Is this a breaking change? Why or why not?

This is not a breaking change.

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

Secondly, the default value of --fake is false, so the meaning of sqlx migrate run has not changed, maintaining compatibility with the original usage.