DDL Statements

Data Definition Language (DDL) statements define and modify database schema in RaisinDB.

CREATE NODETYPE

Define a new node type schema.

Syntax

CREATE NODETYPE type_name (
    column_name data_type [ DEFAULT default_value ] [ NOT NULL ],
    ...
);

Examples

-- Simple node type
CREATE NODETYPE page (
    title TEXT NOT NULL,
    content TEXT,
    status TEXT DEFAULT 'draft'
);

-- Node type with various data types
CREATE NODETYPE product (
    name TEXT NOT NULL,
    price DOUBLE,
    stock_count INT,
    active BOOLEAN DEFAULT true,
    metadata JSONB
);

-- Node type with hierarchical and spatial data
CREATE NODETYPE store (
    name TEXT NOT NULL,
    address TEXT,
    location GEOMETRY,
    opening_hours JSONB,
    parent_store_path PATH
);

Compound Indexes

Add multi-column indexes to a NodeType for efficient queries on property combinations:

CREATE NODETYPE 'type_name' (
    PROPERTIES (
        column_name data_type,
        ...
    )
    COMPOUND_INDEX 'index_name' ON (
        column_name [ ASC | DESC ],
        ...
    )
)

A NodeType can have multiple compound indexes:

CREATE NODETYPE 'myapp:Article' (
    PROPERTIES (
        title String NOT NULL,
        category String,
        status String DEFAULT 'draft',
        priority Integer
    )
    COMPOUND_INDEX 'idx_category_status_created' ON (
        category,
        status,
        __created_at DESC
    )
    COMPOUND_INDEX 'idx_status_priority' ON (
        status,
        priority DESC
    )
)

Index columns can be any property defined on the NodeType, plus these system properties:

System Column	Type	Description
__node_type	String	The node's type name
__created_at	Timestamp	Node creation time
__updated_at	Timestamp	Last modification time

Sort direction — each column can specify ASC (default) or DESC. This matters especially for timestamp columns: __created_at DESC means newest-first in a forward index scan.

Column types are inferred automatically from the property type:

Property Type	Index Column Type	Encoding
TEXT	String	Lexicographic
INT, BIGINT	Integer	Numeric
BOOLEAN	Boolean	Binary
__created_at, __updated_at	Timestamp	Direction-aware

How the query planner uses them — the planner matches equality conditions on leading columns, with an optional ORDER BY on the trailing column:

-- ✅ Uses idx_category_status_created: prefix match + ORDER BY
SELECT * FROM 'default'
WHERE properties->>'category'::String = 'tech'
  AND properties->>'status'::String = 'published'
ORDER BY __created_at DESC
LIMIT 20;

-- ✅ Uses idx_category_status_created: partial prefix
SELECT * FROM 'default'
WHERE properties->>'category'::String = 'tech';

-- ❌ Cannot use index: skips leading column (category)
SELECT * FROM 'default'
WHERE properties->>'status'::String = 'published';

When you add a compound index to a NodeType that already has data, RaisinDB automatically schedules a background job to build the index from existing nodes.

CREATE ARCHETYPE

Define an archetype schema (template for node types).

Syntax

CREATE ARCHETYPE archetype_name (
    column_name data_type [ DEFAULT default_value ] [ NOT NULL ],
    ...
);

Examples

-- Base archetype for content
CREATE ARCHETYPE content (
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'draft',
    published_at TIMESTAMPTZ
);

-- Archetype with metadata
CREATE ARCHETYPE searchable (
    search_vector TSVECTOR,
    keywords ARRAY[TEXT],
    category TEXT
);

CREATE MIXIN

Define a mixin — a reusable property set composed into NodeTypes. See Using Mixins for the full guide.

Syntax

CREATE MIXIN 'namespace:Name'
    [ DESCRIPTION 'text' ]
    [ ICON 'icon-name' ]
    PROPERTIES ( name Type [ MODIFIERS ], ... );

Examples

CREATE MIXIN 'myapp:SEO'
  DESCRIPTION 'Search engine metadata'
  PROPERTIES (
    meta_title String,
    meta_description String,
    canonical_url String
  );

