React Router

In framework mode, loaders and actions run on the server, so they query the pool directly. Written for React Router 8; on v7, middleware needs its future flag. Data mode and SPA mode have no server, so there you mount the HTTP handler wherever your API runs and call it with the browser client.

Where the pool lives

The .server.ts suffix keeps this module out of the client bundle:

app/db.server.ts

import { PgQuerierPool } from 'uql-orm/postgres';
import './models';

export const pool = new PgQuerierPool({
  connectionString: process.env.DATABASE_URL,
});

Loaders and actions

loaderData is typed from what the loader returns, each post with a typed author:

app/routes/posts.tsx

import { pool } from '~/db.server';
import { Post } from '~/models';
import type { Route } from './+types/posts';

export async function loader() {
  return {
    posts: await pool.findMany(Post, {
      $select: { id: true, title: true },
      $populate: { author: { $select: { name: true } } },
      $where: { published: true },
      $limit: 20,
    }),
  };
}

export default function Posts({ loaderData }: Route.ComponentProps) {
  return (
    <ul>
      {loaderData.posts.map((post) => (
        <li key={post.id}>
          {post.title} - {post.author.name}
        </li>
      ))}
    </ul>
  );
}

An action is a public endpoint, so validate its input and build the query on the server. Several writes go in pool.transaction.

app/routes/posts.new.tsx

import { redirect } from 'react-router';
import { z } from 'zod';
import { pool } from '~/db.server';
import { Post } from '~/models';
import type { Route } from './+types/posts.new';

const NewPost = z.object({ title: z.string().trim().min(1) });

export async function action({ request }: Route.ActionArgs) {
  const form = NewPost.safeParse(Object.fromEntries(await request.formData()));
  if (!form.success) return { errors: z.flattenError(form.error).fieldErrors };
  return redirect(`/posts/${await pool.insertOne(Post, form.data)}`);
}

Auto-generated CRUD

Point a splat route, route('api/uql/*', 'routes/uql.ts'), at a resource route that forwards to the HTTP handler:

app/routes/uql.ts

import { createFetchHandler } from 'uql-orm/http';
import { pool } from '~/db.server';
import { Post, User } from '~/models';
import type { Route } from './+types/uql';

const handler = createFetchHandler({
  pool,
  include: [Post, User],
  basePath: '/api/uql',
});

export const loader = ({ request }: Route.LoaderArgs) => handler(request);
export const action = ({ request }: Route.ActionArgs) => handler(request);

loader serves GET and HEAD, and action the writes. QUERY reaches neither, so keep the browser client on GET.

Multi-tenancy

Middleware on the root route wraps every loader, action and resource route below it, so set the context there. A security filter then scopes every query. See Multi-tenancy.

app/root.tsx

import { withContext } from 'uql-orm';

export const middleware: Route.MiddlewareFunction[] = [
  async ({ request }, next) => {
    const session = await getSession(request.headers.get('cookie'));
    return session
      ? withContext({ tenantId: session.tenantId }, () => next())
      : next();
  },
];