mssql/schemas

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

MSSQL schema declaration with mssqlSchema

To declare a schema in MSSQL with Drizzle ORM, import mssqlSchema from 'drizzle-orm/mssql-core' and call it with the schema name as a string. Tables declared within this schema will be prepended with the schema name in generated queries using the format [schema].[table].

MSSQL schema table definition example

Example of declaring a table within an MSSQL schema: Create a schema using mssqlSchema('my_schema'), then call .table('users', {...}) on it to define the table with columns. The generated query will be select * from [my_schema].[users].

MSSQL schema code example

```ts import { int, text, mssqlSchema, nvarchar } from 'drizzle-orm/mssql-core'; export const mySchema = mssqlSchema('my_schema'); export const mySchemaUsers = mySchema.table('users', { id: int().identity().primaryKey(), name: text(), color: nvarchar({ length: 100 }).default('red'), }); ``` This declares a schema named 'my_schema' with a 'users' table containing an identity primary key, text name column, and nvarchar color column with a default value of 'red'.

MSSQL schema definition with Drizzle

Drizzle lets you define a schema in TypeScript with various models and properties supported by the underlying database. When you define your schema, it serves as the source of truth for future modifications in queries using Drizzle-ORM and migrations using Drizzle-Kit. All models defined in schema files must be exported so that Drizzle-Kit can import them and use them in the migration diff process.

MSSQL table definition with mssqlTable

Define an MSSQL table in Drizzle using mssqlTable from drizzle-orm/mssql-core. A table must have at least one column. There is no common table object in Drizzle; you must choose the database-specific table object (mssqlTable for MSSQL).

MSSQL basic table example

Example of defining an MSSQL table with three columns: id as int with primary key and identity, name as varchar with length 256 and not null, and email as varchar with length 256, not null, and unique constraint. Columns are defined using methods like int().primaryKey().identity(), varchar({ length: 256 }).notNull(), and .unique().

Three ways to define MSSQL schema

MSSQL tables can be defined using three approaches: (1) Using direct imports of column type functions from drizzle-orm/mssql-core and calling them directly, (2) Using a callback function parameter that receives a parameter object with column type methods (t), or (3) Using import * as t syntax to import all functions as a namespace. All three approaches produce equivalent results.

snakeCase and camelCase builders for MSSQL

Drizzle provides snakeCase and camelCase builders from drizzle-orm/mssql-core to automatically map naming conventions between TypeScript and database. Use snakeCase.table, snakeCase.view, snakeCase.schema or camelCase.table, camelCase.view, camelCase.schema. When using snakeCase builder, camelCase column names in TypeScript are automatically converted to snake_case in the database (e.g., fullName becomes full_name, createdAt becomes created_at).

MSSQL schema file organization - single file

The most common approach is to place all table definitions in a single schema.ts file (or any file name you prefer like models.ts). In drizzle.config.ts, specify the path to this file: schema: './src/db/schema.ts'. Drizzle will read from this file during migration generation.

MSSQL schema objects with mssqlSchema

MSSQL supports schemas as organizational structures. Define a schema using mssqlSchema from drizzle-orm/mssql-core: export const customSchema = mssqlSchema('custom'). Then place tables inside the schema using customSchema.table('tableName', {...}).

MSSQL int().primaryKey().identity()

In MSSQL, create an auto-incrementing integer primary key using int().primaryKey().identity(). The identity constraint automatically generates sequential integer values for new rows.

MSSQL varchar column with length

Define a varchar column in MSSQL using varchar({ length: 256 }). The length parameter is required and specifies the maximum number of characters. Use .notNull() to make it required and .unique() to add a unique constraint.

MSSQL foreign key references

Define a foreign key reference in MSSQL using .references() method on an int column, passing a function that returns the referenced column. Example: invitee: t.int().references((): AnyMsSqlColumn => users.id) creates a foreign key to the users table id column. Similarly, ownerId: t.int('owner_id').references(() => users.id) references the users table.

MSSQL enum in varchar column

Define an enumerated type in MSSQL using varchar with an enum parameter: t.varchar({ length: 20, enum: ['guest', 'admin', 'user'] }). This restricts the column to specific string values. Use .default('guest') to set a default value.

MSSQL $default with function

Set a dynamic default value on a column using .$default() with a function. Example: slug: t.varchar({ length: 256 }).$default(() => generateUniqueString(16)) generates a unique slug string when a row is inserted without an explicit value.

MSSQL uniqueIndex constraint

Define a unique index on a table using t.uniqueIndex('index_name').on(column) in the table constraints array (second parameter to mssqlTable). Example: t.uniqueIndex('email_idx').on(table.email) creates a unique constraint on the email column.

MSSQL regular index

Define a regular (non-unique) index on a table using t.index('index_name').on(column) in the table constraints array. Example: t.index('title_idx').on(table.title) creates an index on the title column for query performance.

MSSQL table constraints syntax

Table constraints (indexes, unique constraints, etc.) are defined as an array in the third parameter to mssqlTable. The third parameter is a function that receives the table object and returns an array of constraint definitions. Example: mssqlTable('users', {...}, (table) => [t.uniqueIndex('email_idx').on(table.email)])

Give your agent this brain