Database Object Types

Overview

Poge provides comprehensive TypeScript interfaces for representing PostgreSQL database objects including schemas, tables, views, columns, indexes, and constraints.

Core Object Types

DatabaseInfo

Represents a database with its schema structure. From /types/database.ts:1-4:

export interface DatabaseInfo {
  name: string
  schemas: SchemaInfo[]
}

name

string

required

Database name

schemas

SchemaInfo[]

required

Array of schemas in the database

Example:

const db: DatabaseInfo = {
  name: "myapp_production",
  schemas: [
    { name: "public", tables: [...] },
    { name: "analytics", tables: [...] }
  ]
}

SchemaInfo

Represents a database schema containing tables and views. From /types/database.ts:6-9:

export interface SchemaInfo {
  name: string
  tables: TableInfo[]
}

name

string

required

Schema name (e.g., "public", "myschema")

tables

TableInfo[]

required

Array of tables and views in the schema

TableInfo

Represents a table, view, or materialized view. From /types/database.ts:11-19:

export interface TableInfo {
  name: string
  type: "table" | "view" | "materialized_view"
  schema: string
  rowCount?: number
  columnCount?: number
  size?: string
  createdAt?: Date
}

name

string

required

Table or view name

type

string

required

Object type:

"table" - Regular table
"view" - View
"materialized_view" - Materialized view

schema

string

required

Parent schema name

rowCount

number

Estimated row count (from pg_class.reltuples)

columnCount

number

Number of columns in the table

size

string

Table size as human-readable string (e.g., "1.5 MB")

createdAt

Date

Table creation timestamp

Example:

const table: TableInfo = {
  name: "users",
  type: "table",
  schema: "public",
  rowCount: 15000,
  columnCount: 8,
  size: "2.3 MB",
  createdAt: new Date("2024-01-15T10:00:00Z")
}

ColumnInfo

Represents a table column with its metadata. From /types/database.ts:21-27:

export interface ColumnInfo {
  name: string
  dataType: string
  isNullable: boolean
  defaultValue?: string
  isPrimaryKey?: boolean
}

name

string

required

Column name

dataType

string

required

PostgreSQL data type (e.g., "integer", "varchar", "timestamp without time zone")

isNullable

boolean

required

Whether the column accepts NULL values

defaultValue

string

Default value expression (e.g., "CURRENT_TIMESTAMP", "nextval('users_id_seq')"))

isPrimaryKey

boolean

Whether the column is part of the primary key

Example:

const columns: ColumnInfo[] = [
  {
    name: "id",
    dataType: "integer",
    isNullable: false,
    defaultValue: "nextval('users_id_seq'::regclass)",
    isPrimaryKey: true
  },
  {
    name: "email",
    dataType: "character varying",
    isNullable: false,
    isPrimaryKey: false
  },
  {
    name: "created_at",
    dataType: "timestamp without time zone",
    isNullable: true,
    defaultValue: "CURRENT_TIMESTAMP"
  }
]

TableData

Represents paginated table data with column metadata. From /types/database.ts:29-35:

export interface TableData {
  columns: ColumnInfo[]
  rows: Record<string, any>[]
  totalRows: number
  currentPage: number
  pageSize: number
}

columns

ColumnInfo[]

required

Column metadata

rows

Record<string, any>[]

required

Array of row objects (column name -> value)

totalRows

number

required

Total number of rows in the table

currentPage

number

required

Current page number (1-indexed)

pageSize

number

required

Number of rows per page

Example:

const tableData: TableData = {
  columns: [
    { name: "id", dataType: "integer", isNullable: false, isPrimaryKey: true },
    { name: "name", dataType: "varchar", isNullable: false }
  ],
  rows: [
    { id: 1, name: "Alice" },
    { id: 2, name: "Bob" }
  ],
  totalRows: 150,
  currentPage: 1,
  pageSize: 50
}

SelectedTable

Represents the currently selected table in the UI. From /types/database.ts:37-44:

export interface SelectedTable {
  serverId: string
  serverName: string
  database: string
  schema: string
  table: string
  type: "table" | "view" | "materialized_view"
}

serverId

string

required

Server connection ID

serverName

string

required

Human-readable server name

database

string

required

Database name

schema

string

required

Schema name

table

string

required

Table or view name

type

string

required

Object type ("table", "view", "materialized_view")

Schema Definition Types

ColumnDefinition

Extended column definition with full metadata. From /types/schema.ts:12-24:

export interface ColumnDefinition {
  name: string
  dataType: string
  length?: number
  precision?: number
  scale?: number
  isNullable: boolean
  defaultValue?: string
  isPrimaryKey: boolean
  isUnique: boolean
  isAutoIncrement: boolean
  comment?: string
}

name

string

required

Column name

dataType

string

required

PostgreSQL data type

length

number

Character length for VARCHAR, CHAR types

precision

number

Numeric precision (total digits)

scale

number

Numeric scale (decimal places)

isNullable

boolean

required

Whether NULL values are allowed

defaultValue

string

Default value expression

isPrimaryKey

boolean

required

Whether column is part of primary key

