Data migrations are notoriously difficult to test. They take a long time to run on large datasets. They often involve heavy, inflexible database engines. And they’re only meant to run once, so people think it’s throw-away code, and therefore not worth writing tests for. Not so.

眾所周知，數據遷移很難測試。 他們需要很長時間才能在大型數據集上運行。 它們通常涉及笨重，僵化的數據庫引擎。 而且它們只打算運行一次，所以人們認為它是一次性的代碼，因此不值得編寫測試。 不是這樣

With a few modern tools, we can test migrations just like we do our production code. Let me show you one approach.

使用一些現代工具，我們可以像測試生產代碼一樣測試遷移。 讓我向您展示一種方法。

鳥瞰 (Bird’s-eye view)

We’ll be moving data from a PostgreSQL database to MongoDB using Node.js. Obviously, you can substitute any of these. Anything that runs in Docker, like PostgreSQL, or in-memory, like MongoDB, will fit the approach. Also, any programming language is fine, if you’re comfortable with it. I chose Node.js for the plasticity it offers, which is useful to have in data transformations.

我們將使用Node.js將數據從PostgreSQL數據庫移動到MongoDB 。 顯然，您可以替換其中任何一個。 任何在Docker(例如PostgreSQL)或內存(例如MongoDB)中運行的東西都適合該方法。 另外，如果您愿意的話，任何編程語言都可以。 我選擇Node.js是因為它具有可塑性，在數據轉換中非常有用。

We’ll split the migration into several functions, organized around data scopes (like companies, users, whatever you have in the database), each function receiving an instance of the PostgreSQL and MongoDB client, i.e. migrateUsers(postgresClient, mongoClient). This way, our test code can provide connections to the test databases, and production code to production ones.

我們將遷移分為幾個函數，這些函數圍繞數據范圍(例如公司，用戶，無論您在數據庫中擁有什么)進行組織，每個函數接收PostgreSQL和MongoDB客戶端的實例，即migrateUsers(postgresClient, mongoClient) 。 這樣，我們的測試代碼可以提供到測試數據庫的連接，而生產代碼則可以提供到生產數據庫的連接。

Each migration function will have its accompanying test suite, i.e. migrate-users.js will have migrate-users.specs.js.

每個遷移功能都有其隨附的測試套件，即migrate-users.js將具有migrate-users.specs.js 。

         /-> migrateCompanies()  <-- migrate-companies.spec.js
index.js --> migrateUsers()      <-- migrate-users.spec.js
         \-> migrateFizzBuzzes() <-- migrate-fizzBuss.spec.js

The test suites will also share a file we’ll call test-init.js containing code that’ll launch both test databases and connect clients, then shut them down after tests finish.

測試套件還將共享一個名為test-init.js的文件，其中包含將啟動兩個測試數據庫并連接客戶端，然后在測試完成后將其關閉的代碼。

The article comes with an accompanying GitHub repository, containing all the code we’re discussing here. I’ll solely be quoting excerpts to illustrate key concepts, and you may prefer to follow them by studying the full source code.

本文帶有隨附的GitHub存儲庫 ，其中包含我們在此處討論的所有代碼。 我僅引用摘錄來說明關鍵概念，并且您可能更喜歡通過研究完整的源代碼來遵循它們。

安裝工具 (Install the tools)

Install Docker Engine with Docker Compose for your platform. Make sure you can run the docker command without requiring user rights elevation (i.e. without sudo). On Linux, this is typically achieved by adding your current user to the docker group:

在您的平臺上安裝帶有Docker Compose的 Docker引擎 。 確保您可以運行docker命令而無需提升用戶權限(即，無需sudo )。 在Linux上，通常是通過將當前用戶添加到docker組來實現的：

sudo usermod -aG docker $USER

after which you’ll need to log out and back in again.

之后，您需要注銷并重新登錄。

You’ll also need Node.js.

您還需要Node.js。

Start a new npm project by going through the npm init steps, then install the dependencies we’ll be using:

通過執行npm init步驟開始一個新的npm項目，然后安裝我們將使用的依賴項：

npm install -E mongodb pg
npm install -ED jest mongodb-memory-server shelljs

We’ll use jest to run the tests, but again, go with whatever suits your needs.

我們將使用jest來運行測試，但同樣，請根據您的需要進行選擇。

配置工具 (Configure the tools)

