Postgres

To use FrogBot with Postgres, install the package @frogbotai/db-postgres. It leverages Drizzle ORM and node-postgres to interact with a Postgres database that you provide. Alternatively, the @frogbotai/db-vercel-postgres package is also available and is optimized for use with Vercel. It automatically manages changes to your database for you in development mode, and exposes a full suite of migration controls for you to leverage in order to keep other database environments in sync with your schema. DDL transformations are automatically generated. To configure FrogBot to use Postgres, pass the postgresAdapter to your FrogBot Config as follows:

Usage

@frogbotai/db-postgres:

import { postgresAdapter } from '@frogbotai/db-postgres'

export default buildConfig({
  // Configure the Postgres adapter here
  db: postgresAdapter({
    // Postgres-specific arguments go here.
    // `pool` is required.
    pool: {
      connectionString: process.env.DATABASE_URL,
    },
  }),
})

@frogbotai/db-vercel-postgres:

import { vercelPostgresAdapter } from '@frogbotai/db-vercel-postgres'

export default buildConfig({
  // Without a pool, the adapter uses process.env.POSTGRES_URL.
  // Pass @vercel/postgres pool options to connect elsewhere:
  // vercelPostgresAdapter({ pool: { connectionString: process.env.DATABASE_URL } })
  db: vercelPostgresAdapter({}),
})

Collection search

Both adapters implement collection search with PostgreSQL full-text search and pgvector. Lexical, vector, and hybrid queries each run as a single SQL statement, and ranking happens inside the database after access, where, draft, and locale filters are applied.

import { postgresAdapter } from '@frogbotai/db-postgres'
import { buildConfig } from 'frogbot'

export default buildConfig({
  secret: process.env.FROGBOT_SECRET!,
  db: postgresAdapter({ pool: { connectionString: process.env.DATABASE_URL } }),
  collections: [
    {
      slug: 'articles',
      fields: [
        { name: 'title', type: 'text', required: true },
        { name: 'body', type: 'textarea' },
        { name: 'embedding', type: 'vector', dimensions: 1536 },
      ],
      search: {
        content: {
          lexical: { fields: ['title', 'body'], language: 'english' },
          vector: { field: 'embedding' },
        },
      },
    },
  ],
})

Prerequisites

Lexical-only indexes need nothing beyond PostgreSQL. An index with vector needs pgvector 0.8.0 or later. Each time the adapter connects, including when you run migrate, it checks pg_extension and runs CREATE EXTENSION IF NOT EXISTS vector if pgvector is missing. migrate:create does not connect, so it generates the schema without this check. pgvector is not a trusted extension, so the connecting role must be a superuser, or you must create the extension ahead of time with a role that can. On a managed host, enable pgvector the way your host documents when the application role cannot create it. If the extension cannot be used, the adapter stops setup with SearchCapabilityError. The message names the first vector index, the reason, and the PostgreSQL SQLSTATE:

Reason	Cause
`permission-denied`	SQLSTATE `42501`: the role cannot create the extension.
`missing-prerequisite`	pgvector is not installed on the server (`0A000` or `58P01`), or the installed version is older than 0.8.0.
`setup-failed`	Any other error while creating the extension.

Storage and indexes

A vector field that an index selects with vector.field is stored as a native vector(n) column in the collection, locale, and version tables. Vector fields that no index selects stay JSON columns. Setup adds these indexes to the schema, so they appear in development push and in generated migrations:

An HNSW index for each vector field and metric: vector_cosine_ops for cosine, vector_l2_ops for euclidean, and vector_ip_ops for dotProduct. pgvector’s HNSW index supports up to 2,000 dimensions. Larger vectors, and indexes with vector.approximate: false, are searched without an approximate index.
A GIN index on to_tsvector(language, ...) over an index’s lexical fields. lexical.language is a PostgreSQL text search configuration name such as english or french and defaults to simple. When some lexical fields are localized and others are not, the fields live in different tables, so the query is still correct but has no GIN index.

Collections with drafts get the same indexes on the version table, so draft: true queries rank the latest draft of each document. Changing a vector field’s dimensions requires a migration that rewrites or clears existing vectors.