CREATE MIXIN 'myapp:Timestamps'
  PROPERTIES (
    created_at Date REQUIRED,
    updated_at Date REQUIRED
  );

Compose mixins into a NodeType with the MIXINS (...) clause:

CREATE NODETYPE 'myapp:Article'
  MIXINS ('myapp:SEO', 'myapp:Timestamps')
  PROPERTIES (title String REQUIRED);

Mixins are applied in order (later mixins win on property conflicts), after the EXTENDS parent and before the NodeType's own properties.

CREATE ELEMENTTYPE

Define an element type (used for array or collection elements).

Syntax

CREATE ELEMENTTYPE type_name (
    column_name data_type [ DEFAULT default_value ] [ NOT NULL ],
    ...
);

Examples

-- Element type for tags
CREATE ELEMENTTYPE tag (
    name TEXT NOT NULL,
    color TEXT,
    description TEXT
);

-- Element type for address
CREATE ELEMENTTYPE address (
    street TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT,
    location GEOMETRY
);

ALTER Statements

Modify existing schema definitions.

ALTER NODETYPE

-- Add column
ALTER NODETYPE page
ADD COLUMN author TEXT;

-- Add column with default
ALTER NODETYPE page
ADD COLUMN view_count INT DEFAULT 0;

-- Drop column
ALTER NODETYPE page
DROP COLUMN legacy_field;

-- Modify column type
ALTER NODETYPE page
ALTER COLUMN priority TYPE BIGINT;

-- Set default value
ALTER NODETYPE page
ALTER COLUMN status SET DEFAULT 'draft';

-- Remove default value
ALTER NODETYPE page
ALTER COLUMN status DROP DEFAULT;

-- Set NOT NULL constraint
ALTER NODETYPE page
ALTER COLUMN title SET NOT NULL;

-- Remove NOT NULL constraint
ALTER NODETYPE page
ALTER COLUMN description DROP NOT NULL;

ALTER ARCHETYPE

-- Add column to archetype
ALTER ARCHETYPE content
ADD COLUMN featured BOOLEAN DEFAULT false;

-- Modify archetype column
ALTER ARCHETYPE content
ALTER COLUMN status TYPE TEXT;

ALTER MIXIN

-- Add a property to a mixin
ALTER MIXIN 'myapp:SEO' ADD PROPERTY og_image String;

-- Remove a property from a mixin
ALTER MIXIN 'myapp:SEO' DROP PROPERTY canonical_url;

ALTER ELEMENTTYPE

-- Add column to element type
ALTER ELEMENTTYPE tag
ADD COLUMN priority INT;

-- Modify element type column
ALTER ELEMENTTYPE tag
ALTER COLUMN name SET NOT NULL;

DROP Statements

Remove schema definitions.

DROP NODETYPE

-- Drop node type
DROP NODETYPE old_type;

-- Drop if exists (no error if missing)
DROP NODETYPE IF EXISTS temporary_type;

DROP ARCHETYPE

-- Drop archetype
DROP ARCHETYPE legacy_archetype;

-- Drop if exists
DROP ARCHETYPE IF EXISTS old_archetype;

DROP MIXIN

-- Drop a mixin
DROP MIXIN 'myapp:SEO';

DROP ELEMENTTYPE

-- Drop element type
DROP ELEMENTTYPE unused_element;

-- Drop if exists
DROP ELEMENTTYPE IF EXISTS temp_element;

Data Types

Available data types for schema definitions:

Numeric Types

INT           -- 32-bit integer
BIGINT        -- 64-bit integer
DOUBLE        -- 64-bit floating point

Text Types

TEXT          -- UTF-8 string
UUID          -- UUID string
BOOLEAN       -- true/false

Temporal Types

TIMESTAMPTZ   -- Timestamp with timezone (UTC)
INTERVAL      -- Time interval/duration

RaisinDB-Specific Types

PATH              -- Hierarchical path
JSONB             -- JSON data
VECTOR(n)         -- n-dimensional vector for embeddings
GEOMETRY          -- GeoJSON geometry (PostGIS compatible)

