Column_types

Chapter 7: Column Types & Options

Deep Dive into TypeORM Column Types

---

7.1 Column Type Overview

TypeORM supports various column types that map to database-specific types.

                    TypeORM Type System
    ================================================================================

                            TypeScript Type
                                  |
                    +-------------+-------------+
                    |             |             |
                    v             v             v
                string        number       boolean
                    |             |             |
                    v             v             v
              VARCHAR        INTEGER       BOOLEAN
              TEXT          BIGINT        TINYINT
              CHAR          DECIMAL       BIT
              UUID          FLOAT
              ENUM          DOUBLE
                    |             |
                    +------+------+
                           |
                           v
                    Database Column Type

    ================================================================================

---

7.2 String Column Types

VARCHAR

@Entity('users')
export class User {
  // Default VARCHAR(255)
  @Column()
  name: string;
  // SQL: name VARCHAR(255)

  // VARCHAR with specific length
  @Column({ length: 50 })
  shortCode: string;
  // SQL: shortCode VARCHAR(50)

  // VARCHAR with options
  @Column({
    type: 'varchar',
    length: 100,
    nullable: false,
    unique: true,
  })
  username: string;
  // SQL: username VARCHAR(100) NOT NULL UNIQUE
}

TEXT

@Entity('posts')
export class Post {
  // TEXT - unlimited length
  @Column({ type: 'text' })
  content: string;
  // SQL: content TEXT

  // TEXT with nullable
  @Column({ type: 'text', nullable: true })
  summary: string;
  // SQL: summary TEXT NULL

  // MEDIUMTEXT (MySQL)
  @Column({ type: 'mediumtext' })
  longContent: string;
  // SQL: longContent MEDIUMTEXT

  // LONGTEXT (MySQL)
  @Column({ type: 'longtext' })
  veryLongContent: string;
  // SQL: veryLongContent LONGTEXT
}

CHAR (Fixed Length)

@Entity('countries')
export class Country {
  // CHAR - fixed length, padded with spaces
  @Column({ type: 'char', length: 2 })
  code: string;
  // SQL: code CHAR(2)

  // National CHAR
  @Column({ type: 'nchar', length: 10 })
  nationalCode: string;
  // SQL: nationalCode NCHAR(10)
}

UUID

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity('users')
export class User {
  // Auto-generated UUID primary key
  @PrimaryGeneratedColumn('uuid')
  id: string;
  // SQL: id UUID PRIMARY KEY DEFAULT uuid_generate_v4()

  // UUID column (not primary)
  @Column({ type: 'uuid' })
  externalId: string;
  // SQL: externalId UUID

  // UUID with default
  @Column({ type: 'uuid', default: () => 'uuid_generate_v4()' })
  referenceId: string;
  // SQL: referenceId UUID DEFAULT uuid_generate_v4()
}

---

7.3 Numeric Column Types

Integer Types

@Entity('products')
export class Product {
  // INTEGER (default for number)
  @Column()
  quantity: number;
  // SQL: quantity INTEGER

  // INT (alias)
  @Column({ type: 'int' })
  stock: number;
  // SQL: stock INT

  // SMALLINT (2 bytes, -32768 to 32767)
  @Column({ type: 'smallint' })
  priority: number;
  // SQL: priority SMALLINT

  // TINYINT (1 byte, -128 to 127)
  @Column({ type: 'tinyint' })
  status: number;
  // SQL: status TINYINT

  // MEDIUMINT (3 bytes, MySQL)
  @Column({ type: 'mediumint' })
  mediumNumber: number;
  // SQL: mediumNumber MEDIUMINT

  // BIGINT (8 bytes, very large numbers)
  @Column({ type: 'bigint' })
  viewCount: number;
  // SQL: viewCount BIGINT
}

Decimal Types

@Entity('orders')
export class Order {
  // DECIMAL with precision and scale
  @Column({
    type: 'decimal',
    precision: 10,  // Total digits
    scale: 2,       // Digits after decimal
  })
  totalAmount: number;
  // SQL: totalAmount DECIMAL(10, 2)
  // Can store: 12345678.99

  // NUMERIC (same as DECIMAL)
  @Column({
    type: 'numeric',
    precision: 15,
    scale: 4,
  })
  preciseAmount: number;
  // SQL: preciseAmount NUMERIC(15, 4)

  // MONEY (PostgreSQL)
  @Column({ type: 'money' })
  price: number;
  // SQL: price MONEY
}

Floating Point Types

@Entity('measurements')
export class Measurement {
  // FLOAT (4 bytes)
  @Column({ type: 'float' })
  temperature: number;
  // SQL: temperature FLOAT

