allow PgTestClient to seed from db or pg

[pyramation]

Seeding API Extension for PgTestClient

Task Overview

Extend PgTestClient with seeding capabilities to provide developers with intuitive options for loading test data. The implementation should support CSV, JSON, SQL files, and LaunchQL deployments while maintaining simplicity and avoiding unnecessary abstraction.

Core Requirements

Supported Seeding Methods

1. JSON Seeding - Load data from JavaScript objects
2. CSV Seeding - Load data from CSV files using PostgreSQL COPY
3. SQL Files - Execute SQL files for schema and data setup
4. LaunchQL - Deploy LaunchQL packages for full schema management

Explicitly Excluded Methods

• NO sqitch() - Sqitch integration is not part of the instance API
• NO run() - Users can create closure functions themselves
• NO seeder() - Users can create closure functions themselves

API Design Principles

1. Simplicity First - "JUST A CLASS"

The API must be simple and direct. No meta-programming, no runtime attachment magic, no confusing abstractions.

Bad (Rejected):

// Meta-programming with attachSeedAPI
attachSeedAPI(client, context);
client.seed.json(data);

// Confusing options
pg.seed.json(data, { client: 'pg', publish: true });

Good (Implemented):

// Direct class methods
await pg.loadJson(data);
await pg.publish(); // Explicit when needed

2. No Confusing Options

Methods execute on the client they're called on. No client or publish options.

Constraint: Each method operates on its own client instance. Users call publish() explicitly when they need cross-connection visibility.

3. Backward Compatibility

While the new API uses direct class methods, a backward-compatible shim exists for external packages that still use the old adapter pattern:

// Old pattern (still supported for external packages)
import { seed } from 'pgsql-test';
await getConnections({}, [seed.sqlfile(['schema.sql'])]);

// New pattern (primary API)
const { pg } = await getConnections({}, false);
await pg.loadSql(['schema.sql']);

Implementation Architecture

Direct Class Methods on PgTestClient

The seeding capabilities are implemented as direct methods on the PgTestClient class:

class PgTestClient {
  // Direct seeding methods
  async loadJson(data: JsonSeedMap): Promise<void>
  async loadCsv(map: CsvSeedMap): Promise<void>
  async loadSql(files: string[]): Promise<void>
  async loadLaunchql(cwd?: string, cache?: boolean): Promise<void>
}

we should update contextSettings to be of type PgTextClientContext:

export class PgTestClient {
  ...
  private contextSettings: Record<string, string | null> = {};
...

export class PgTestClient {
  ...
  private contextSettings: PgTextClientContext = {};
...

Standalone Helper Functions

To avoid circular dependencies, the actual seeding logic is implemented as standalone functions that accept minimal primitives:

also each function should handle the way we insert context, and the creation of the ctxStmts string could use the same funciton that creates the statements to set the context. Since some functions may do things differently than others, like potentially using streams or psql, we need to include the statments as needed.

// src/seed/json.ts
export async function insertJson(
  client: Client,
  context: PgTextClientContext,
  data: JsonSeedMap
): Promise<void>

// src/seed/csv.ts
export async function loadCsvMap(
  client: Client,
  context: PgTextClientContext,
  tables: CsvSeedMap
): Promise<void>

Key Design Decision: Helpers use type-only imports to avoid circular dependencies:

import type { PgTestClient } from '../test-client';

Architectural Decisions & Constraints

1. COPY Command Transaction Isolation

Question: Should PgTextClientContext be used to set the context for the COPY commands?

Answer: Yes, it should be. The PgTextClientContext is used to set the context for the COPY commands, ensuring proper transaction isolation and context application (RLS policies, search_path, etc.).

Test Structure Guidelines

Simplified Test Patterns

Tests should use one client per test body to avoid confusion about transaction boundaries:

Bad (Too Complex):

it('test', async () => {
  await pg.loadJson(data);
  await pg.publish();
  const result = await db.any('SELECT * FROM users');
  // Mixing pg and db in same test body
});

Good (Simple):

describe('with pg client', () => {
  it('seeds data', async () => {
    await pg.loadJson(data);
    const result = await pg.any('SELECT * FROM users');
  });
});

describe('with db client', () => {
  it('respects RLS', async () => {
    await db.loadJson(data);
    const result = await db.any('SELECT * FROM users');
  });
});

Cross-Connection Visibility Pattern

When testing RLS or cross-connection scenarios:

beforeEach(async () => {
  // Seed with pg (admin) and publish
  await pg.loadJson(data);
  await pg.publish();
});

it('db client sees published data', async () => {
  const result = await db.any('SELECT * FROM users');
  expect(result).toHaveLength(2);
});

Usage Examples

JSON Seeding

// Load data with pg client (admin)
await pg.loadJson({
  'custom.users': [
    { id: 1, name: 'Alice', email: 'alice@example.com' },
    { id: 2, name: 'Bob', email: 'bob@example.com' }
  ]
});

// Load data with db client (app user)
await db.loadJson({
  'custom.posts': [
    { id: 1, user_id: 1, title: 'First Post' }
  ]
});

CSV Seeding

// Load CSV files
await pg.loadCsv({
  'custom.users': '/path/to/users.csv',
  'custom.posts': '/path/to/posts.csv'
});

SQL Files

// Run SQL files
await pg.loadSql([
  '/path/to/schema.sql',
  '/path/to/seed-data.sql'
]);

LaunchQL Deployment

// Deploy LaunchQL package
await pg.loadLaunchql('/path/to/package', true); // with cache

Cross-Connection Visibility

// Seed with pg and make visible to db
await pg.loadJson({ 'custom.users': [{ id: 1, name: 'Alice' }] });
await pg.publish();

// Now db can see the data
const users = await db.any('SELECT * FROM custom.users');