Database Testing

Xata Database Testing Guide: Testing Serverless Postgres + Search

HelpMeTest

HelpMeTest

— 6 min read
Xata Database Testing Guide: Testing Serverless Postgres + Search

Xata is a serverless database platform built on PostgreSQL with a TypeScript SDK, full-text search, branch-based development workflows, and an HTTP API. Its branch feature — where every branch gets an isolated database — makes it particularly interesting for testing: you can create a branch per pull request and get real database isolation without Docker.

This guide covers how to test Xata-backed applications using branch-based isolation, SDK mocking, and direct database testing patterns.

Understanding Xata's Testing Advantage: Branches

The key concept in Xata testing is branches. Unlike traditional databases, Xata lets you create a branch of your database schema and data in seconds. Each branch is fully isolated from the main branch. This means:

  • Development branches during feature work
  • A test branch that CI creates fresh for each run
  • PR-level isolation with no Docker setup needed
# Create a test branch
xata branch create <span class="hljs-built_in">test --from main

<span class="hljs-comment"># After testing, delete it
xata branch delete <span class="hljs-built_in">test

Project Setup

Install the Xata SDK and CLI:

npm install @xata.io/client
npm install -D @xata.io/cli
npx xata auth login

Initialize Xata in your project:

npx xata init

This generates a typed client based on your schema — critical for type-safe queries.

Unit Testing: Mocking the Xata SDK

The generated Xata client is a class you can mock directly. Here's a minimal mock factory:

// tests/mocks/xata.ts
import { XataClient } from '../src/xata'; // generated client

type MockXataClient = {
  [K in keyof XataClient]: jest.Mocked<XataClient[K]>;
};

export function createMockXataClient(): MockXataClient {
  return {
    db: {
      posts: {
        getAll: jest.fn(),
        getFirst: jest.fn(),
        getMany: jest.fn(),
        filter: jest.fn().mockReturnThis(),
        sort: jest.fn().mockReturnThis(),
        select: jest.fn().mockReturnThis(),
        create: jest.fn(),
        update: jest.fn(),
        delete: jest.fn(),
        search: jest.fn(),
      },
      users: {
        getAll: jest.fn(),
        getFirst: jest.fn(),
        getMany: jest.fn(),
        filter: jest.fn().mockReturnThis(),
        create: jest.fn(),
        update: jest.fn(),
        delete: jest.fn(),
      },
    },
  } as unknown as MockXataClient;
}

Testing a Repository Layer

// src/repositories/PostRepository.ts
import { XataClient } from '../xata';

export class PostRepository {
  constructor(private xata: XataClient) {}

  async getPublishedPosts(limit: number = 10) {
    return this.xata.db.posts
      .filter({ status: 'published' })
      .sort('publishedAt', 'desc')
      .getMany({ pagination: { size: limit } });
  }

  async searchPosts(query: string) {
    return this.xata.db.posts.search(query, {
      fuzziness: 1,
      highlight: { enabled: true },
    });
  }

  async createPost(data: {
    title: string;
    content: string;
    authorId: string;
  }) {
    return this.xata.db.posts.create({
      ...data,
      status: 'draft',
      publishedAt: null,
    });
  }

  async publishPost(id: string) {
    return this.xata.db.posts.update(id, {
      status: 'published',
      publishedAt: new Date().toISOString(),
    });
  }
}

Testing this repository:

// src/repositories/PostRepository.test.ts
import { PostRepository } from './PostRepository';
import { createMockXataClient } from '../../tests/mocks/xata';

describe('PostRepository', () => {
  let repo: PostRepository;
  let mockXata: ReturnType<typeof createMockXataClient>;

  beforeEach(() => {
    mockXata = createMockXataClient();
    repo = new PostRepository(mockXata as any);
  });

  describe('getPublishedPosts', () => {
    it('filters by published status', async () => {
      const mockPosts = [
        { id: 'post-1', title: 'First', status: 'published' },
        { id: 'post-2', title: 'Second', status: 'published' },
      ];

      // Xata uses a fluent chain — mock the final call
      mockXata.db.posts.getMany.mockResolvedValue(mockPosts as any);

      const posts = await repo.getPublishedPosts();

      expect(mockXata.db.posts.filter).toHaveBeenCalledWith({
        status: 'published',
      });
      expect(mockXata.db.posts.sort).toHaveBeenCalledWith('publishedAt', 'desc');
      expect(posts).toHaveLength(2);
    });

    it('applies pagination limit', async () => {
      mockXata.db.posts.getMany.mockResolvedValue([] as any);

      await repo.getPublishedPosts(5);

      expect(mockXata.db.posts.getMany).toHaveBeenCalledWith({
        pagination: { size: 5 },
      });
    });
  });

  describe('searchPosts', () => {
    it('calls search with fuzziness enabled', async () => {
      mockXata.db.posts.search.mockResolvedValue({
        records: [],
        totalCount: 0,
      } as any);

      await repo.searchPosts('testing guide');

      expect(mockXata.db.posts.search).toHaveBeenCalledWith('testing guide', {
        fuzziness: 1,
        highlight: { enabled: true },
      });
    });
  });

  describe('publishPost', () => {
    it('sets status to published and sets publishedAt', async () => {
      const now = new Date();
      jest.useFakeTimers().setSystemTime(now);

      mockXata.db.posts.update.mockResolvedValue({
        id: 'post-1',
        status: 'published',
        publishedAt: now.toISOString(),
      } as any);

      await repo.publishPost('post-1');

      expect(mockXata.db.posts.update).toHaveBeenCalledWith(
        'post-1',
        expect.objectContaining({
          status: 'published',
          publishedAt: now.toISOString(),
        }),
      );

      jest.useRealTimers();
    });
  });
});