isUnique

boolean

required

Whether column has UNIQUE constraint

isAutoIncrement

boolean

required

Whether column auto-increments (SERIAL, IDENTITY)

comment

string

Column comment/description

IndexDefinition

Represents a database index. From /types/schema.ts:26-32:

export interface IndexDefinition {
  name: string
  columns: string[]
  isUnique: boolean
  type: "btree" | "hash" | "gin" | "gist"
  condition?: string
}

name

string

required

Index name

columns

string[]

required

Array of column names in the index

isUnique

boolean

required

Whether index enforces uniqueness

type

string

required

Index type: "btree", "hash", "gin", "gist"

condition

string

Partial index condition (WHERE clause)

Example:

const index: IndexDefinition = {
  name: "idx_users_email",
  columns: ["email"],
  isUnique: true,
  type: "btree"
}

ConstraintDefinition

Represents a table constraint. From /types/schema.ts:34-43:

export interface ConstraintDefinition {
  name: string
  type: "primary_key" | "foreign_key" | "unique" | "check"
  columns: string[]
  referencedTable?: string
  referencedColumns?: string[]
  onUpdate?: "cascade" | "restrict" | "set_null" | "set_default"
  onDelete?: "cascade" | "restrict" | "set_null" | "set_default"
  checkExpression?: string
}

name

string

required

Constraint name

type

string

required

Constraint type:

"primary_key" - Primary key
"foreign_key" - Foreign key reference
"unique" - Unique constraint
"check" - Check constraint

columns

string[]

required

Columns involved in the constraint

referencedTable

string

Referenced table for foreign keys

referencedColumns

string[]

Referenced columns for foreign keys

onUpdate

string

Foreign key ON UPDATE action: "cascade", "restrict", "set_null", "set_default"

onDelete

string

Foreign key ON DELETE action: "cascade", "restrict", "set_null", "set_default"

checkExpression

string

CHECK constraint expression

Example:

const constraints: ConstraintDefinition[] = [
  {
    name: "users_pkey",
    type: "primary_key",
    columns: ["id"]
  },
  {
    name: "fk_orders_user_id",
    type: "foreign_key",
    columns: ["user_id"],
    referencedTable: "users",
    referencedColumns: ["id"],
    onUpdate: "cascade",
    onDelete: "restrict"
  },
  {
    name: "check_age_positive",
    type: "check",
    columns: ["age"],
    checkExpression: "age > 0"
  }
]

TableDefinition

Complete table definition with columns, indexes, and constraints. From /types/schema.ts:45-52:

export interface TableDefinition {
  name: string
  schema: string
  columns: ColumnDefinition[]
  indexes: IndexDefinition[]
  constraints: ConstraintDefinition[]
  comment?: string
}

ViewDefinition

View definition with SQL. From /types/schema.ts:54-59:

export interface ViewDefinition {
  name: string
  schema: string
  definition: string
  comment?: string
}

DatabaseDefinition

Database metadata. From /types/schema.ts:61-68:

export interface DatabaseDefinition {
  name: string
  encoding: string
  collation: string
  template?: string
  owner?: string
  comment?: string
}

SchemaDefinition

Schema metadata. From /types/schema.ts:70-75:

export interface SchemaDefinition {
  name: string
  database: string
  owner?: string
  comment?: string
}

Use Cases

Building a Schema Explorer

function renderSchemaTree(db: DatabaseInfo) {
  return db.schemas.map(schema => ({
    label: schema.name,
    children: schema.tables.map(table => ({
      label: `${table.name} (${table.type})`,
      rowCount: table.rowCount,
      icon: table.type === 'table' ? 'table' : 'view'
    }))
  }));
}

Generating CREATE TABLE DDL

function generateCreateTable(def: TableDefinition): string {
  const columns = def.columns.map(col => {
    let ddl = `  ${col.name} ${col.dataType}`;
    if (col.length) ddl += `(${col.length})`;
    if (!col.isNullable) ddl += ' NOT NULL';
    if (col.defaultValue) ddl += ` DEFAULT ${col.defaultValue}`;
    return ddl;
  });
  
  return `CREATE TABLE ${def.schema}.${def.name} (\n${columns.join(',\n')}\n);`;
}

Overview

Endpoints

Types

​Overview

​Core Object Types

​DatabaseInfo

​SchemaInfo

​TableInfo

​ColumnInfo

​TableData

​SelectedTable

​Schema Definition Types

​ColumnDefinition

​IndexDefinition

​ConstraintDefinition

​TableDefinition

​ViewDefinition

​DatabaseDefinition

​SchemaDefinition

​Use Cases

​Building a Schema Explorer

​Generating CREATE TABLE DDL

​Related Types

Overview

Core Object Types

DatabaseInfo

SchemaInfo

TableInfo

ColumnInfo

TableData

SelectedTable

Schema Definition Types

ColumnDefinition

IndexDefinition

ConstraintDefinition

TableDefinition

ViewDefinition

DatabaseDefinition

SchemaDefinition

Use Cases

Building a Schema Explorer

Generating CREATE TABLE DDL

Related Types