Full-Text Types

TSVECTOR      -- Full-text search document
TSQUERY       -- Full-text search query

Collection Types

ARRAY[T]      -- Array of type T
              -- Example: ARRAY[TEXT], ARRAY[INT]

Nullable Modifier

All types can be nullable (default) or NOT NULL:

column_name TEXT              -- Nullable
column_name TEXT NOT NULL     -- Not nullable

Constraints

NOT NULL Constraint

CREATE NODETYPE page (
    title TEXT NOT NULL,
    content TEXT
);

DEFAULT Values

CREATE NODETYPE page (
    status TEXT DEFAULT 'draft',
    created_count INT DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Schema Inheritance

Node types can inherit from archetypes:

-- Define archetype
CREATE ARCHETYPE content (
    title TEXT NOT NULL,
    status TEXT DEFAULT 'draft'
);

-- Node type inheriting from archetype
CREATE NODETYPE article INHERITS content (
    author TEXT,
    content TEXT,
    category TEXT
);

Examples

Blog Schema

-- Base content archetype
CREATE ARCHETYPE content (
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    status TEXT DEFAULT 'draft',
    published_at TIMESTAMPTZ
);

-- Blog post node type
CREATE NODETYPE post INHERITS content (
    author TEXT NOT NULL,
    content TEXT,
    excerpt TEXT,
    tags ARRAY[TEXT],
    view_count INT DEFAULT 0,
    search_vector TSVECTOR
);

-- Category node type
CREATE NODETYPE category (
    name TEXT NOT NULL,
    description TEXT,
    color TEXT
);

-- Tag element type
CREATE ELEMENTTYPE tag (
    name TEXT NOT NULL,
    slug TEXT NOT NULL
);

E-commerce Schema

-- Product node type
CREATE NODETYPE product (
    name TEXT NOT NULL,
    sku TEXT NOT NULL,
    description TEXT,
    price DOUBLE NOT NULL,
    stock INT DEFAULT 0,
    active BOOLEAN DEFAULT true,
    metadata JSONB,
    images ARRAY[TEXT]
);

-- Store location node type
CREATE NODETYPE store (
    name TEXT NOT NULL,
    address TEXT,
    phone TEXT,
    location GEOMETRY,
    opening_hours JSONB
);

-- Order node type
CREATE NODETYPE order (
    order_number TEXT NOT NULL,
    customer_id UUID NOT NULL,
    total DOUBLE NOT NULL,
    status TEXT DEFAULT 'pending',
    items JSONB,
    shipping_address JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Document Management Schema

-- Document archetype
CREATE ARCHETYPE document (
    title TEXT NOT NULL,
    description TEXT,
    file_type TEXT,
    file_size BIGINT,
    search_vector TSVECTOR
);

-- Contract node type
CREATE NODETYPE contract INHERITS document (
    contract_number TEXT NOT NULL,
    party_a TEXT NOT NULL,
    party_b TEXT NOT NULL,
    start_date TIMESTAMPTZ,
    end_date TIMESTAMPTZ,
    value DOUBLE,
    metadata JSONB
);

-- Invoice node type
CREATE NODETYPE invoice INHERITS document (
    invoice_number TEXT NOT NULL,
    customer_id UUID NOT NULL,
    amount DOUBLE NOT NULL,
    due_date TIMESTAMPTZ,
    paid BOOLEAN DEFAULT false,
    line_items JSONB
);

Branch DDL

For branch management statements (CREATE BRANCH, ALTER BRANCH, DROP BRANCH, MERGE BRANCH), see Branch Statements.

Notes

• Schema changes may require data migration
• Dropping a node type removes all nodes of that type
• ALTER operations may fail if incompatible with existing data
• System pseudo-columns (__id, __path, etc.) are automatically included
• NOT NULL constraints are validated on INSERT and UPDATE
• DEFAULT values apply only to INSERT when column is omitted
• Type changes may require explicit CAST of existing data
• In RaisinDB, the workspace name is used as the table name in SQL queries