Integration Testing with Xata Branches

The best integration testing approach for Xata uses a dedicated test branch. Create it once, run tests against it, clean up records between test suites.

Environment Setup

# .env.test
XATA_API_KEY=xau_...
XATA_BRANCH=<span class="hljs-built_in">test
XATA_DATABASE_URL=https://yourworkspace.xata.sh/db/yourdb

Creating the Test Branch

Add a script to your package.json:

{
  "scripts": {
    "test:branch:create": "xata branch create test --from main",
    "test:branch:delete": "xata branch delete test",
    "test:integration": "XATA_BRANCH=test vitest run tests/integration"
  }
}

Integration Test Suite

// tests/integration/posts.integration.test.ts
import { getXataClient } from '../../src/xata';
import { PostRepository } from '../../src/repositories/PostRepository';

// This test uses the real Xata `test` branch
const xata = getXataClient();
const repo = new PostRepository(xata);

async function cleanupPosts() {
  const all = await xata.db.posts.getAll();
  await Promise.all(all.map((p) => xata.db.posts.delete(p.id)));
}

describe('PostRepository (integration, Xata test branch)', () => {
  beforeEach(cleanupPosts);
  afterAll(cleanupPosts);

  it('creates and retrieves a post', async () => {
    await repo.createPost({
      title: 'Integration Test Post',
      content: 'Real content',
      authorId: 'user-1',
    });

    const posts = await xata.db.posts
      .filter({ title: 'Integration Test Post' })
      .getFirst();

    expect(posts).not.toBeNull();
    expect(posts?.status).toBe('draft');
  });

  it('filters only published posts', async () => {
    // Create one draft and one published
    const draft = await repo.createPost({
      title: 'Draft Post',
      content: '',
      authorId: 'u1',
    });
    const pub = await repo.createPost({
      title: 'Published Post',
      content: '',
      authorId: 'u1',
    });
    await repo.publishPost(pub.id);

    const published = await repo.getPublishedPosts(10);

    expect(published.some((p) => p.id === draft.id)).toBe(false);
    expect(published.some((p) => p.id === pub.id)).toBe(true);
  });

  it('searches posts with fuzzy matching', async () => {
    await xata.db.posts.create({
      title: 'Playwright E2E Testing Guide',
      content: 'Complete guide to end-to-end testing with Playwright.',
      status: 'published',
      authorId: 'u1',
    });

    const results = await repo.searchPosts('Playright'); // intentional typo

    // Fuzziness=1 should catch one-character difference
    expect(results.records.length).toBeGreaterThan(0);
    expect(results.records[0].title).toContain('Playwright');
  });
});

Testing Xata's Full-Text Search

Xata's search is built on top of Elasticsearch and supports fuzzy matching, boosting, and highlights. Unit test the query construction and integration test the actual search results:

// Testing search ranking
it('boosts title matches over content matches', async () => {
  await xata.db.posts.create({
    title: 'Playwright Guide',
    content: 'An introduction.',
    status: 'published',
    authorId: 'u1',
  });
  await xata.db.posts.create({
    title: 'E2E Testing Best Practices',
    content: 'Playwright is the best tool for this.',
    status: 'published',
    authorId: 'u1',
  });

  const results = await xata.db.posts.search('Playwright', {
    boosters: [{ numericBooster: { column: 'id', factor: 1 } }],
  });

  // Title match should rank first
  expect(results.records[0].title).toBe('Playwright Guide');
});

CI Configuration with Branch Creation

# .github/workflows/integration.yml
name: Integration Tests

on:
  pull_request:

jobs:
  test:
    runs-on: ubuntu-latest

    env:
      XATA_API_KEY: ${{ secrets.XATA_API_KEY }}
      XATA_DATABASE_URL: ${{ secrets.XATA_DATABASE_URL }}

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
      - run: npm ci

      # Create a PR-specific branch for full isolation
      - name: Create test branch
        run: npx xata branch create ci-${{ github.run_id }} --from main
        env:
          XATA_BRANCH: main

      - name: Run integration tests
        run: npm run test:integration
        env:
          XATA_BRANCH: ci-${{ github.run_id }}

      - name: Delete test branch
        if: always()
        run: npx xata branch delete ci-${{ github.run_id }} --force
        env:
          XATA_BRANCH: main

This gives every CI run a fully isolated database with no Docker setup, no port conflicts, and no test data interference.

Testing TypeScript Type Safety

One of Xata's strengths is its generated TypeScript types. You can write type-level tests to ensure your schema stays in sync:

// tests/types/schema.test-d.ts
import { expectType } from 'tsd';
import type { PostsRecord } from '../../src/xata';

// Verify the schema has the fields your code expects
expectType<string>({} as PostsRecord['title']);
expectType<'draft' | 'published' | 'archived'>({} as PostsRecord['status']);
expectType<string | null>({} as PostsRecord['publishedAt']);

Run with npx tsd in your test pipeline.

Key Patterns

Use branches for isolation, not Docker. Xata's branch feature eliminates the need for a local database for integration tests. One xata branch create ci-$RUN_ID call gives you a real, isolated PostgreSQL database.

Mock the fluent chain carefully. Xata's SDK uses method chaining (filter().sort().getMany()). When mocking, return this from intermediate methods and mock the terminal method (getMany, getFirst, getAll) to return data.

Don't test Xata's internals. Don't write integration tests that verify Xata correctly stores data — that's Xata's responsibility. Test that your repository methods construct the right queries and return data in the format your application expects.

Keep branch cleanup in always() blocks. In CI, always delete test branches even if tests fail. A growing list of orphaned branches wastes quota and money.

For ongoing monitoring of your Xata-backed application, HelpMeTest runs automated tests on a schedule — detecting when schema changes break queries before your users encounter errors.

Read more