Astro

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

src/lib/uql.ts

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

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

Pages, actions and endpoints use the exported 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

src/pages/posts/index.astro

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

Route caching, stable in Astro 7, is what stops the page above querying on every request. Name a provider once and set the defaults per URL pattern:

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

A page overrides its rule and tags the response with Astro.cache.set({ maxAge: 60, swr: 300, tags: ['posts'] }), so a write drops only what it touched:

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

memoryCache() from astro/config caches only inside the instance that served the request, so on functions it barely reduces queries; the adapter’s own provider does (cacheVercel(), cacheNetlify(), cacheCloudflare()). Either way the key is the URL and not your tenant context, so per-user data belongs in a server island or behind Astro.cache.set(false), security filter or no.

Server islands

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

src/components/RecentOrders.astro

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

src/pages/account.astro

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

Actions

The examples below write comments, so src/lib/models.ts exports one alongside Post:

src/lib/models.ts

import { Entity, Field, Id, ManyToOne } from 'uql-orm';

@Entity()
export class Comment {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) body?: string | null;
  @Field({ references: () => Post }) postId?: number | null;
  @Field({ type: Number }) authorId?: number | null;
  @ManyToOne({ entity: () => Post, references: (comment) => comment.postId })
  post?: Post;
}

src/actions/index.ts

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.coerce.number(), 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

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

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

export const prerender = false;

const handler = createFetchHandler({
  pool,
  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 takes the same handler, if you would rather own the request pipeline than sit in the route table:

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 { pool } from './lib/uql';
import { Comment, Post } from './lib/models';

const uql = 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 }, () => uql(request))
      : uql(request);
  },
} satisfies Fetchable;

astro(state) is everything Astro would have done: middleware, actions, caching, sessions, pages. Answering ahead of it skips all of that, src/middleware.ts included, which is why the tenant context is set here by hand.

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

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

Astro 7 removed @astrojs/db, and its upgrade guide sends you to node:sqlite, Drizzle or a hosted database. UQL covers those without a different query layer per database: Turso or libSQL, a local SQLite file, Postgres, or PGlite for Postgres itself with no server to run. Only the pool changes.