src/content/docs/custom-types.mdx
import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import Callout from '@mdx/Callout.astro'
The best way to see how customType definition is working is to check how existing data types
could be defined using customType function from Drizzle ORM.
import { customType } from 'drizzle-orm/pg-core';
...
import { customType } from 'drizzle-orm/mysql-core';
...
import { customType } from 'drizzle-orm/sqlite-core';
...
import { customType } from 'drizzle-orm/gel-core';
...
import { customType } from 'drizzle-orm/singlestore-core';
Integer
import { customType } from 'drizzle-orm/pg-core';
const customSerial = customType<{ data: number; }>(
{
dataType() {
return 'integer';
},
},
);
Text
import { customType } from 'drizzle-orm/pg-core';
const customText = customType<{ data: string }>({
dataType() {
return 'text';
},
});
Boolean
import { customType } from 'drizzle-orm/pg-core';
const customBoolean = customType<{ data: boolean }>({
dataType() {
return 'boolean';
},
});
Jsonb
import { customType } from 'drizzle-orm/pg-core';
const customJsonb = <TData>(name: string) =>
customType<{ data: TData; driverData: string }>({
dataType() {
return 'jsonb';
},
toDriver(value: TData): string {
return JSON.stringify(value);
},
})(name);
Timestamp
import { customType } from 'drizzle-orm/pg-core';
const customTimestamp = customType<
{
data: Date;
driverData: string;
config: { withTimezone: boolean; precision?: number };
}
>({
dataType(config) {
const precision = typeof config.precision !== 'undefined'
? ` (${config.precision})`
: '';
return `timestamp${precision}${
config.withTimezone ? ' with time zone' : ''
}`;
},
fromDriver(value: string): Date {
return new Date(value);
},
});
Usage for all types will be same as defined functions in Drizzle ORM. For example:
const usersTable = pgTable('users', {
id: customSerial('id').primaryKey(),
name: customText('name').notNull(),
verified: customBoolean('verified').notNull().default(false),
jsonb: customJsonb<string[]>('jsonb'),
createdAt: customTimestamp('created_at', { withTimezone: true }).notNull()
.default(sql`now()`),
});
You can check ts-doc for types and param definition.
export interface CustomTypeValues = {
/**
* Required type for custom column, that will infer proper type model
*
* Examples:
*
* If you want your column to be `string` type after selecting/or on inserting - use `data: string`. Like `text`, `varchar`
*
* If you want your column to be `number` type after selecting/or on inserting - use `data: number`. Like `integer`
*/
data: unknown;
/**
* Type helper, that represents what type database driver is returning for specific database data type
*
* Needed only in case driver's output and input for type differ
*
* Defaults to {@link driverData}
*/
driverOutput?: unknown;
/**
* Type helper, that represents what type database driver is accepting for specific database data type
*/
driverData?: unknown;
/**
* Type helper, that represents what type field returns after being aggregated to JSON for Relational Queries
*/
jsonData?: unknown;
/**
* What config type should be used for {@link CustomTypeParams} `dataType` generation
*/
config?: Record<string, unknown>;
/**
* If your custom data type should be notNull by default you can use `notNull: true`
*
* @example
* const customSerial = customType<{ data: number, notNull: true, default: true }>({
* dataType() {
* return 'serial';
* },
* });
*/
notNull?: boolean;
/**
* If your custom data type has default you can use `default: true`
*
* @example
* const customSerial = customType<{ data: number, notNull: true, default: true }>({
* dataType() {
* return 'serial';
* },
* });
*/
default?: boolean;
};
export interface CustomTypeParams<T extends CustomTypeValues> {
/**
* Database data type string representation, that is used for migrations
* @example
* ```
* `jsonb`, `text`
* ```
*
* If database data type needs additional params you can use them from `config` param
* @example
* ```
* `varchar(256)`, `numeric(2,3)`
* ```
*
* To make `config` be of specific type please use config generic in {@link CustomTypeValues}
*
* @example
* Usage example
* ```
* dataType() {
* return 'boolean';
* },
* ```
* Or
* ```
* dataType(config) {
* return typeof config.length !== 'undefined' ? `varchar(${config.length})` : `varchar`;
* }
* ```
*/
dataType: (config: T['config']) => string;
/**
* Optional mapping function, between user input and what database driver will provide to the database
* @example
* For example, when using jsonb we need to map JS/TS object to string before writing to database
* ```
* toDriver(value: TData): string {
* return JSON.stringify(value);
* }
* ```
*/
toDriver?: (value: T['data']) => T['driverData'];
/**
* Optional mapping function, that is used for transforming data returned by driver to desired column's output format
* @example
* For example, when using timestamp we need to map string Date representation to JS Date
* ```
* fromDriver(value: string): Date {
* return new Date(value);
* },
* ```
*
* It'll cause the returned data to change from:
* ```
* {
* customField: "2025-04-07T03:25:16.635Z";
* }
* ```
* to:
* ```
* {
* customField: new Date("2025-04-07T03:25:16.635Z");
* }
* ```
*/
fromDriver?: (value: T['driverData']) => T['data'];
/**
* Optional mapping function, that is used for transforming data returned by transofmed to JSON in database data to desired format
*
* Used by [relational queries](https://orm.drizzle.team/docs/rqb-v2)
*
* Defaults to {@link fromDriver} function
* @example
* For example, when querying bigint column via [RQB](https://orm.drizzle.team/docs/rqb-v2) or [JSON functions](https://orm.drizzle.team/docs/json-functions), the result field will be returned as it's string representation, as opposed to bigint from regular query
* To handle that, we need a separate function to handle such field's mapping:
* ```
* fromJson(value: string): bigint {
* return BigInt(value);
* },
* ```
*
* It'll cause the returned data to change from:
* ```
* {
* customField: "5044565289845416380";
* }
* ```
* to:
* ```
* {
* customField: 5044565289845416380n;
* }
* ```
*/
fromJson?: (value: T['jsonData']) => T['data'];
/**
* Optional selection modifier function, that is used for modifying selection of column inside [JSON functions](https://orm.drizzle.team/docs/json-functions)
*
* Additional mapping that could be required for such scenarios can be handled using {@link fromJson} function
*
* Used by [relational queries](https://orm.drizzle.team/docs/rqb-v2)
* @example
* For example, when using bigint we need to cast field to text to preserve data integrity
* ```
* forJsonSelect(identifier: SQL, sql: SQLGenerator, arrayDimensions?: number): SQL {
* return sql`${identifier}::text`
* },
* ```
*
* This will change query from:
* ```
* SELECT
* row_to_json("t".*)
* FROM
* (
* SELECT
* "table"."custom_bigint" AS "bigint"
* FROM
* "table"
* ) AS "t"
* ```
* to:
* ```
* SELECT
* row_to_json("t".*)
* FROM
* (
* SELECT
* "table"."custom_bigint"::text AS "bigint"
* FROM
* "table"
* ) AS "t"
* ```
*
* Returned by query object will change from:
* ```
* {
* bigint: 5044565289845416000; // Partial data loss due to direct conversion to JSON format
* }
* ```
* to:
* ```
* {
* bigint: "5044565289845416380"; // Data is preserved due to conversion of field to text before JSON-ification
* }
* ```
*/
forJsonSelect?: (identifier: SQL, sql: SQLGenerator, arrayDimensions?: number) => SQL;
}