sqlite-migrate

一个基于sqlite-utils的简单SQLite数据库迁移系统。

此项目是早期alpha版本。预期会有破坏性更改。

安装

此工具作为sqlite-utils的插件工作。首先安装它

pip install sqlite-utils

然后按照以下方式安装此插件

sqlite-utils install sqlite-migrate

迁移文件

此工具针对迁移文件工作。一个迁移文件看起来像这样

from sqlite_migrate import Migrations

# Pick a unique name here - it must not clash with other migration sets that
# the user might run against the same database.

migration = Migrations("creatures")

# Use this decorator against functions that implement migrations
@migration()
def m001_create_table(db):
    # db is a sqlite-utils Database instance
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id"
    )

@migration()
def m002_add_weight(db):
    # db is a sqlite-utils Database instance
    db["creatures"].add_column("weight", float)

以下是传递给每个迁移函数的数据库实例文档

运行迁移

运行此命令将按顺序对指定的数据库文件执行这些迁移。

使用您的数据库和要应用的迁移文件的路径调用migrate

sqlite-utils migrate creatures.db path/to/migrations.py

多次运行此命令不会有额外影响，除非您向文件中添加更多迁移函数。

如果您不带参数调用它，它将在当前目录或其任何子目录中搜索并应用任何migrations.py文件。

您也可以传递一个目录的路径，在这种情况下，将应用该目录及其子目录中的所有migrations.py文件

sqlite-utils migrate creatures.db path/to/parent/

在应用单个迁移文件时，您可以使用--stop-before选项应用所有迁移，但不包括指定的迁移

sqlite-utils migrate creatures.db path/to/migrations.py --stop-before m002_add_weight

列出迁移

添加--list来列出迁移而不运行它们，例如

sqlite-utils migrate creatures.db --list

输出将类似于以下内容

Migrations for: creatures

  Applied:
    m001_create_table - 2023-07-23 04:09:40.324002
    m002_add_weight - 2023-07-23 04:09:40.324649
    m003_add_age - 2023-07-23 04:09:44.441616
    m003_cleanup - 2023-07-23 04:09:44.443394
    m004_cleanup - 2023-07-23 04:09:44.444184
    m005_cleanup - 2023-07-23 04:09:44.445389
    m006_cleanup - 2023-07-23 04:09:44.446742
    m007_cleanup - 2023-07-23 04:16:02.529983

  Pending:
    m008_cleanup

详细模式

添加-v或--verbose以获取详细输出，它将显示在应用迁移前后以及差异的架构

sqlite-utils migrate creatures.db --verbose

示例输出

Migrating creatures.db

Schema before:

  CREATE TABLE "_sqlite_migrations" (
     [migration_set] TEXT,
     [name] TEXT,
     [applied_at] TEXT,
     PRIMARY KEY ([migration_set], [name])
  );
  CREATE TABLE [creatures] (
     [id] INTEGER PRIMARY KEY,
     [name] TEXT,
     [species] TEXT
  , [weight] FLOAT);

Schema after:

  CREATE TABLE "_sqlite_migrations" (
     [migration_set] TEXT,
     [name] TEXT,
     [applied_at] TEXT,
     PRIMARY KEY ([migration_set], [name])
  );
  CREATE TABLE "creatures" (
     [id] INTEGER PRIMARY KEY,
     [name] TEXT,
     [species] TEXT,
     [weight] FLOAT,
     [age] INTEGER,
     [shoe_size] INTEGER
  );

Schema diff:

    [applied_at] TEXT,
    PRIMARY KEY ([migration_set], [name])
  );
-CREATE TABLE [creatures] (
+CREATE TABLE "creatures" (
    [id] INTEGER PRIMARY KEY,
    [name] TEXT,
-   [species] TEXT
-, [weight] FLOAT);
+   [species] TEXT,
+   [weight] FLOAT,
+   [age] INTEGER,
+   [shoe_size] INTEGER
+);