We’ll be running PostgreSQL in Docker, so let’s first configure that. Create a file called docker-compose.yml and paste in the following:

我們將在Docker中運行PostgreSQL，因此我們首先對其進行配置。 創建一個名為 docker-compose.yml的文件，然后粘貼以下內容：

version: '3'
services:
  postgresql:
    image: postgres:12   #1
    container_name: postgresql-migration-test
    environment:
      POSTGRES_PASSWORD: 'password'   #2
    ports:
      - "5432"   #3

This will configure a Docker container with the latest PostgreSQL 12.x (1), default user and password for password (2). (Never use that outside of testing, obviously.) The above also says that PostgreSQL’s standard port 5432 will be exposed to a random port on the host (3). Thanks to that, you can safely launch the container on a host machine where PostgreSQL is already running, thus avoiding test failures due to port conflicts. We’ll have a way to find the right port to connect to in tests.

這將配置與碼頭工人，容器最新的PostgreSQL12.x (1)，默認用戶名和password的密碼(2)。 (顯然，切勿在測試之外使用該端口。)上面的內容還表明PostgreSQL標準端口5432將暴露給主機上的隨機端口(3)。 因此，您可以在已經運行PostgreSQL主機上安全啟動容器，從而避免由于端口沖突而導致測試失敗。 我們將提供一種方法來找到測試中要連接的正確端口。

Now, if you run the command docker-compose up in the same directory, the container with the database should start and display PostgreSQL’s standard output. If it did, great; shut it down with Ctrl+c, followed by docker-compose down and we’ll configure the test suite to do the starting and stopping later.

現在，如果在同一目錄中運行命令docker-compose up ，則包含數據庫的容器應啟動并顯示PostgreSQL標準輸出。 如果可以，那就太好了； 使用Ctrl+c其關閉，然后使用docker-compose down將其docker-compose down ，我們將配置測試套件以稍后進行啟動和停止。

Move on to your package.json and add the tasks we’ll be using into the scripts section:

轉到package.json并將我們將要使用的任務添加到scripts部分：

"scripts": {
  "start": "node src/index.js",
  "test": "jest --runInBand"   //1},

With that, npm start will run your migration and npm test will run the tests.

這樣， npm start將運行您的遷移，而npm test將運行測試。

Notice the --runInBand parameter for jest (1). That tells jest to run every test suite in sequence, whereas by default these run in parallel. This is important, because all test suites share the same database instances and running them simultaneously would cause data conflicts. Don’t worry about speed, though — we’re not dealing with a lot of data here, so tests will still be fast.

注意jest (1)的--runInBand參數。 這告訴jest 依次運行每個測試套件，而默認情況下，它們并行運行。 這很重要，因為所有測試套件都共享相同的數據庫實例，并且同時運行它們會導致數據沖突。 不過，請不要擔心速度-這里我們不會處理大量數據，因此測試仍將很快。

jest itself also needs a bit of configuration, so add a file called jest.config.js with the following content:

jest本身也需要一些配置，因此添加一個名為jest.config.js的文件，其內容如下：

module.exports = {
    testEnvironment: "jest-environment-node",
    moduleFileExtensions: ["js"],
    testRegex: ".spec.js$",
}

This simply tells jest to treat any file ending with .spec.js as a test suite to run, and to use its node environment (as opposed to the default jsdom useful for code meant to run in the browser).

這只是簡單地告訴jest將以.spec.js結尾的任何文件視為要運行的測試套件，并使用其node環境(與默認的jsdom對于打算在瀏覽器中運行的代碼有用)相對。

Ready. Let’s start testing.

準備。 讓我們開始測試。

設置測試環境 (Set up the test environment)

We’ll start with creating the src/test-init.js file, which will deal with database startup and teardown, and will be included by every test suite. It’s a crude method, I know, and jest claims to have better ways, but I’ll exclude them from this text for clarity.

我們將從創建src/test-init.js文件開始，該文件將處理數據庫的啟動和拆卸，并將包含在每個測試套件中。 我知道這是一種粗略的方法，并且jest 聲稱有更好的方法 ，但是為了清楚起見，我將從本文中排除它們。

const shell = require("shelljs");const {MongoMemoryServer} = require("mongodb-memory-server");const {getPostgresClient} = require("./postgres-client-builder");const {afterAll, afterEach, beforeAll} = require("@jest/globals");const {getMongoClient} = require("./mongo-client-builder");let clients = {};   //1let mongod;const dockerComposeFile = __dirname + '/../docker-compose.yml';beforeAll(async () => {
    console.log('Starting in memory Mongo server');
    mongod = new MongoMemoryServer({autoStart: false});   //2
    await mongod.start();
    const mongoUri = await mongod.getUri();
    clients.mongo = await getMongoClient(mongoUri);   //3
    clients.mongoDb = clients.mongo.db();
    console.log('Starting Docker container with PostgreSQL');
    shell.exec(`docker-compose -f ${dockerComposeFile} up -d`);  //4
    await new Promise(resolve => setTimeout(resolve, 1000));   //5
    const postgresHost = shell.exec(`docker-compose -f ${dockerComposeFile} port postgresql 5432`).stdout;   //6
    const postgresUri = `postgresql://postgres:password@${postgresHost}`;
    clients.postgres = await getPostgresClient(postgresUri);   //7
});afterAll(async () => {
    await clients.mongo.close();   //8
    await clients.postgres.end();
    await mongod.stop();
    console.log('Mongo server stopped')
    shell.exec(`docker-compose -f ${dockerComposeFile} down`);   //9
    console.log('PostgreSQL Docker container stopped');
});module.exports = {
    clients,
}