Ranking

Mode	`ranking.method`	Score
Lexical	`postgres-fts`	`ts_rank_cd` over `websearch_to_tsquery`. Higher is better.
Vector	`pgvector-hnsw` or `pgvector-exact`	Cosine similarity, Euclidean distance (lower is better), or inner product.
Hybrid	`rrf`	Weighted reciprocal rank fusion, `weight / (60 + rank)` summed over both components. Higher is better.

Query text supports web search syntax: quoted phrases, or, and - to exclude a term. It is always sent as a bound parameter. Vector and hybrid queries on an HNSW index report approximate: true. They run in a transaction with SET LOCAL hnsw.iterative_scan = strict_order, so pgvector keeps scanning the index until enough rows pass your access and where filters, in exact distance order. The same transaction sets hnsw.ef_search to the resolved candidate count, with pgvector’s default of 40 as the minimum and its maximum of 1,000 as the cap. Both settings end with the transaction and never reach other pooled connections. Iterative scans still stop at pgvector’s scan limits, so a very selective filter can return fewer than limit hits. Exact queries on an index with vector.approximate: false run with SET LOCAL enable_indexscan = off, so they never use an HNSW index that another search index built on the same field. When the request already runs in a transaction, search reads through it and sees its uncommitted writes. Hybrid search takes up to the resolved candidate count from each component, joins them in the database, and returns the top limit by fused score. A record that matches only one component still ranks, with the other component reported as null.

Limits

Localized fields are ranked on the requested locale’s stored value. Fallback-locale values are not ranked.
If a search runs before its columns or indexes exist, for example before you run migrations, it fails with SearchReadinessError.

Options

Both adapters accept these options. @frogbotai/db-vercel-postgres takes a different pool and adds two options of its own; see Vercel Postgres options.

Option	Type	Default	Description
`pool`	`PoolConfig`	Required	`node-postgres` pool options, such as `connectionString`.
`push`	`boolean`	`true`	Pushes schema changes to the database with Drizzle’s `db push` on startup. Push never runs when `NODE_ENV` is `production` or during migration commands. Set `false` to rely on migrations only.
`query`	`PostgresQueryConfig`	—	Customizes how query operators such as `contains`, `like` and `not_like` are built, for example to make text matching accent-insensitive.
`migrationDir`	`string`	First of `src/migrations`, `dist/migrations`, or `migrations` that exists	Directory migrations are written to and read from. See Migrations.
`prodMigrations`	`{ name: string; up: (args: MigrateUpArgs) => Promise<void>; down: (args: MigrateDownArgs) => Promise<void> }[]`	—	Migrations to run on connect when `NODE_ENV` is `production`. See Running migrations in production.
`idType`	`'serial' \| 'uuid' \| 'uuidv7'`	`'serial'`	Column type for document IDs. `serial` IDs are numbers; `uuid` and `uuidv7` IDs are strings.
`allowIDOnCreate`	`boolean`	`false`	Uses an `id` passed in create data, without a custom ID field.
`schemaName`	`string`	`'public'`	Experimental. Postgres schema for FrogBot’s tables. Works only when no table or enum with the same name exists in another schema.
`transactionOptions`	`PgTransactionConfig \| false`	—	Options for transactions, or `false` to disable them.
`disableCreateDatabase`	`boolean`	`false`	Stops the adapter from creating the database when it doesn’t exist.
`extensions`	`string[]`	`[]`	Postgres extensions to create on connect with `CREATE EXTENSION IF NOT EXISTS`. A failure is logged and doesn’t stop startup. Listing `postgis` also keeps development push from touching PostGIS tables.
`tablesFilter`	`string[]`	—	Table name patterns development push manages. Tables that don’t match are left alone.
`readReplicas`	`string[]`	—	Connection strings of read replicas, to offload read-heavy traffic.
`readReplicasAfterWriteInterval`	`number`	`2000`	How long, in milliseconds, reads go to the primary after a write instead of a replica, to avoid stale reads from replication lag. Applies only with `readReplicas`. Set `0` to disable.
`blocksAsJSON`	`boolean`	`false`	Stores blocks in a JSON column instead of relational tables, which can be faster with many blocks.
`localesSuffix`	`string`	`'_locales'`	Suffix for tables that store localized fields.
`relationshipsSuffix`	`string`	`'_rels'`	Suffix for tables that store relationships.
`versionsSuffix`	`string`	`'_v'`	Suffix for tables that store versions.
`beforeSchemaInit`	`PostgresSchemaHook[]`	`[]`	Drizzle schema hooks that run before the schema is built. See beforeSchemaInit.
`afterSchemaInit`	`PostgresSchemaHook[]`	`[]`	Drizzle schema hooks that run after the schema is built. See afterSchemaInit.
`logger`	`boolean \| Logger`	`false`	Drizzle query logger. `true` logs every query; pass a Drizzle `Logger` to handle them yourself.
`generateSchemaOutputFile`	`string`	A generated-schema file in `src/`, or in the project root without `src/`	File that `frogbot.db.generateSchema()` writes the typed Drizzle schema to.
`pg`	`typeof import('pg')`	The bundled `pg` module	`node-postgres` module used to create pools. Pass your own to control its version or instrument it. Not available on `@frogbotai/db-vercel-postgres`.

