Integration testing tRPC and in memory MySQL database.

Pietro Palmesi — Fri, 09 May 2025 13:09:52 +0000

Integration Testing with next.js tRPC, kysely and MySQL

Integration testing ensures that your backend API, database, and frontend work together as expected. In a Next.js + tRPC + Kysely/Prisma project, you can test the flow from database to API to UI by mocking or spinning up real services in-memory.

This guide covers:

Mocking tRPC hooks in frontend tests
Mocking the database layer for backend tests
Using mysql-memory-server for in-memory MySQL
Alternatives: pg-mem (PostgreSQL), in-memory SQLite
Automatically calculating tRPC procedure paths for integration tests

1. Mocking tRPC in Frontend Tests

When testing React components that use tRPC hooks, you often want to control the responses returned by the API without running a real backend.

Approach:

Use your test runner's mocking utilities (e.g., Vitest, Jest) to replace the trpc hooks with mock implementations.
Return desired loading, error, or data states for each test.

Example:

// ...in your test file
import { trpc } from 'utils/trpc';
import { vi } from 'vitest';

vi.mock('utils/trpc', () => ({
  trpc: {
    user: {
      listUsers: {
        useQuery: vi.fn(),
      },
    },
  },
}));

it('shows loading', () => {
  vi.mocked(trpc.user.listUsers.useQuery).mockReturnValue({ isPending: true, error: null, data: undefined });
  // ...render and assert
});

This lets you simulate any API state in your component tests.

2. Mocking the Database Layer

For backend integration tests (e.g., testing tRPC routers), you can mock the database service classes (e.g., MysqlService) to return fixed data.

Approach:

Create a mock DB service object with methods that return promises of test data.
Provide this mock service via the tRPC context (ctx) in your router tests.

Example:

const mockDb = {
  user: {
    listUsers: vi.fn().mockResolvedValue([{ id: '1', name: 'Test', email: 'test@example.com' }]),
  },
};

const mockCtx = {
  user: { email: 'test@example.com' },
  getDb: () => mockDb,
};

// Call the tRPC procedure directly
const result = await userRouter.listUsers.resolve({ ctx: mockCtx, input: undefined });
expect(result).toEqual([{ id: '1', name: 'Test', email: 'test@example.com' }]);

3. Using `mysql-memory-server` for In-Memory MySQL

For full integration tests, you may want to run real database queries against a temporary, in-memory MySQL instance. mysql-memory-server makes this easy.

Setup:

Start an ephemeral MySQL server in your test setup.
Run your migrations to create tables.
Use Kysely or Prisma to connect and run queries as usual.

Example Helper:

import { createDB } from 'mysql-memory-server';
import { createPool } from 'mysql2';
import { Kysely, MysqlDialect } from 'kysely';

export async function getEphemeralDb() {
  const db = await createDB({ version: '8.0.x' });
  const pool = createPool({
    host: 'localhost',
    user: db.username,
    password: '',
    port: db.port,
    database: db.dbName,
  });
  const kysely = new Kysely({ dialect: new MysqlDialect({ pool }) });
  // Run migrations here...
  return { db, pool, kysely };
}

Usage in Tests:

const { kysely } = await getEphemeralDb();
// Insert test data
await kysely.insertInto('User').values({ id: '1', ... }).execute();
// Use in your backend context
const getDb = () => new MysqlService(kysely);
const mockCtx = { user: {...}, getDb };

This approach tests your real DB queries, migrations, and API logic together.

4. Alternatives: PostgreSQL and SQLite In-Memory

If your app uses PostgreSQL or SQLite, or you want faster/lighter tests, you can use:

pg-mem: An in-memory PostgreSQL implementation for Node.js. Great for testing Kysely/Prisma code that targets Postgres.
SQLite in-memory: Most SQLite drivers support :memory: as a database path for ephemeral databases.

Example (pg-mem):

import { newDb } from 'pg-mem';
const db = newDb();
const client = db.adapters.createPgPromise();

Example (SQLite):

import Database from 'better-sqlite3';
const db = new Database(':memory:');

Note: Adjust your Kysely/Prisma config to use the appropriate dialect and connection for your test environment.

5. Automatically Calculating tRPC Procedure Paths for Integration Tests

When writing integration tests for React components that use tRPC hooks (e.g., trpc.user.listUsers.useQuery()), you often need to specify the procedure path (like ['user', 'listUsers']) to mock or resolve data. Manually specifying these paths can be error-prone and repetitive.

Automatic Path Calculation:

You can programmatically extract the tRPC procedure path from the code by analyzing the usage of the hook in your component. For example, if your component calls:

const { data } = trpc.user.listUsers.useQuery();

The corresponding procedure path is ['user', 'listUsers'].

Implementation in Integration Tests:

To automate this, you can:

Conventionally Derive the Path:

For each trpc.<router>.<procedure>.useQuery() call, the path is ['<router>', '<procedure>'].
If you use code generation or TypeScript helpers, you can statically analyze or infer these paths.

Use in Test Helpers:
- Pass the derived path to your test helper (e.g., createTrpcMockFromHelpersProxy) to set up mocks or resolve pending fetches.

Example:

Suppose your component contains:

const { data, isPending } = trpc.user.listUsers.useQuery();

In your test, you can set up the integration mock as follows:

import { createTrpcMockFromHelpersProxy } from './helpers.test';

const mockCtx = { /* ... */ };
const awaiter = createTrpcMockFromHelpersProxy(mockCtx);

// Render your component (which calls trpc.user.listUsers.useQuery())
render(<YourComponent />);

// At this point, the hook is pending (loading state).
// To resolve the pending fetch, call:
await awaiter();

// Now, the component will re-render with the fetched data.
render(<YourComponent />);

How createTrpcMockFromHelpersProxy Works:

It intercepts tRPC hook calls (like trpc.user.listUsers.useQuery()).
It records the procedure path and arguments.
When you call the returned awaiter() function, it automatically:
- Looks up the correct procedure in the tRPC server helpers using the recorded path.
- Calls .fetch() on the helper to get real data (from your test DB or mock context).
- Resolves the pending fetch, causing your component to re-render with the data.

No Manual Path Needed:

Because the proxy records the path and arguments at runtime, you do not need to manually specify the procedure path in your test. Just render your component and call the awaiter.

Summary of Steps:

Render your component that uses tRPC hooks.
Use createTrpcMockFromHelpersProxy(mockCtx) to set up the proxy and get the awaiter.
Call the awaiter after rendering to resolve all pending tRPC queries.
Assert on the rendered output.

Helper file:

import { appRouter } from 'src/server/routers/app';
import { createTRPCReact } from '@trpc/react-query';
import { inferRouterInputs, inferRouterOutputs } from '@trpc/server';
import { QueryClient } from '@tanstack/react-query';

// Types for tRPC
type AppRouter = typeof appRouter;
type RouterInputs = inferRouterInputs<AppRouter>;
type RouterOutputs = inferRouterOutputs<AppRouter>;

// This will store all intercepted queries
const pendingQueries: {
  path: string[];
  args: any[];
  resolve: (data: any) => void;
}[] = [];

// Proxy for trpc hooks
export function createTrpcMockFromHelpersProxy(mockCtx: any) {
  // Create tRPC helpers for server-side resolution
  const helpers = appRouter.createCaller(mockCtx);

  // Patch the global trpc object (or provide a mock)
  // This assumes your test setup allows you to inject this proxy
  // Example: vi.mock('utils/trpc', ...)

  // Recursive proxy to capture path and hook calls
  function makeProxy(path: string[] = []) {
    return new Proxy(
      {},
      {
        get(_, prop: string) {
          if (prop === 'useQuery') {
            // Return a mock hook
            return (...args: any[]) => {
              let data: any = undefined;
              let isPending = true;
              let error: any = null;

              // Store for later resolution
              pendingQueries.push({
                path,
                args,
                resolve: (result: any) => {
                  data = result;
                  isPending = false;
                },
              });

              // Return the shape expected by React Query
              return { data, isPending, error };
            };
          }
          // Support chaining: trpc.user.listUsers
          return makeProxy([...path, prop]);
        },
      },
    );
  }

  // The awaiter resolves all pending queries using the helpers
  async function awaiter() {
    for (const q of pendingQueries) {
      // Traverse helpers to the correct procedure
      let proc: any = helpers;
      for (const segment of q.path) {
        proc = proc[segment];
      }
      // Call the procedure with args and resolve
      const result = await proc(...q.args);
      q.resolve(result);
    }
    // Clear for next test
    pendingQueries.length = 0;
  }

  // Return the proxy and the awaiter
  return awaiter;
}

// Usage in your test:
// const awaiter = createTrpcMockFromHelpersProxy(mockCtx);
// render(<YourComponent />);
// await awaiter();

Summary

Mock tRPC hooks for frontend tests to control API responses.
Mock the DB service for backend router tests to isolate logic.
Use mysql-memory-server for true integration tests with MySQL.
Consider pg-mem or in-memory SQLite for faster, dependency-free tests.
Automate tRPC procedure path calculation for seamless integration testing.

Choose the approach that best fits your test goals: speed, realism, or isolation.

DEV Community: Pietro Palmesi

Integration testing tRPC and in memory MySQL database.

Integration Testing with next.js tRPC, kysely and MySQL

1. Mocking tRPC in Frontend Tests

2. Mocking the Database Layer

3. Using mysql-memory-server for In-Memory MySQL

4. Alternatives: PostgreSQL and SQLite In-Memory

5. Automatically Calculating tRPC Procedure Paths for Integration Tests

Summary

3. Using `mysql-memory-server` for In-Memory MySQL