Let’s go through this step by step. First, we’re declaring and exporting the variable clients (1) which will allow us to pass database clients to the test suites.

讓我們逐步進行此步驟。 首先，我們聲明并導出變量clients (1)，這將使我們能夠將數據庫客戶端傳遞給測試套件。

beforeAll is all about starting the databases. The first part starts MongoDB and follows standard instructions from the mongodb-memory-server package (2). There’s a call to a custom function getMongoClient()(3) here that I left out which simply builds a MongoClient instance from the mongodb package.

beforeAll與啟動數據庫有關。 第一部分啟動MongoDB，并遵循mongodb-memory-server 軟件包 (2)中的標準說明。 我在這里忽略了對自定義函數getMongoClient() (3)的調用，該函數getMongoClient() mongodb 包 構建 MongoClient 實例 。

Then it gets interesting. shell allows us to run shell commands, and we’re simply calling docker-compose up (4) to start the PostgreSQL container we configured earlier. That’s followed by a one-second wait (5) to let the database start accepting connections on the port. Next, we need to see what port on the host machine was assigned to the container. That’s what docker-compose port does (6) and it also outputs the local IP address, i.e. 0.0.0.0:12345 so we can store that directly in postgresUri and use another custom function getPostgresClient() (7) to build the PostgreSQL client instance.

然后變得有趣。 shell允許我們運行shell命令，而我們只是簡單地調用docker-compose up (4)來啟動我們之前配置的PostgreSQL容器。 接下來是等待一秒鐘(5)，以使數據庫開始接受端口上的連接。 接下來，我們需要查看主機上的哪個端口已分配給容器。 這就是docker-compose port作用(6)，它還輸出本地IP地址，即0.0.0.0:12345因此我們可以將其直接存儲在postgresUri并使用另一個自定義函數getPostgresClient() (7)來構建PostgreSQL客戶端實例 。

You’ll notice I use two extra options for docker-compose: -f points to the docker-compose.yml file which is in the parent directory of src/test-init.js and -d runs the containers as daemons, so they’re not holding up the tests.

您會注意到我對docker-compose使用了兩個額外的選項： -f指向src/test-init.js的父目錄中docker-compose.yml文件，而-d將容器作為守護程序運行，因此它們沒有阻止測試。

afterAll reverses the process. First, we disconnect the clients from the databases (8). Then we stop MongoDB and finally shut down the PostgreSQL Docker container with docker-compose down (9).

afterAll撤消該過程。 首先，我們將客戶端與數據庫斷開連接(8)。 然后我們停止MongoDB，最后通過docker-compose down (9)關閉PostgreSQL Docker容器。

The src/test-init.js file needs one more function that will clean both databases after each test.

src/test-init.js文件還需要一個函數，該函數將在每次測試后清除兩個數據庫。

afterEach(async () => {
    const tables = await clients.postgres.query(
        `SELECT table_name FROM information_schema.tables WHERE table_schema ='public';`
    );
    await Promise.all(tables.rows.map(row => clients.postgres.query(`DROP TABLE ${row.table_name}`)));
    const collections = await clients.mongoDb.collections();
    await Promise.all(collections.map(collection => clients.mongoDb.dropCollection(collection.collectionName)));
});