Vercel Postgres options

Option	Type	Default	Description
`pool`	`VercelPostgresPoolConfig`	—	`@vercel/postgres` pool options. Without it, the adapter connects with `process.env.POSTGRES_URL`.
`forceUseVercelPostgres`	`boolean`	`false`	Uses `@vercel/postgres` even when the connection string points to `localhost` or `127.0.0.1`. By default, local databases connect through `pg` instead.
`connectionString`	`string`	—	Accepted but not read. Set `pool.connectionString` instead.

Access to Drizzle

After FrogBot is initialized, this adapter will expose the full power of Drizzle to you for use if you need it. You can query the adapter’s runtime tables directly. These queries are untyped:

// To avoid installing Drizzle, you can import everything that drizzle has from our re-export path.
import { eq, sql, and } from '@frogbotai/db-postgres/drizzle'

const postsTable = frogbot.db.tables.posts

// Drizzle's Querying API: https://orm.drizzle.team/docs/rqb
const posts = await frogbot.db.drizzle.query.posts.findMany()

// Drizzle's Select API: https://orm.drizzle.team/docs/select
const result = await frogbot.db.drizzle
  .select()
  .from(postsTable)
  .where(
    and(
      eq(postsTable.id, 50),
      sql`lower(${postsTable.title}) = 'example post title'`,
    ),
  )

Tables, relations, and enums

In addition to exposing Drizzle directly, all of the tables, Drizzle relations, and enum configs are exposed for you via the frogbot.db property as well.

Tables - frogbot.db.tables
Enums - frogbot.db.enums
Relations - frogbot.db.relations

Prototyping in development mode

Drizzle exposes two ways to work locally in development mode. The first is db push, which automatically pushes changes you make to your FrogBot Config (and therefore, Drizzle schema) to your database so you don’t have to manually migrate every time you change your FrogBot Config. This only works in development mode, and should not be mixed with manually running migrate commands. You will be warned if any changes that you make will entail data loss while in development mode. Push is enabled by default, but you can opt out if you’d like. Alternatively, you can disable push and rely solely on migrations to keep your local database in sync with your FrogBot Config.

Migration workflows

In Postgres, migrations are a fundamental aspect of working with FrogBot and you should become familiar with how they work. For more information about migrations, click here.

Drizzle schema hooks

Apps created with create-frogbot-app already depend on drizzle-orm at the adapter’s version; in other apps, install the same version used by your database adapter.

beforeSchemaInit

Runs before the schema is built. You can use this hook to extend your database structure with tables that won’t be managed by FrogBot.

import { postgresAdapter } from '@frogbotai/db-postgres'
import { pgTable, serial } from 'drizzle-orm/pg-core'

postgresAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          addedTable: pgTable('added_table', {
            id: serial('id').notNull(),
          }),
        },
      }
    },
  ],
})

