Astro

An Astro page can query the database in its frontmatter, with no API in between. Written for Astro 7. Database access needs on-demand rendering: an adapter, plus output: 'server' or export const prerender = false per route.

Where the pool lives

src/lib/uql.ts

import { DATABASE_URL } from 'astro:env/server';
import { PgQuerierPool } from 'uql-orm/postgres';

export const pool = new PgQuerierPool({ connectionString: DATABASE_URL });

Declare DATABASE_URL in env.schema as envField.string({ context: 'server', access: 'secret' }), so it stays off the client and a missing value fails the build.

If you are moving off @astrojs/db, which Astro 7 removed, the databases its upgrade guide suggests all work here: Turso or libSQL, SQLite, Postgres or PGlite. Only the pool changes.

Pages

src/pages/posts/index.astro

---
import { pool } from '../../lib/uql';
import { Post } from '../../lib/models';

const posts = await pool.findMany(Post, {
  $select: { id: true, title: true },
  $populate: { author: { $select: { name: true } } },
  $where: { published: true },
  $limit: 20,
});
---

<ul>
  {posts.map((post) => (
    <li>
      {post.title} - {post.author.name}
    </li>
  ))}
</ul>

Caching

Route caching stops that page from querying on every request. Pick your adapter’s provider and set rules per URL:

astro.config.ts

import { cacheVercel } from '@astrojs/vercel/cache';
import { defineConfig } from 'astro/config';

export default defineConfig({
  cache: { provider: cacheVercel() },
  routeRules: { '/posts': { maxAge: 60, swr: 300 } },
});

Tag a page with Astro.cache.set({ tags: ['posts'] }), and after a write call context.cache.invalidate({ tags: ['posts'] }) to drop only what changed. memoryCache() caches inside one instance only, so on serverless it saves few queries.

The cache key is the URL, not your tenant context, so per-user data belongs in a server island or behind Astro.cache.set(false). Defer the component and it renders in its own request:

src/pages/account.astro

<RecentOrders server:defer>
  <p slot="fallback">Loading your orders...</p>
</RecentOrders>

Actions

An action is a public endpoint, so build the query from its validated input, never from a client’s Query. Several writes go in pool.transaction.

src/actions/index.ts

import { z } from 'astro/zod';
import { ActionError, defineAction } from 'astro:actions';
import { Comment } from '../lib/models';
import { pool } from '../lib/uql';

export const server = {
  addComment: defineAction({
    accept: 'form',
    input: z.object({ postId: z.coerce.number(), body: z.string().min(1) }),
    handler: async ({ postId, body }, context) => {
      const user = context.locals.user;
      if (!user) throw new ActionError({ code: 'UNAUTHORIZED' });
      const id = await pool.insertOne(Comment, {
        postId,
        body,
        authorId: user.id,
      });
      return pool.findOneById(Comment, id);
    },
  }),
};

Auto-generated CRUD

Mount the HTTP handler in a catch-all endpoint, with the prefix as basePath. ALL catches every method, QUERY included:

src/pages/api/uql/[...uql].ts

import type { APIRoute } from 'astro';
import { createFetchHandler } from 'uql-orm/http';
import { Comment, Post } from '../../../lib/models';
import { pool } from '../../../lib/uql';

export const prerender = false;

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

export const ALL: APIRoute = ({ request }) => handler(request);

You can also answer from src/fetch.ts, before Astro’s pipeline runs. That skips middleware, so set the tenant context there yourself:

src/fetch.ts

import type { Fetchable } from 'astro';
import { astro, FetchState } from 'astro/fetch';
import { withContext } from 'uql-orm';
import { createFetchHandler } from 'uql-orm/http';
import { Comment, Post } from './lib/models';
import { pool } from './lib/uql';

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

export default {
  async fetch(request: Request) {
    const state = new FetchState(request);
    if (!state.pathname.startsWith('/api/uql')) return astro(state);
    const session = await getSession(state.cookies);
    return session
      ? withContext({ tenantId: session.tenantId }, () => handler(request))
      : handler(request);
  },
} satisfies Fetchable;

Multi-tenancy

Astro middleware wraps next(), so setting the context there covers pages, islands, actions and the API endpoint. A security filter then scopes their queries. See Multi-tenancy.

src/middleware.ts

import { defineMiddleware } from 'astro:middleware';
import { withContext } from 'uql-orm';

export const onRequest = defineMiddleware(async (context, next) => {
  const session = await getSession(context.cookies);
  context.locals.user = session?.user;
  return session
    ? withContext({ tenantId: session.tenantId }, () => next())
    : next();
});