We’re simply dropping all tables from PostgreSQL and collections from MongoDB, leaving a clean slate for the next test. You can truncate the tables instead, so you won’t need to recreate them before each test. Either way is fine.

我們只是刪除了PostgreSQL中的所有表和MongoDB中的集合，為下一個測試留出了一塊空白。 您可以截斷表，因此無需在每次測試之前重新創建它們。 兩種方法都可以。

The code in src/test-init.js is the magic sauce of the solution. It’ll ensure that there are two, real databases running for your migration code to operate on, make sure they’re clean for each test case, and shut them down after all the tests finish running.

src/test-init.js的代碼是該解決方案的神奇之處。 它將確保有兩個真實的數據庫正在運行，以供您的遷移代碼運行，確保它們對于每個測試用例都是干凈的，并在所有測試完成后關閉它們。

創建測試套件 (Create the test suite)

We’re ready to start writing test code. Our actual migration code will be split into functions for each data type. We could be migrating user accounts, so we’ll have a file called src/migrate-users.js and inside:

我們準備開始編寫測試代碼。 我們的實際遷移代碼將分為每種數據類型的函數。 我們可能正在遷移用戶帳戶，因此我們將在其中包含一個名為src/migrate-users.js的文件：

async function migrateUsers(postgresClient, mongoClient) {
    // Migration logic goes here
}

We’ll be calling this function in our test suite at src/migrate-users.spec.js.

我們將在測試套件中的src/migrate-users.spec.js上調用此函數。

Start with a beforeEach() call to create the necessary tables (because we’re dropping them; if you opted to truncate them, you can skip this step):

從beforeEach()調用開始以創建必要的表(因為我們正在刪除它們；如果您選擇截斷它們，則可以跳過此步驟)：

beforeEach(async () => {
    await clients.postgres.query(`CREATE TABLE users (id integer, username varchar(20))`);
});

We’re not adding any indexes or constraints, as we’ll be dealing with small amounts of strictly controlled data. That said, your specific use case may warrant it.

我們不會添加任何索引或約束，因為我們將處理少量嚴格控制的數據。 就是說，您的特定用例可能會得到保證。

Now, we’ll write the actual test case:

現在，我們將編寫實際的測試用例：

it('migrates one user', async () => {
    await clients.postgres.query(`
        INSERT INTO users (id, username) 
        VALUES (1, 'john_doe')
        `);
    await migrateUsers(clients.postgres, clients.mongo);
    const users = await clients.mongoDb.collection('users').find().toArray();
    expect(users).toHaveLength(1);
    expect(users[0].username).toEqual('john_doe');
});

It looks simple and it is. We’re inserting a user into the source database (PostgreSQL), then calling the migrateUsers() function, passing in the database clients connected to our test databases, then fetching all users from the target database (MongoDB), verifying that exactly one was migrated and that its username value is what we’re expecting.

看起來很簡單，確實如此。 我們正在將一個用戶插入源數據庫(PostgreSQL)，然后調用migrateUsers()函數，傳入連接到我們測試數據庫的數據庫客戶端，然后從目標數據庫(MongoDB)中獲取所有用戶，并驗證是否確實有一個已遷移，其username值正是我們所期望的。

Try running the test, either with an npm test or from within your favorite IDE.

嘗試通過npm test或從您喜歡的IDE中npm test 。

You can continue building out your test suite, adding more data, conditions and cases. Some of the items you should test for, are:

您可以繼續構建測試套件，添加更多數據，條件和案例。 您應該測試的一些項目是：

• data transformations — if you’re mapping or changing data,
  數據轉換-如果您要映射或更改數據，
• missing or incorrect data — make sure your code expects and handles these,
  丟失或不正確的數據-確保您的代碼期望并處理這些數據，
• data scope — check that you’re only migrating the records you want,
  數據范圍-檢查您是否僅在遷移所需的記錄，
• joins — if you’re putting together data from several tables, what if there is no match? What if there are multiple matches?
  連接-如果將多個表中的數據放在一起，如果不匹配怎么辦？ 如果有多個匹配項怎么辦？
• timestamps & time zones — when you’re moving these between tables, make sure they still describe the same point in time.
  時間戳和時區-在表之間移動時，請確保它們仍描述相同的時間點。

