spannify

spannify

English | 日本語

An assertion utility for verifying Cloud Spanner test data against JSON expectations.

Installation

npm install spannify

or

pnpm add spannify

Usage Guide

Quick Start

• Start the Spanner emulator and verify the connection settings.

• Create an expectations JSON file:

{
  "tables": {
    "Users": {
      "rows": [
        {
          "UserID": "user-001",
          "Name": "Alice Example",
          "Email": "[e-mail]",
          "Status": 1,
          "CreatedAt": "2024-01-01T00:00:00Z"
        }
      ]
    },
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Name": "Example Product",
          "Price": 1999,
          "IsActive": true,
          "CategoryID": null,
          "CreatedAt": "2024-01-01T00:00:00Z"
        }
      ]
    },
    "Books": {
      "rows": [
        {
          "BookID": "book-001",
          "Title": "Example Book",
          "Author": "Jane Doe",
          "PublishedYear": 2024,
          "JSONData": "{\"genre\":\"Fiction\",\"rating\":4.5}"
        }
      ]
    }
  }
}

Each table lists expected rows as an array. If you want to verify the total number of rows returned, add an optional count field.

• Run the assertion from your script:

import { createSpannerAssert } from "spannify";
import expectations from "./expectations.json" with { type: "json" };

const spannerAssert = createSpannerAssert({
  connection: {
    projectId: "your-project-id",
    instanceId: "your-instance-id",
    databaseId: "your-database",
    emulatorHost: "127.0.0.1:9010",
  },
});

await spannerAssert.assert(expectations);

When It Fails

SpannerAssertionError: 1 expected row(s) not found in table "Users".
  - Expected
  + Actual

  Array [
    Object {
-     "Name": "Alice",
+     "Name": "Invalid Name",
    },
  ]

An error is thrown with a color-coded diff showing expected vs. actual values.

Usage with Playwright

A practical example using spannify in Playwright E2E tests to verify database state after user interactions:

import { test, expect } from "@playwright/test";
import { createSpannerAssert } from "spannify";
import userCreatedExpectations from "./test/expectations/user-created.json" with { type: "json" };
import profileUpdatedExpectations from "./test/expectations/profile-updated.json" with { type: "json" };
import productInventoryExpectations from "./test/expectations/product-inventory.json" with { type: "json" };

test.describe("User Registration Flow", () => {
  let spannerAssert;

  test.beforeAll(async () => {
    spannerAssert = createSpannerAssert({
      connection: {
        projectId: "your-project-id",
        instanceId: "your-instance-id",
        databaseId: "your-database",
        emulatorHost: "127.0.0.1:9010",
      },
    });
  });

  test("should create user record after registration", async ({ page }) => {
    // 1. Perform UI actions
    await page.goto("https://your-app.com/register");
    await page.fill('[name="email"]', "[e-mail]");
    await page.fill('[name="name"]', "Alice Example");
    await page.click('button[type="submit"]');

    await expect(page.locator(".success-message")).toBeVisible();

    // 2. Verify database state
    await spannerAssert.assert(userCreatedExpectations);
  });

  test("should update user profile", async ({ page }) => {
    // Navigate to profile and update
    await page.goto("https://your-app.com/profile");
    await page.fill('[name="bio"]', "Software engineer");
    await page.click('button:has-text("Save")');

    await expect(page.locator(".success-notification")).toBeVisible();

    // Verify the database was updated correctly
    await spannerAssert.assert(profileUpdatedExpectations);
  });

  test("should create product and verify inventory", async ({ page }) => {
    // Admin creates a new product
    await page.goto("https://your-app.com/admin/products");
    await page.fill('[name="productName"]', "Example Product");
    await page.fill('[name="price"]', "1999");
    await page.check('[name="isActive"]');
    await page.click('button:has-text("Create Product")');

    await expect(page.locator(".product-created")).toBeVisible();

    // Verify both Products and Inventory tables
    await spannerAssert.assert(productInventoryExpectations);
  });
});

You can also verify multiple databases by changing the connection information when creating instances.

Example expectations file (test/expectations/user-created.json):

{
  "tables": {
    "Users": {
      "count": 1,
      "rows": [
        {
          "Email": "[e-mail]",
          "Name": "Alice Example",
          "Status": 1
        }
      ]
    }
  }
}

Multiple tables example (test/expectations/product-inventory.json):

{
  "tables": {
    "Products": {
      "count": 1,
      "rows": [
        {
          "Name": "Example Product",
          "Price": 1999,
          "IsActive": true
        }
      ]
    },
    "Inventory": {
      "count": 1,
      "rows": [
        {
          "ProductID": "product-001",
          "Quantity": 0,
          "LastUpdated": "2024-01-01T00:00:00Z"
        }
      ]
    }
  }
}

Supported Value Types

spannify compares column values using string, number, boolean, null, arrays, and JSON types.

Primitive Types:

• string, number, boolean, null
• For Spanner types like TIMESTAMP or DATE, provide values as strings (e.g., "2024-01-01T00:00:00Z")

Array Types (ARRAY Columns):

• Supports ARRAY<STRING>, ARRAY<INT64>, ARRAY<BOOL>
• Arrays are compared with order-independent matching (element order doesn't matter)
• Empty arrays ([]) are supported

Array Example:

{
  "tables": {
    "Articles": {
      "rows": [
        {
          "ArticleID": "article-001",
          "Tags": ["javascript", "typescript", "node"],
          "Scores": [100, 200, 300],
          "Flags": [true, false, true]
        },
        {
          "ArticleID": "article-002",
          "Tags": [],
          "Scores": [],
          "Flags": []
        }
      ]
    }
  }
}

JSON Type:

• Spanner JSON columns are fully supported
• Subset matching: Only specified keys in the expected JSON object are compared (extra keys in actual data are ignored)
• Order-independent arrays: Arrays within JSON values can be in any order
• Nested structures: Unlimited nesting depth is supported

JSON Example:

{
  "tables": {
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Metadata": {
            "category": "electronics",
            "tags": ["laptop", "gaming"],
            "specs": {
              "cpu": "Intel i9",
              "ram": 32
            }
          }
        },
        {
          "ProductID": "product-002",
          "Reviews": [
            { "rating": 5, "comment": "Great!" },
            { "rating": 4, "comment": "Good" }
          ]
        }
      ]
    }
  }
}

JSON Subset Matching Example:

// Expected (subset matching - check specific keys only)
{
  "tables": {
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Metadata": {
            "category": "electronics"
          }
        }
      ]
    }
  }
}

// Actual database (matches even with extra keys)
{
  "ProductID": "product-001",
  "Metadata": {
    "category": "electronics",     ✅ Match
    "tags": ["laptop", "gaming"],   ⬜ Ignored
    "specs": { "cpu": "Intel i9" }  ⬜ Ignored
  }
}

JSON Order-Independent Array Example:

// Expected (arrays in any order)
{
  "tables": {
    "Articles": {
      "rows": [
        {
          "ArticleID": "article-001",
          "Tags": [
            { "id": 3, "name": "TypeScript" },
            { "id": 1, "name": "JavaScript" },
            { "id": 2, "name": "Node.js" }
          ]
        }
      ]
    }
  }
}

// Actual database (matches even with different order)
{
  "ArticleID": "article-001",
  "Tags": [
    { "id": 1, "name": "JavaScript" },  ✅ Match
    { "id": 2, "name": "Node.js" },     ✅ Match
    { "id": 3, "name": "TypeScript" }   ✅ Match
  ]
}