  // DOUBLE (8 bytes)
  @Column({ type: 'double' })
  preciseTemperature: number;
  // SQL: preciseTemperature DOUBLE

  // REAL (PostgreSQL)
  @Column({ type: 'real' })
  realNumber: number;
  // SQL: realNumber REAL

  // DOUBLE PRECISION (PostgreSQL)
  @Column({ type: 'double precision' })
  veryPrecise: number;
  // SQL: veryPrecise DOUBLE PRECISION
}

---

7.4 Boolean Column Type

@Entity('users')
export class User {
  // BOOLEAN (default for boolean)
  @Column()
  isActive: boolean;
  // SQL: isActive BOOLEAN

  // With default value
  @Column({ default: true })
  emailVerified: boolean;
  // SQL: emailVerified BOOLEAN DEFAULT TRUE

  // With nullable
  @Column({ nullable: true })
  hasAcceptedTerms: boolean;
  // SQL: hasAcceptedTerms BOOLEAN NULL

  // TINYINT as boolean (MySQL)
  @Column({ type: 'tinyint', default: 0 })
  isBlocked: boolean;
  // SQL: isBlocked TINYINT DEFAULT 0
}

---

7.5 Date & Time Column Types

@Entity('events')
export class Event {
  // TIMESTAMP (default for Date)
  @Column()
  eventTime: Date;
  // SQL: eventTime TIMESTAMP

  // TIMESTAMP WITH TIME ZONE
  @Column({ type: 'timestamptz' })
  eventTimeWithZone: Date;
  // SQL: eventTimeWithZone TIMESTAMPTZ

  // DATE (date only, no time)
  @Column({ type: 'date' })
  eventDate: Date;
  // SQL: eventDate DATE

  // TIME (time only, no date)
  @Column({ type: 'time' })
  startTime: Date;
  // SQL: startTime TIME

  // TIME WITH TIME ZONE
  @Column({ type: 'timetz' })
  startTimeWithZone: Date;
  // SQL: startTimeWithZone TIMETZ

  // DATETIME (MySQL)
  @Column({ type: 'datetime' })
  createdAt: Date;
  // SQL: createdAt DATETIME

  // YEAR (MySQL)
  @Column({ type: 'year' })
  year: number;
  // SQL: year YEAR
}

Special Date Columns

import {
  Entity,
  CreateDateColumn,
  UpdateDateColumn,
  DeleteDateColumn,
} from 'typeorm';

@Entity('users')
export class User {
  // Auto-set on INSERT
  @CreateDateColumn()
  createdAt: Date;
  // SQL: createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP

  // Auto-update on each UPDATE
  @UpdateDateColumn()
  updatedAt: Date;
  // SQL: updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

  // Soft delete - set on "delete"
  @DeleteDateColumn()
  deletedAt: Date;
  // SQL: deletedAt TIMESTAMP NULL
}

---

7.6 JSON Column Types

@Entity('users')
export class User {
  // JSON (stored as text, parsed on read)
  @Column({ type: 'json' })
  preferences: object;
  // SQL: preferences JSON

  // JSONB (PostgreSQL - binary format, faster)
  @Column({ type: 'jsonb' })
  metadata: Record<string, any>;
  // SQL: metadata JSONB

  // JSON with nullable
  @Column({ type: 'json', nullable: true })
  settings: object;
  // SQL: settings JSON NULL

  // Simple JSON (stored as string)
  @Column({ type: 'simple-json' })
  tags: string[];
  // SQL: tags TEXT
  // Stored as: '["tag1", "tag2"]'
}

JSON Querying (PostgreSQL)

// Query JSON column
const users = await userRepository
  .createQueryBuilder('user')
  .where("user.preferences->>'theme' = :theme", { theme: 'dark' })
  .getMany();

// Query JSONB column with operators
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.metadata @> :filter', { filter: { role: 'admin' } })
  .getMany();

---

7.7 Array Column Types (PostgreSQL)

@Entity('posts')
export class Post {
  // Integer array
  @Column({ type: 'int', array: true })
  ratings: number[];
  // SQL: ratings INT[]

  // Text array
  @Column({ type: 'text', array: true })
  tags: string[];
  // SQL: tags TEXT[]

  // UUID array
  @Column({ type: 'uuid', array: true })
  relatedIds: string[];
  // SQL: relatedIds UUID[]

  // VARCHAR array with length
  @Column({ type: 'varchar', length: 50, array: true })
  categories: string[];
  // SQL: categories VARCHAR(50)[]