運行實際的遷移 (Run the actual migration)

You can test-drive and build your whole migration code with the above setup, but eventually you’ll want to run it on actual data. I suggest you try that early on a copy of the database you’ll be migrating, because that’ll uncover conditions you haven’t considered, and will inspire more test cases.

您可以使用上述設置測試驅動并構建您的整個遷移代碼，但最終您將需要在實際數據上運行它。 我建議您盡早在要遷移的數據庫副本上嘗試一下 ，因為這將發現您尚未考慮的條件，并會激發更多的測試用例。

You’ll have several migration functions for various data scopes, so what you’ll need now is the src/index.js file to call them in sequence:

您將具有針對各種數據范圍的幾種遷移功能，因此現在需要的是src/index.js文件，以按順序調用它們：

run().then().catch(console.error);async function run() {
    const postgresClient = await buildPostgresClient();
    const mongoClient = await buildMongoClient();
    console.log('Starting migration');
    await migrateCompanies(postgresClient, mongoClient);
    await migrateUsers(postgresClient, mongoClient);
    await migrateFizzBuzzes(postgresClient, mongoClient);
    console.log(`Migration finished`);
}

And you can run the actual migration with npm start.

您可以使用npm start運行實際的遷移。

建立測試套件 (Build out the test suite)

Your test suite will quickly grow in scope and complexity, so here are a few more ideas to help you on your way:

您的測試套件的范圍和復雜性將Swift增長，因此，這里有一些其他想法可以幫助您：

• docker-compose.yml can be extended with more services, if you need them: additional databases, external services, etc. They’ll all launch together via the same docker-compose up command.

  如果需要，可以使用更多服務擴展docker-compose.yml ：其他數據庫，外部服務等。它們都將通過同一docker-compose up命令一起啟動。

• You’ll be creating the same tables in several test suites, so write a shared function for that, i.e. createUsersTable() and call it inside your beforeEach() block.

  您將在多個測試套件中創建相同的表，因此為此編寫一個共享函數，即createUsersTable()并在beforeEach()塊內調用它。

• You will also be inserting a lot of test data, often focusing on one or more fields. It’s useful to have generic, shared functions for inserting test records, with the ability to override specific fields, like the one below. Then you can call it in your tests with await givenUser({username: "whatever"}) .

  您還將插入大量測試數據，通常側重于一個或多個字段。 具有通用的，插入測試記錄的共享功能，并且具有覆蓋特定字段(如下面的字段)的功能非常有用。 然后，可以在測試中使用await givenUser({username: "whatever"})來調用它。

async function givenUser(overrides = {}) {
    const user = Object.assign({
        id: '1',
        username: 'john_doe',
    }, overrides);
    await clients.postgres.query(`
        INSERT INTO users (id, username)
        VALUES (${user.id}, '${user.username}')
        `);
    return user;
}

• You may need to make your test record IDs unique. Sometimes that’s easy, like with MongoDB, where calling new ObjectId() generates a unique value, or using an integer sequence; sometimes you can use packages like uuid; at other times, you’ll have to write a simple function that generates an ID in the format you require. Here’s one I used, when I needed unique 18-character IDs that were also sortable in the order they were generated:

  您可能需要使測試記錄ID唯一。 有時候，這很容易，例如在MongoDB中，在其中調用new ObjectId()生成一個唯一值或使用整數序列。 有時您可以使用uuid之類的包； 在其他時候，您將必須編寫一個簡單的函數，以所需的格式生成ID。 當需要唯一的18個字符的ID(它們也可以按照生成順序排序)時，這就是我使用的一個：

let lastSuffix = 0;function uniqueId() {
    return new Date().toISOString().replace(/\D/g, '') + (lastSuffix++ % 10);
}

查看樣本存儲庫 (Check out the sample repository)

All of the code quoted above is available in a GitHub repository at @Pragmatists/data-migration-testing. You can pull it, run npm install and, with a bit of luck, it should run out of the box. You can also use it as a base for your own data migration suite. There’s no license attached — it’s public domain.

上面引用的所有代碼均可在@ Pragmatists / data-migration-testing的GitHub存儲庫中找到。 您可以將其拉出，運行npm install ，但幸運的是，它應該立即可用。 您也可以將其用作自己的數據遷移套件的基礎。 沒有附加的許可證-它是公共領域。