Common way of defining custom types

import { customType } from 'drizzle-orm/pg-core';

const customSerial = customType<{ data: number; }>(
  {
    dataType() {
      return 'integer';
    },
  },
);

import { customType } from 'drizzle-orm/pg-core';

const customText = customType<{ data: string }>({
  dataType() {
    return 'text';
  },
});

import { customType } from 'drizzle-orm/pg-core';

const customBoolean = customType<{ data: boolean }>({
  dataType() {
    return 'boolean';
  },
});

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);

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);
  },
});

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()`),
});

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;
}