Integration testing tRPC and in memory MySQL database.

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...useQuery()

// ...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
});

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' }]);

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 };
}

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 };

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

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

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

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

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 />);

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();
// await awaiter();

Related Posts

How to Create Security Tokens on a Zero Budget

May 3, 2025  0

DOJ’s sweeping remedies would harm America’s economy an...

Apr 29, 2025  0

Smarter insights, stronger teams: New features for Stac...

Apr 26, 2025  0