  // Numeric array
  @Column({ type: 'numeric', array: true })
  coordinates: string[];
  // SQL: coordinates NUMERIC[]
}

Array Querying

// Check if array contains value
const posts = await postRepository
  .createQueryBuilder('post')
  .where(':tag = ANY(post.tags)', { tag: 'typescript' })
  .getMany();

// Check if array contains all values
const posts = await postRepository
  .createQueryBuilder('post')
  .where('post.tags @> :tags', { tags: ['typescript', 'typeorm'] })
  .getMany();

// Check if array overlaps with values
const posts = await postRepository
  .createQueryBuilder('post')
  .where('post.tags && :tags', { tags: ['typescript', 'nodejs'] })
  .getMany();

---

7.8 Enum Column Types

String Enums

enum UserRole {
  ADMIN = 'admin',
  USER = 'user',
  MODERATOR = 'moderator',
}

@Entity('users')
export class User {
  @Column({
    type: 'enum',
    enum: UserRole,
    default: UserRole.USER,
  })
  role: UserRole;
  // SQL: role ENUM('admin', 'user', 'moderator') DEFAULT 'user'
}

Numeric Enums

enum UserStatus {
  INACTIVE = 0,
  ACTIVE = 1,
  PENDING = 2,
}

@Entity('users')
export class User {
  @Column({
    type: 'enum',
    enum: UserStatus,
    default: UserStatus.PENDING,
  })
  status: UserStatus;
  // SQL: status INT DEFAULT 2
}

Enum with Custom Name (PostgreSQL)

enum Priority {
  LOW = 'low',
  MEDIUM = 'medium',
  HIGH = 'high',
}

@Entity('tasks')
export class Task {
  @Column({
    type: 'enum',
    enum: Priority,
    enumName: 'priority_enum',  // Creates custom type
    default: Priority.MEDIUM,
  })
  priority: Priority;
  // SQL:
  // CREATE TYPE priority_enum AS ENUM ('low', 'medium', 'high');
  // priority priority_enum DEFAULT 'medium'
}

---

7.9 Binary Column Types

@Entity('files')
export class File {
  // BLOB (Binary Large Object)
  @Column({ type: 'blob' })
  data: Buffer;
  // SQL: data BLOB

  // TINYBLOB (MySQL, max 255 bytes)
  @Column({ type: 'tinyblob' })
  smallData: Buffer;
  // SQL: smallData TINYBLOB

  // MEDIUMBLOB (MySQL, max 16MB)
  @Column({ type: 'mediumblob' })
  mediumData: Buffer;
  // SQL: mediumData MEDIUMBLOB

  // LONGBLOB (MySQL, max 4GB)
  @Column({ type: 'longblob' })
  largeData: Buffer;
  // SQL: largeData LONGBLOB

  // BYTEA (PostgreSQL)
  @Column({ type: 'bytea' })
  binaryData: Buffer;
  // SQL: binaryData BYTEA

  // VARBINARY with length
  @Column({ type: 'varbinary', length: 255 })
  shortBinary: Buffer;
  // SQL: shortBinary VARBINARY(255)
}

---

7.10 Spatial Column Types

@Entity('locations')
export class Location {
  // POINT
  @Column({
    type: 'point',
    spatialFeatureType: 'Point',
    srid: 4326,
  })
  coordinates: string;
  // SQL: coordinates POINT SRID 4326

  // GEOMETRY
  @Column({
    type: 'geometry',
    spatialFeatureType: 'Geometry',
    srid: 4326,
  })
  geometry: string;
  // SQL: geometry GEOMETRY SRID 4326

  // POLYGON
  @Column({
    type: 'polygon',
    spatialFeatureType: 'Polygon',
    srid: 4326,
  })
  area: string;
  // SQL: area POLYGON SRID 4326

  // LINESTRING
  @Column({
    type: 'linestring',
    spatialFeatureType: 'LineString',
    srid: 4326,
  })
  path: string;
  // SQL: path LINESTRING SRID 4326
}

---

7.11 Column Options Deep Dive

Common Options

@Entity('users')
export class User {
  @Column({
    // Column type
    type: 'varchar',

    // String length
    length: 100,

    // Allow NULL
    nullable: true,  // default: false

    // Default value
    default: 'guest',

    // Unique constraint
    unique: true,

    // Custom column name
    name: 'user_name',

    // Comment (for documentation)
    comment: 'The display name of the user',

    // Include in SELECT queries
    select: true,  // default: true

    // Include in INSERT
    insert: true,  // default: true

    // Include in UPDATE
    update: true,  // default: true

    // Read-only (never update)
    readonly: false,

    // Character set (MySQL)
    charset: 'utf8mb4',

    // Collation (MySQL)
    collation: 'utf8mb4_unicode_ci',
  })
  name: string;
}