One use case is preserving your existing database structure when migrating to FrogBot. By default, FrogBot drops the current database schema, which may not be desirable in this scenario. To quickly generate the Drizzle schema from your database you can use Drizzle Introspection You should get the schema.ts file which may look like this:

import {
  pgTable,
  uniqueIndex,
  serial,
  varchar,
  text,
} from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
})

export const countries = pgTable(
  'countries',
  {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }),
  },
  (countries) => {
    return {
      nameIndex: uniqueIndex('name_idx').on(countries.name),
    }
  },
)

You can import them into your config and append to the schema with the beforeSchemaInit hook like this:

import { postgresAdapter } from '@frogbotai/db-postgres'
import { users, countries } from '../drizzle/schema'

postgresAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          users,
          countries,
        },
      }
    },
  ],
})

Make sure FrogBot doesn’t overlap table names with its collections. For example, if you already have a collection with slug “users”, you should either change the slug or dbName to change the table name for this collection.

afterSchemaInit

Runs after the Drizzle schema is built. You can use this hook to modify the schema with features that aren’t supported by FrogBot, or if you want to add a column that you don’t want to be in the FrogBot config. To extend a table, FrogBot exposes extendTable utility to the args. You can refer to the Drizzle documentation. The following example adds the extra_integer_column column and a composite index on country and city columns.

import { postgresAdapter } from '@frogbotai/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'frogbot'

export default buildConfig({
  collections: [
    {
      slug: 'places',
      fields: [
        {
          name: 'country',
          type: 'text',
        },
        {
          name: 'city',
          type: 'text',
        },
      ],
    },
  ],
  db: postgresAdapter({
    afterSchemaInit: [
      ({ schema, extendTable, adapter }) => {
        extendTable({
          table: schema.tables.places,
          columns: {
            extraIntegerColumn: integer('extra_integer_column'),
          },
          extraConfig: (table) => ({
            country_city_composite_index: index(
              'country_city_composite_index',
            ).on(table.country, table.city),
          }),
        })

        return schema
      },
    ],
  }),
})

Adding tables through rawTables

You can also add tables, columns, and indexes to the adapter’s raw SQL schema through adapter.rawTables in beforeSchemaInit. The raw schema supports fewer features than Drizzle’s table builders.

import { postgresAdapter } from '@frogbotai/db-postgres'

postgresAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      // Add a new table
      adapter.rawTables.myTable = {
        name: 'my_table',
        columns: {
          my_id: {
            name: 'my_id',
            type: 'serial',
            primaryKey: true,
          },
        },
      }

      // Add a new column to a table generated by FrogBot
      adapter.rawTables.posts.columns.customColumn = {
        name: 'custom_column',
        type: 'integer',
        notNull: true,
      }

      // Add a new index to a table generated by FrogBot
      adapter.rawTables.posts.indexes.customColumnIdx = {
        name: 'custom_column_idx',
        unique: true,
        on: ['custom_column'],
      }

      return schema
    },
  ],
})

Start here

Basics

Managing Data

Features

Ecosystem

Deployment

Usage

Collection search

Prerequisites

Storage and indexes

Ranking

Limits

Options

Vercel Postgres options

Access to Drizzle

Tables, relations, and enums

Prototyping in development mode

Migration workflows

Drizzle schema hooks

beforeSchemaInit

afterSchemaInit

Adding tables through rawTables

​Usage

​Collection search

​Prerequisites

​Storage and indexes

​Ranking

​Limits

​Options

​Vercel Postgres options

​Access to Drizzle

​Tables, relations, and enums

​Prototyping in development mode

​Migration workflows

​Drizzle schema hooks

​beforeSchemaInit

​afterSchemaInit

​Adding tables through rawTables

Usage

Collection search

Prerequisites

Storage and indexes

Ranking

Limits

Options

Vercel Postgres options

Access to Drizzle

Tables, relations, and enums

Prototyping in development mode

Migration workflows

Drizzle schema hooks

beforeSchemaInit

afterSchemaInit

Adding tables through rawTables