cockroachSchema function creates a named schema object
Import cockroachSchema from 'drizzle-orm/cockroach-core'. Call it with a string argument to create a schema object. Example: `const mySchema = cockroachSchema("my_schema");`
cockroach/schemas
37 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Define enum types within a CockroachDB schema
Call the .enum() method on a schema object to define an enum type. Pass the enum name as the first argument and an array of allowed values as the second argument. Example: `const colors = mySchema.enum('colors', ['red', 'green', 'blue']);`
Define tables within a CockroachDB schema
Call the .table() method on a schema object to define a table within that schema. Pass the table name as the first argument and a column definition object as the second argument. Example: `const mySchemaUsers = mySchema.table('users', { id: int4('id').primaryKey(), name: string('name') });`
cockroachSchema creates named schema with prepended names in queries
When you declare an entity within a schema using cockroachSchema, the query builder will prepend the schema name in queries. For example, if you create a schema called 'my_schema' and a table called 'users', queries will use `select * from "my_schema"."users"`.
Using enum columns with default values in CockroachDB schemas
Enum columns defined in a schema can have default values. Use the .default() method on the enum column definition. Example: `color: colors('color').default('red')`
CockroachDB schema example with enum and table
```ts import { int4, string, cockroachSchema } from "drizzle-orm/cockroach-core"; export const mySchema = cockroachSchema("my_schema"); export const colors = mySchema.enum('colors', ['red', 'green', 'blue']); export const mySchemaUsers = mySchema.table('users', { id: int4('id').primaryKey(), name: string('name'), color: colors('color').default('red'), }); ``` This example creates a schema named 'my_schema', defines an enum type 'colors' with three values, and creates a 'users' table with an id, name, and color column where color defaults to 'red'.
CockroachDB schema definition in Drizzle
Define a CockroachDB table schema in Drizzle using cockroachTable from drizzle-orm/cockroach-core. A table requires at least one column. You must export all models defined in your schema files so that Drizzle-Kit can import them during the migration diff process.
Three ways to import CockroachDB schema types
You can define a CockroachDB table using three import patterns: (1) importing individual types like int4, varchar directly then passing them to cockroachTable; (2) using a callback parameter 't' that provides all type methods like t.int4(), t.varchar(); (3) importing all types as a namespace like 'import * as p' then using p.int4(), p.varchar(). All three approaches produce identical results.
Column name aliasing in CockroachDB schema
TypeScript key names are used as database column names by default. To use different names, pass the database column name as a string argument to the column type function. For example: firstName: varchar('first_name') will create a TypeScript property 'firstName' that maps to the database column 'first_name'.
snakeCase and camelCase builders for CockroachDB
Import snakeCase or camelCase from drizzle-orm/cockroach-core to automatically map naming conventions. Use snakeCase.table() or camelCase.table() to declare a table whose camelCase TypeScript column keys are automatically mapped to snake_case in the database. These builders are also available on views (snakeCase.view, camelCase.view), materialized views (snakeCase.materializedView, camelCase.materializedView), and schemas (snakeCase.schema, camelCase.schema).
Reusing column definitions across CockroachDB tables
Define common columns like timestamps in a separate file as an object with timestamp properties, then spread them across multiple table definitions using the spread operator. For example, define timestamps = { updated_at: timestamp(), created_at: timestamp().defaultNow().notNull(), deleted_at: timestamp() } in columns.helpers.ts, then use ...timestamps inside any table definition.
CockroachDB schemas (folders) in Drizzle
Use cockroachSchema from drizzle-orm/cockroach-core to create a schema namespace in CockroachDB. Define it with export const customSchema = cockroachSchema('custom'), then place tables inside it using customSchema.table('tableName', { columns }). This creates a folder-like structure in the database.
CockroachDB schema file organization patterns
You can organize schema files in three ways: (1) single file approach: put all tables in one schema.ts file and reference it as './src/db/schema.ts' in drizzle.config.ts; (2) separate files approach: create a schema folder with individual files for each table (users.ts, products.ts, etc.) and reference the folder as './src/db/schema' in drizzle.config.ts; (3) grouped files approach: organize tables by feature groups (users.ts, messaging.ts, products.ts) in a schema folder. Drizzle-Kit will recursively find and import all drizzle tables from the specified path.
CockroachDB schema example with enums, references, and indexes
Example: import { cockroachEnum, cockroachTable as table } from "drizzle-orm/cockroach-core"; import * as t from "drizzle-orm/cockroach-core"; export const rolesEnum = cockroachEnum("roles", ["guest", "user", "admin"]); export const users = table("users", { id: t.int4().primaryKey().generatedAlwaysAsIdentity(), firstName: t.varchar("first_name", { length: 256 }), lastName: t.varchar("last_name", { length: 256 }), email: t.varchar().notNull(), invitee: t.int4().references((): AnyCockroachColumn => users.id), role: rolesEnum().default("guest"), }, (table) => [ t.uniqueIndex("email_idx").on(table.email) ]); export const posts = table("posts", { id: t.int4().primaryKey().generatedAlwaysAsIdentity(), slug: t.varchar().$default(() => generateUniqueString(16)), title: t.varchar({ length: 256 }), ownerId: t.int4("owner_id").references(() => users.id), }, (table) => [ t.uniqueIndex("slug_idx").on(table.slug), t.index("title_idx").on(table.title), ]); export const comments = table("comments", { id: t.int4().primaryKey().generatedAlwaysAsIdentity(), text: t.varchar({ length: 256 }), postId: t.int4("post_id").references(() => posts.id), ownerId: t.int4("owner_id").references(() => users.id), });
Schema model types supported in CockroachDB
Drizzle schema for CockroachDB supports: tables with columns and constraints, schemas (folders), enums, sequences, views, and materialized views.
createUpdateSchema for CockroachDB tables
Use createUpdateSchema from drizzle-orm/typebox-legacy to generate a schema that defines the shape of data to be updated in the database. This schema can be used to validate API requests. All columns in an update schema are optional.
typebox-legacy install
To use typebox-legacy with Drizzle ORM for CockroachDB, install drizzle-orm@rc and @sinclair/typebox as dependencies.
createSelectSchema for CockroachDB tables
Use createSelectSchema from drizzle-orm/typebox-legacy to generate a schema that defines the shape of data queried from the database. This schema can be used to validate API responses. The schema type includes all columns from the table, so if a query does not select all columns, parsing with the schema will fail.
createInsertSchema for CockroachDB tables
Use createInsertSchema from drizzle-orm/typebox-legacy to generate a schema that defines the shape of data to be inserted into the database. This schema can be used to validate API requests. Columns that are not nullable and have no default value are required in the insert schema.
createSchemaFactory for extended Typebox instances
Use createSchemaFactory from drizzle-orm/typebox to create schema factory functions when using an extended Typebox instance. Pass the extended instance as the typeboxInstance option. The resulting factory functions return createSelectSchema, createInsertSchema, and createUpdateSchema that use the extended Typebox instance.
Example schema refinements with callback and overwrite
import { int4, jsonb, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createSelectSchema } from 'drizzle-orm/typebox-legacy'; import { Type } from '@sinclair/typebox'; import { Value } from '@sinclair/typebox/value'; const users = cockroachTable('users', { id: int4().primaryKey().generatedAlwaysAsIdentity(), name: text().notNull(), bio: text(), preferences: jsonb() }); const userSelectSchema = createSelectSchema(users, { name: (schema) => Type.String({ ...schema, maxLength: 20 }), bio: (schema) => Type.String({ ...schema, maxLength: 1000 }), preferences: Type.Object({ theme: Type.String() }) });
Example createSchemaFactory with extended Typebox
import { int4, cockroachTable, text } from 'drizzle-orm/cockroach-core'; import { createSchemaFactory } from 'drizzle-orm/typebox'; import { t } from 'elysia'; const users = cockroachTable('users', { id: int4().primaryKey().generatedAlwaysAsIdentity(), name: text().notNull(), age: int4().notNull() }); const { createInsertSchema } = createSchemaFactory({ typeboxInstance: t }); const userInsertSchema = createInsertSchema(users, { name: (schema) => t.Number({ ...schema }, { error: '`name` must be a string' }) });
Declare view with standalone QueryBuilder
Import QueryBuilder from drizzle-orm/cockroach-core, create an instance with new QueryBuilder(), then use cockroachView(name).as(qb.select()...) to declare a view. This works identically to inline query builder syntax.
Declare view with raw SQL and explicit schema
When using raw sql operator to declare a view with unsupported syntax, you must explicitly specify view columns schema. Use cockroachView(name, { column definitions }).as(sql`...`) or cockroachMaterializedView(name, { column definitions }).as(sql`...`).
Declare existing view with .existing()
When you have read-only access to an existing view in the database, use cockroachView(name, { column definitions }).existing(). This tells drizzle-kit to ignore this view and not generate a create view statement in migrations.
Refresh materialized view - basic syntax
Call await db.refreshMaterializedView(viewName) to refresh a materialized view at runtime.
Refresh materialized view - concurrently
Call await db.refreshMaterializedView(viewName).concurrently() to refresh a materialized view concurrently.
Refresh materialized view - with no data
Call await db.refreshMaterializedView(viewName).withNoData() to refresh a materialized view without populating data.
Materialized view .withNoData() method
Use .withNoData() on a materialized view definition to create the view without data, e.g. cockroachMaterializedView(name).withNoData().as((qb) => ...).
View declaration methods - query builder column inference
When views are declared with inline query builder or standalone query builder, view columns schema is automatically inferred. When using raw sql operator, columns must be explicitly declared.
View parameters are inlined in SQL
All parameters inside view queries are inlined in the generated SQL, not replaced by $1, $2, etc.
CockroachView with inline query builder example
import { cockroachTable, cockroachView, int4, text, timestamp } from "drizzle-orm/cockroach-core"; import { eq } from "drizzle-orm"; export const user = cockroachTable("user", { id: int4(), name: text(), email: text(), password: text(), role: text().$type<"admin" | "customer">(), createdAt: timestamp("created_at"), updatedAt: timestamp("updated_at"), }); export const userView = cockroachView("user_view").as((qb) => qb.select().from(user)); export const customersView = cockroachView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));
CockroachView with existing() example
export const trimmedUser = cockroachView("trimmed_user", { id: int4("id"), name: text("name"), email: text("email"), }).existing(); export const trimmedUser = cockroachMaterializedView("trimmed_user", { id: int4("id"), name: text("name"), email: text("email"), }).existing(); This example shows how to declare existing views that already exist in the database without generating create view migrations.
CockroachMaterializedView complex example with CTE
export const newYorkers2 = cockroachMaterializedView('new_yorkers_2') .withNoData() .as((qb) => { const sq = qb.$with('sq').as( qb .select({ userId: users.id.as('users_id'), cityId: cities.id.as('cities_id'), homeCity: users.homeCity, }) .from(users) .leftJoin(cities, eq(cities.id, users.homeCity)) .where(sql`${users.age1} > 18`), ); return qb .with(sq) .select() .from(sq) .where(sql`${sq.homeCity} = 1`); }); This example shows a materialized view using withNoData() and a common table expression (CTE) with $with().
CockroachView with raw SQL and schema example
export const newYorkers = cockroachView('new_yorkers', { id: int4().primaryKey(), name: text().notNull(), cityId: integer('city_id').notNull(), }).as(sql`select * from ${users} where ${eq(users.cityId, 1)}`); export const newYorkers = cockroachMaterializedView('new_yorkers', { id: int4().primaryKey(), name: text().notNull(), cityId: integer('city_id').notNull(), }).as(sql`select * from ${users} where ${eq(users.cityId, 1)}`); This example shows how to declare both regular and materialized views using raw SQL with explicit column definitions.
cockroachView - declare view with inline query builder
Use cockroachView(name).as((qb) => qb.select()...) to declare a view with an inline query builder. View columns schema will be automatically inferred from the query builder.
cockroachMaterializedView - declare materialized view
Use cockroachMaterializedView(name).as((qb) => qb.select()...) to declare a materialized view. Materialized views in CockroachDB persist results in a table-like form, returning results directly from the materialized view rather than reconstructing them from base tables.