Transformer Option

// Custom value transformer
class JsonTransformer {
  // Convert JS value to database value
  to(value: any): string {
    return JSON.stringify(value);
  }

  // Convert database value to JS value
  from(value: string): any {
    return JSON.parse(value);
  }
}

@Entity('users')
export class User {
  @Column({
    type: 'text',
    transformer: new JsonTransformer(),
  })
  preferences: Record<string, any>;
  // Stored as JSON string, parsed as object
}

// Date transformer
class DateTransformer {
  to(value: Date): string {
    return value?.toISOString() || null;
  }
  from(value: string): Date {
    return value ? new Date(value) : null;
  }
}

// Encrypt transformer
class EncryptTransformer {
  private key = 'secret-key';

  to(value: string): string {
    return encrypt(value, this.key);
  }
  from(value: string): string {
    return decrypt(value, this.key);
  }
}

@Entity('users')
export class User {
  @Column({
    type: 'varchar',
    transformer: new EncryptTransformer(),
  })
  sensitiveData: string;
}

---

7.12 Type Mapping Reference

                    TypeScript to SQL Type Mapping
    +------------------------------------------------------------------+
    |                                                                   |
    |    TypeScript Type    | Default SQL Type    | Notes                |
    |    -------------------|--------------------|----------------------|
    |    string            | VARCHAR(255)       | Default length 255   |
    |    number            | INTEGER            | Default integer      |
    |    boolean           | BOOLEAN            | TINYINT(1) in MySQL  |
    |    Date              | TIMESTAMP          | With time            |
    |    object            | JSON               | Parsed object        |
    |    Buffer            | BLOB               | Binary data          |
    |    string[]          | TEXT[]             | PostgreSQL only      |
    |    number[]          | INT[]              | PostgreSQL only      |
    |                                                                   |
    +------------------------------------------------------------------+

---

7.13 Database-Specific Types

PostgreSQL-Specific

@Entity('postgres_features')
export class PostgresFeature {
  // UUID with auto-generation
  @PrimaryGeneratedColumn('uuid')
  id: string;

  // JSONB (binary JSON)
  @Column({ type: 'jsonb' })
  data: object;

  // Arrays
  @Column({ type: 'int', array: true })
  numbers: number[];

  // TIMESTAMPTZ
  @Column({ type: 'timestamptz' })
  timestampWithZone: Date;

  // MONEY
  @Column({ type: 'money' })
  price: number;

  // CIDR (IP address)
  @Column({ type: 'cidr' })
  ipAddress: string;

  // INET (IP address)
  @Column({ type: 'inet' })
  network: string;

  // MACADDR (MAC address)
  @Column({ type: 'macaddr' })
  macAddress: string;

  // TSVECTOR (full-text search)
  @Column({ type: 'tsvector' })
  searchVector: string;
}

MySQL-Specific

@Entity('mysql_features')
export class MySqlFeature {
  // TINYINT
  @Column({ type: 'tinyint' })
  tinyNumber: number;

  // MEDIUMINT
  @Column({ type: 'mediumint' })
  mediumNumber: number;

  // YEAR
  @Column({ type: 'year' })
  year: number;

  // MEDIUMTEXT
  @Column({ type: 'mediumtext' })
  mediumText: string;

  // LONGTEXT
  @Column({ type: 'longtext' })
  longText: string;

  // ENUM
  @Column({ type: 'enum', enum: ['a', 'b', 'c'] })
  status: string;

  // SET
  @Column({ type: 'set', enum: ['a', 'b', 'c'] })
  options: string;
}

---

7.14 Summary

                    Column Types Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Category        | Types                                        |
    |    ----------------|---------------------------------------------  |
    |    String          | varchar, text, char, uuid, enum              |
    |    Number          | int, bigint, smallint, decimal, float, double|
    |    Boolean         | boolean, tinyint                             |
    |    Date/Time       | timestamp, date, time, timestamptz           |
    |    JSON            | json, jsonb, simple-json                     |
    |    Binary          | blob, bytea, varbinary                       |
    |    Array           | int[], text[], varchar[] (PostgreSQL)        |
    |    Spatial         | point, geometry, polygon, linestring         |
    |                                                                   |
    +------------------------------------------------------------------+

---

Next Chapter

Chapter 8: Primary Keys & Generated Values

---

Last Updated: February 2026