我想要一个数据库迁移工具，它具有以下属性：
其中很多观点都源自我使用这个名为 tern 的出色工具的经验。我很遗憾 javascript 没有相同的功能！ （或者也许我不擅长谷歌搜索......）。所以我认为这对我自己来说是一个很好的编码练习，也是一个对其他人来说可能很有趣的故事:)
让我们偷用设计 CLI 工具！
因此该工具的语法如下：martlet up --database-url
其中“向上”应应用所有尚未应用的迁移，向下应回滚到指定版本。
选项具有以下含义和默认值：
如您所见，在编写任何实际代码之前，我已经开始弄清楚如何调用该工具。这是一个很好的实践，它有助于实现需求并缩短开发周期。
好的，先说第一件事！让我们创建一个index.js 文件并输出帮助消息。它看起来像这样：
function printHelp() {
console.log(
"Usage: martlet up --driver <driver> --dir <dir> --database-url <url>",
);
console.log(
" martlet down <version> --driver <driver> --dir <dir> --database-url <url>",
);
console.log(
" <version> is a number that specifies the version to migrate down to",
);
console.log("Options:");
console.log(' --driver <driver> Driver to use, default is "pg"');
console.log(' --dir <dir> Directory to use, default is "migrations"');
console.log(
" --database-url <url> Database URL to use, default is DATABASE_URL environment variable",
);
}
printHelp();
现在我们将解析选项：
export function parseOptions(args) {
const options = {
dir: "migrations",
driver: "pg",
databaseUrl: process.env.DATABASE_URL,
};
for (let idx = 0; idx < args.length; ) {
switch (args[idx]) {
case "--help":
case "-h": {
printHelp();
process.exit(0);
}
case "--dir": {
options.dir = args[idx + 1];
idx += 2;
break;
}
case "--driver": {
options.driver = args[idx + 1];
idx += 2;
break;
}
case "--database-url": {
options.databaseUrl = args[idx + 1];
idx += 2;
break;
}
default: {
console.error(`Unknown option: ${args[idx]}`);
printHelp();
process.exit(1);
}
}
}
return options;
}
如你所见，我没有使用任何库进行解析；我只是简单地迭代参数列表并处理每个选项。因此，如果我有一个布尔选项，我会将迭代索引移动 1，如果我有一个带有值的选项，我会将其移动 2。
为了支持多个驱动程序，我们需要有一些通用接口来访问数据库；它可能是这样的：
interface Adapter {
connect(url: string): Promise<void>;
transact(query: (fn: (text) => Promise<ResultSet>)): Promise<ResultSet>;
close(): Promise<void>;
}
我认为 connect 和 close 是非常明显的函数，让我解释一下 transact 方法。它应该接受一个函数，该函数将被一个接受查询文本并返回带有中间结果的承诺的函数调用。这种复杂性需要有一个通用接口来提供在事务内运行多个查询的能力。通过查看使用示例更容易掌握。
这就是适配器查找 postgres 驱动程序的方式：
class PGAdapter {
constructor(driver) {
this.driver = driver;
}
async connect(url) {
this.sql = this.driver(url);
}
async transact(query) {
return this.sql.begin((sql) => (
query((text) => sql.unsafe(text))
));
}
async close() {
await this.sql.end();
}
}
用法示例可以是：
import postgres from "postgres";
const adapter = new PGAdapter(postgres);
await adapter.connect(url);
await adapter.transact(async (sql) => {
const rows = await sql("SELECT * FROM table1");
await sql(`INSERT INTO table2 (id) VALUES (${rows[0].id})`);
});
const PACKAGES = {
pg: "postgres@3.4.4",
};
const downloadDriver = async (driver) => {
const pkg = PACKAGES[driver];
if (!pkg) {
throw new Error(`Unknown driver: ${driver}`);
}
try {
await stat(join(process.cwd(), "yarn.lock"));
const lockfile = await readFile(join(process.cwd(), "yarn.lock"));
const packagejson = await readFile(join(process.cwd(), "package.json"));
spawnSync("yarn", ["add", pkg], {
stdio: "inherit",
});
await writeFile(join(process.cwd(), "yarn.lock"), lockfile);
await writeFile(join(process.cwd(), "package.json"), packagejson);
return;
} catch {}
spawnSync("npm", ["install", "--no-save", "--legacy-peer-deps", pkg], {
stdio: "inherit",
});
};
我们首先尝试使用yarn安装驱动程序，但我们不想在目录中生成任何差异，因此我们保留yarn.lock和package.json文件。如果yarn不可用，我们将回退到npm。
当我们确保安装了驱动程序后，我们可以创建一个适配器并使用它：
export async function loadAdapter(driver) {
await downloadDriver(driver);
return import(PACKAGES[driver].split("@")[0]).then(
(m) => new PGAdapter(m.default),
);
我们首先连接到数据库并获取当前版本：
await adapter.connect(options.databaseUrl);
console.log("Connected to database");
const currentVersion = await adapter.transact(async (sql) => {
await sql(`create table if not exists schema_migrations (
version integer primary key
)`);
const result = await sql(`select version from schema_migrations limit 1`);
return result[0]?.version || 0;
});
console.log(`Current version: ${currentVersion}`);
Then, we read the migrations directory and sort them by version. After that, we apply every migration that has a version greater than the current one. I will just present the actual migration in the following snippet:
await adapter.transact(async (sql) => {
await sql(upMigration);
await sql(
`insert into schema_migrations (version) values (${version})`
);
await sql(`delete from schema_migrations where version != ${version}`);
});
The rollback migration is similar, but we sort the migrations in reverse order and apply them until we reach the desired version.
I decided not to use any specific testing framework but use the built-in nodejs testing capabilities. They include the test runner and the assertion package.
import { it, before, after, describe } from "node:test";
import assert from "node:assert";
And to execute tests I would run node --test --test-concurrency=1.
Actually, I was writing the code in a sort of TDD manner. I didn't validate that my migrations code worked by hand, but I was writing it along with tests. That's why I decided that end-to-end tests would be the best fit for this tool.
For such an approach, tests would need to bootstrap an empty database, apply some migrations, check that database contents are correct, and then roll back to the initial state and validate that the database is empty.
To run a database, I used the "testcontainers" library, which provides a nice wrapper around docker.
before(async () => {
console.log("Starting container");
container = await new GenericContainer("postgres:16-alpine")
.withExposedPorts(5432)
.withEnvironment({ POSTGRES_PASSWORD: "password" })
.start();
});
after(async () => {
await container.stop();
});
I wrote some simple migrations and tested that they worked as expected. Here is an example of a database state validation:
const sql = pg(`postgres://postgres:password@localhost:${port}/postgres`);
const result = await sql`select * from schema_migrations`;
assert.deepEqual(result, [{ version: 2 }]);
const tables =
await sql`select table_name from information_schema.tables where table_schema = 'public'`;
assert.deepEqual(tables, [
{ table_name: "schema_migrations" },
{ table_name: "test" },
]);
This was an example of how I would approach the development of a simple CLI tool in the javascript ecosystem. I want to note that the modern javascript ecosystem is pretty charged and powerful, and I managed to implement the tool with a minimum of external dependencies. I used a postgres driver that would be downloaded on demand and testcontainers for tests. I think that approach gives developers the most flexibility and control over the application.
以上是编码练习：nodejs 中的数据库迁移工具的详细内容。更多信息请关注PHP中文网其他相关文章！
