Querying Content | nextjs-cms

Read CMS content from your public website with the Drizzle schema nextjs-cms generates, or with plain SQL.

nextjs-cms stores content in ordinary MySQL tables. Your website (any framework, any language) reads them directly. There's no content API to call and no SDK to install.

The generated Drizzle schema

Every update-sections run writes a Drizzle schema describing every section table, translation table, gallery, join table, and lookup table:

dynamic-schemas/schema.ts (generated)

import { mysqlTable, int, varchar, longtext, datetime, unique } from 'drizzle-orm/mysql-core'

export const PostsTable = mysqlTable('posts', {
    id: int('id').autoincrement().notNull().primaryKey(),
    title: varchar('title', { length: 150 }).notNull(),
    slug: varchar('slug', { length: 255 }).notNull(),
    body: longtext('body'),
    cover: varchar('cover', { length: 50 }),
    publishedAt: datetime('published_at'),
}, (table) => [unique('slug_unique').on(table.slug)])

export const PostsLocalesTable = mysqlTable('posts_locales', { /* ... */ })
export const PostTagsTable = mysqlTable('post_tags', { /* ... */ })

Configure it in cms.config.ts:

cms.config.ts

schemaGeneration: {
    drizzle: {
        enabled: true, // default
        outDir: './dynamic-schemas', // default
        fileName: 'schema.ts', // default
        naming: {
            tables: 'pascalCase', // PostsTable    | 'camelCase': postsTable | 'snakeCase': posts_table
            columns: 'camelCase', // publishedAt   | 'snakeCase': published_at
        },
    },
},

The database names never change; naming only affects the TypeScript identifiers.

Using it in your website

1. Get the schema into your website

Pick what fits your repository layout:

Monorepo: import it directly: import * as cms from '@acme/cms/dynamic-schemas/schema'.
Separate repos: copy the file in CI after update-sections, or publish it as a small internal package.
Point outDir at a path inside your website project so the file lands there.

2. Create a Drizzle client

Terminal

pnpm add drizzle-orm mysql2

website/lib/db.ts

import { drizzle } from 'drizzle-orm/mysql2'
import mysql from 'mysql2/promise'
import * as schema from './cms-schema'

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    port: Number(process.env.DB_PORT ?? 3306),
    user: process.env.DB_USER, // ideally a read-only user
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
})

export const db = drizzle(pool, { schema, mode: 'default' })

Tip:

Give the website a MySQL user with only SELECT privileges. The CMS is the only writer.

Recipes

A paginated list

import { desc } from 'drizzle-orm'

export async function getPosts(page = 1, perPage = 10) {
    return db
        .select({
            id: PostsTable.id,
            title: PostsTable.title,
            slug: PostsTable.slug,
            cover: PostsTable.cover,
            publishedAt: PostsTable.publishedAt,
        })
        .from(PostsTable)
        .orderBy(desc(PostsTable.publishedAt))
        .limit(perPage)
        .offset((page - 1) * perPage)
}

One item by slug

import { eq } from 'drizzle-orm'

export async function getPostBySlug(slug: string) {
    const [post] = await db.select().from(PostsTable).where(eq(PostsTable.slug, slug)).limit(1)
    return post ?? null
}

A simple section (settings)

const [settings] = await db.select().from(SettingsTable).where(eq(SettingsTable.id, 1)).limit(1)

The row doesn't exist until the first save in the admin, so handle undefined.

Published items only

If you add a checkbox like is_published, filter on it. The CMS doesn't hide anything for you:

.where(eq(PostsTable.isPublished, true))

Relations

See Relationships for joins through selectField columns and destinationDb join tables.

Translations

See Querying translations.

Images

const MEDIA = process.env.NEXT_PUBLIC_MEDIA_URL

export const photoUrl = (section: string, name: string | null) => (name ? `${MEDIA}/.photos/${section}/${name}` : null)
export const thumbUrl = (section: string, name: string | null) => (name ? `${MEDIA}/.thumbs/${section}/${name}` : null)

See Media & Uploads for serving the files.

Caching and freshness

Content changes whenever an editor saves. Common strategies for a Next.js website:

Time-based: export const revalidate = 60 on pages, or cacheLife('minutes').
On-demand: call your website's revalidation endpoint from section hooks (afterCreate, afterUpdate, afterDelete), or install @nextjscms/plugin-cache-revalidation, which adds retries and a failure dashboard.

Without Drizzle

The tables are plain MySQL, so any client works:

SELECT id, title, slug, cover, published_at
FROM posts
ORDER BY published_at DESC
LIMIT 10;

Prisma users can run prisma db pull against the database; Laravel, Django, or Rails apps can read the same tables.

Column reference

Field	Value in the row
`textField`, `textAreaField`, `slugField`	string
`richTextField`	sanitized HTML string
`numberField`	number
`checkboxField`	`1` / `0` (Drizzle `boolean` → `true` / `false`)
`colorField`	`#rrggbb`
`dateField`, `dateRangeField`	UTC date/datetime string
`selectField`	option value / item identifier
`selectMultipleField`, `tagsField`	comma-separated string, or rows in `destinationDb`
`mapField`	`"lat,lng"` string
`photoField`, `videoField`, `documentField`	generated file name
`passwordField`	bcrypt hash