Astro

Astro Recipe

A page can query the database in its frontmatter with no API layer in between. Nothing to install beyond uql-orm. Verified against Astro 7.

Database access needs on-demand rendering: add an adapter, then set output: 'server' or opt routes in with export const prerender = false.

Where the pool lives

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

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

setQuerierPool(pool);

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

Pages

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

export const prerender = false;

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

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

Caching

Astro 7’s route caching stops a page querying on every request. Configure a provider once, set defaults per URL pattern, and tag responses so a write invalidates only what it touched:

import { defineConfig, memoryCache } from 'astro/config';

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

---
const posts = await pool.findMany(Post, { $where: { published: true }, $limit: 20 });
Astro.cache.set({ maxAge: 60, swr: 300, tags: ['posts'] });
---

await pool.updateOneById(Post, id, { published: true });
await context.cache.invalidate({ tags: ['posts'] });

Cache only what is the same for everybody. 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) even with a security filter in play.

Server islands

Per-user data on a cached page: defer the component and it renders in its own request.

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

const { user } = Astro.locals;
const orders = user
  ? await pool.findMany(Order, { $where: { customerId: user.id }, $sort: { createdAt: 'desc' }, $limit: 5 })
  : [];
---

<ul>{orders.map((order) => <li>{order.reference}</li>)}</ul>

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

Actions

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

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

An action is a public endpoint: build the query from validated input, never from a raw Query<Comment>. Several writes go in pool.transaction.

Auto-generated CRUD

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

export const prerender = false;

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

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

Astro does not strip the prefix, hence basePath. ALL catches every method, so the QUERY transport works too, and the endpoints are consumable with HttpQuerier. Astro 7’s src/fetch.ts entrypoint expects the same fetch(request) shape if you would rather mount it outside the route table.

Multi-tenancy

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

Astro middleware wraps next(), so pages, islands, actions and the API endpoint are all scoped by the same security filter. See Multi-tenancy.