mysql-core/schemas

14 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Declare MySQL table in a schema

To declare a table within a MySQL schema, import mysqlSchema from drizzle-orm/mysql-core, call mysqlSchema with the schema name as a string, then call .table() on the returned schema object with the table name and column definitions. When a table is declared within a schema, query builder will prepend the schema name in queries, resulting in queries like 'select * from schema.users'.

MySQL schema example with users table

import { int, text, mysqlSchema } from "drizzle-orm/mysql-core"; export const mySchema = mysqlSchema("my_schema") export const mySchemaUsers = mySchema.table("users", { id: int("id").primaryKey().autoincrement(), name: text("name"), });

Table name conflicts in different schemas cause type errors

If tables with the same names exist in different schemas, Drizzle will respond with a 'never[]' error in result types and an error from the database. To resolve this, use alias syntax for joins.

MySQL table definition with mysqlTable

In Drizzle, MySQL tables must be defined using the `mysqlTable()` function imported from 'drizzle-orm/mysql-core'. Each table requires at least one column. There are three syntax styles available: direct imports, using a callback with a parameter, and using import * as t.

Snake case and camel case builders for MySQL

Drizzle provides `snakeCase` and `camelCase` builders imported from 'drizzle-orm/mysql-core' to automatically map naming conventions between TypeScript and the database. Use `snakeCase.table()`, `snakeCase.view()`, `snakeCase.schema()` and similarly for `camelCase`. These automatically convert camelCase TypeScript names to snake_case database names or vice versa.

MySQL schema definition

In MySQL, a schema is equivalent to a database. Define a MySQL schema using `mysqlSchema()` from 'drizzle-orm/mysql-core': `export const customSchema = mysqlSchema('custom');`. Tables can then be placed inside the schema object: `export const users = customSchema.table('users', { ... })`

Reusable column helpers pattern

To avoid repetition of common columns across multiple tables, define them in a separate helper file and spread them into table definitions. For example, define `const timestamps = { updated_at: timestamp(), created_at: timestamp().defaultNow().notNull(), deleted_at: timestamp() }` in a helper file and use `...timestamps` in table definitions.

MySQL table constraints syntax

Table constraints and indexes in MySQL are defined as a second parameter to `mysqlTable()` using a callback function that returns an array. Example: `table('users', { ... }, (table) => [t.uniqueIndex('email_idx').on(table.email), t.index('title_idx').on(table.title)])`

CockroachDB schema declaration with cockroachSchema

Use cockroachSchema() to declare a schema in CockroachDB. When tables are declared within a schema, the query builder will prepend schema names in queries. For example, a table declared in schema 'my_schema' will generate SQL like `select * from "my_schema"."users"`.

CockroachDB enum type creation in schema

Create enum types within a CockroachDB schema using mySchema.enum(). Example: mySchema.enum('colors', ['red', 'green', 'blue']) creates an enum type in the schema that generates `CREATE TYPE "my_schema"."colors" AS ENUM('red', 'green', 'blue');`

CockroachDB schema table definition example

Example of defining a table within a CockroachDB schema: ```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 generates a table in the named schema with an enum column that references the schema-scoped enum type.

MySQL schema declaration with mysqlSchema

You can declare MySQL databases/schemas and tables within them using mysqlSchema from drizzle-orm-mysql. Import mysqlSchema, create a schema instance with mysqlSchema('my_schema'), then call it with a table definition. The generated SQL creates both the database and table with the schema prefix.

MySQL schema example with serial, text columns

Example of MySQL schema with table definition: const mySchema = mysqlSchema('my_schema'); const users = mySchema('users', { id: serial('id').primaryKey(), name: text('name'), email: text('email') });

MySQL schema generates CREATE DATABASE and CREATE TABLE migrations

When using drizzle-kit generate:mysql with a schema definition, it automatically generates SQL migrations that create both the database (CREATE DATABASE `my_schema`) and the table (CREATE TABLE `my_schema`.`users`) with proper backtick escaping.

Give your agent this brain