Field

Database table column/field with data type and constraints

Overview

The DBField interface represents a column in a database table with its data type, constraints, and metadata.

Interface Definition

interface DBField {
    id: string;
    name: string;
    type: DataType;
    primaryKey: boolean;
    unique: boolean;
    nullable: boolean;
    increment?: boolean | null;
    isArray?: boolean | null;
    createdAt: number;
    characterMaximumLength?: string | null;
    precision?: number | null;
    scale?: number | null;
    default?: string | null;
    collation?: string | null;
    comments?: string | null;
    check?: string | null;
}

Properties

id · string · required

Unique identifier for the field

name · string · required

Column name in the database

type · DataType · required

Data type of the field. Contains:

• id: Type identifier (e.g., 'varchar', 'integer')
• name: Display name of the type

Common types: varchar, text, integer, bigint, decimal, boolean, timestamp, uuid, json, jsonb

primaryKey · boolean · required

Whether this field is part of the primary key

unique · boolean · required

Whether this field has a unique constraint

nullable · boolean · required

Whether this field accepts NULL values. Primary keys are always non-nullable.

increment · boolean | null

Whether this is an auto-increment field:

• PostgreSQL: SERIAL, BIGSERIAL, or GENERATED BY DEFAULT AS IDENTITY
• MySQL/MariaDB: AUTO_INCREMENT
• SQL Server: IDENTITY(1,1)
• SQLite: AUTOINCREMENT

isArray · boolean | null

Whether this field is an array type (PostgreSQL/CockroachDB only).

Examples: integer[], text[], varchar(50)[]

createdAt · number · required

Timestamp when the field was created (milliseconds since epoch)

characterMaximumLength · string | null

Maximum length for character types (VARCHAR, CHAR).

Can be:

• A number: '255', '100'
• Special value: 'max' (SQL Server for VARCHAR(MAX))

precision · number | null

Precision for numeric types (DECIMAL, NUMERIC, FLOAT).

For DECIMAL(10, 2), precision is 10.

scale · number | null

Scale for numeric types (DECIMAL, NUMERIC).

For DECIMAL(10, 2), scale is 2.

default · string | null

Default value expression for the field.

Examples:

• String literals: 'active', 'pending'
• Numbers: 0, 100
• Functions: CURRENT_TIMESTAMP, gen_random_uuid(), NOW()
• Boolean: TRUE, FALSE
• NULL: NULL

collation · string | null

Character collation for string types (e.g., 'utf8_general_ci', 'en_US.UTF-8')

comments · string | null

Field-level comments/description (supported in PostgreSQL, MySQL, SQL Server)

check · string | null

Field-level check constraint expression (e.g., 'price > 0')

Zod Schema

const dbFieldSchema: z.ZodType<DBField> = z.object({
    id: z.string(),
    name: z.string(),
    type: dataTypeSchema,
    primaryKey: z.boolean(),
    unique: z.boolean(),
    nullable: z.boolean(),
    increment: z.boolean().or(z.null()).optional(),
    isArray: z.boolean().or(z.null()).optional(),
    createdAt: z.number(),
    characterMaximumLength: z.string().or(z.null()).optional(),
    precision: z.number().or(z.null()).optional(),
    scale: z.number().or(z.null()).optional(),
    default: z.string().or(z.null()).optional(),
    collation: z.string().or(z.null()).optional(),
    comments: z.string().or(z.null()).optional(),
    check: z.string().or(z.null()).optional(),
});

Helper Functions

generateDBFieldSuffix

Generates the type suffix for display (e.g., (255) for VARCHAR, (10, 2) for DECIMAL):

const generateDBFieldSuffix = (
    field: DBField,
    {
        databaseType,
        forceExtended = false,
        typeId,
    }: {
        databaseType?: DatabaseType;
        forceExtended?: boolean;
        typeId?: string;
    } = {}
): string => {
    let suffix = '';
    
    // Character maximum length types (e.g., VARCHAR)
    if (field.characterMaximumLength) {
        suffix = `(${field.characterMaximumLength})`;
    }
    
    // Precision and scale types (e.g., DECIMAL)
    if (field.precision && field.scale) {
        suffix = `(${field.precision}, ${field.scale})`;
    } else if (field.precision) {
        suffix = `(${field.precision})`;
    }
    
    // Add array notation if field is an array
    if (field.isArray && supportsArrayDataType(typeId ?? field.type.id, databaseType)) {
        suffix += '[]';
    }
    
    return suffix;
};

Examples

Primary Key Field

const idField: DBField = {
    id: 'field_id',
    name: 'id',
    type: { id: 'uuid', name: 'uuid' },
    primaryKey: true,
    unique: true,
    nullable: false,
    default: 'gen_random_uuid()',
    createdAt: Date.now()
};

Auto-Increment Field (PostgreSQL)

const serialField: DBField = {
    id: 'field_id',
    name: 'id',
    type: { id: 'serial', name: 'serial' },
    primaryKey: true,
    unique: true,
    nullable: false,
    increment: true,
    createdAt: Date.now()
};

VARCHAR Field with Length

const emailField: DBField = {
    id: 'field_email',
    name: 'email',
    type: { id: 'varchar', name: 'varchar' },
    primaryKey: false,
    unique: true,
    nullable: false,
    characterMaximumLength: '255',
    check: 'email LIKE \'%@%\'',
    createdAt: Date.now(),
    comments: 'User email address'
};

DECIMAL Field with Precision and Scale

const priceField: DBField = {
    id: 'field_price',
    name: 'price',
    type: { id: 'decimal', name: 'decimal' },
    primaryKey: false,
    unique: false,
    nullable: false,
    precision: 10,
    scale: 2,
    default: '0.00',
    check: 'price >= 0',
    createdAt: Date.now()
};

Array Field (PostgreSQL)

const tagsField: DBField = {
    id: 'field_tags',
    name: 'tags',
    type: { id: 'text', name: 'text' },
    primaryKey: false,
    unique: false,
    nullable: true,
    isArray: true, // text[] in PostgreSQL
    default: 'ARRAY[]::text[]',
    createdAt: Date.now()
};

Timestamp with Default

const createdAtField: DBField = {
    id: 'field_created_at',
    name: 'created_at',
    type: { id: 'timestamp', name: 'timestamp' },
    primaryKey: false,
    unique: false,
    nullable: false,
    default: 'CURRENT_TIMESTAMP',
    createdAt: Date.now()
};

Data Type Support by Database

Type	PostgreSQL	MySQL	SQL Server	SQLite
VARCHAR	✅	✅	✅	✅ (as TEXT)
INTEGER	✅	✅	✅	✅
DECIMAL	✅	✅	✅	✅ (as REAL)
BOOLEAN	✅	✅ (TINYINT)	✅ (BIT)	✅ (INTEGER)
UUID	✅	❌	✅ (UNIQUEIDENTIFIER)	❌
JSON	✅	✅	❌	❌
JSONB	✅	❌	❌	❌
Arrays	✅	❌	❌	❌

Related Types

• Table
• Index
• Database Types