INSERT Statement

The INSERT statement adds new nodes to RaisinDB.

Workspace = Table Name

The table name in INSERT refers to the workspace name. For example, INSERT INTO products inserts into the products workspace.

Syntax

INSERT INTO workspace_name (path, node_type, properties)
VALUES (path_value, type_value, properties_jsonb)
[, (path_value, type_value, properties_jsonb) ...]

Basic INSERT

Insert a node with path, node type, and properties:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/getting-started',
    'Article',
    '{"title": "Getting Started", "content": "Welcome to RaisinDB", "status": "published"}'
);

With Explicit ID

You can optionally specify the node ID:

INSERT INTO default (id, path, node_type, properties)
VALUES (
    '01HQ3K9V5NWCR3KXM2Y7P8G6ZT',
    '/content/blog/my-post',
    'Article',
    '{"title": "My Post", "content": "Hello world", "status": "draft"}'
);

Multiple Rows

Insert multiple nodes in one statement:

INSERT INTO default (path, node_type, properties)
VALUES
    ('/content/tags/database', 'Tag', '{"name": "database", "color": "blue"}'),
    ('/content/tags/tutorial', 'Tag', '{"name": "tutorial", "color": "green"}'),
    ('/content/tags/reference', 'Tag', '{"name": "reference", "color": "red"}');

Targeting a Branch

By default an INSERT writes to the current/connection branch. To insert into a different branch in a single statement, add a __branch pseudo-column to the column list — the analog of the WHERE __branch = '…' override that SELECT, UPDATE, and DELETE already support.

-- Write this node to the `staging` branch, regardless of the current branch
INSERT INTO default (__branch, path, node_type, properties)
VALUES ('staging', '/content/blog/draft', 'Article', '{"title": "Draft"}');

This is what lets a server-side function read one branch and write another in a single execution — for example, copy a node from main into another branch:

-- inside a function: read main, then insert into another branch
SELECT * FROM default WHERE __branch = 'main' AND path = '/content/blog/post';
INSERT INTO default (__branch, path, node_type, properties)
VALUES ('staging', '/content/blog/post', 'Article', '{ ... }');

Rules:

• __branch must be a string literal and the same value for every row in the statement.
• It requires an explicit column list (the all-columns shorthand has nowhere to place it) and is removed before the row is stored — it is not a property.
• The override applies in auto-commit mode. Inside an explicit BEGIN … COMMIT the branch is fixed at BEGIN; use USE BRANCH instead there.

See the branches reference for the client-side onBranch equivalent.

NULL Values

Properties that aren't specified in the JSON are simply absent:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/untitled',
    'Article',
    '{"title": "Untitled", "status": "draft"}'
);

To explicitly set a JSON null:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/untitled',
    'Article',
    '{"title": "Untitled", "description": null, "status": "draft"}'
);

JSON Data

Build properties using JSON syntax:

-- Nested objects
INSERT INTO default (path, node_type, properties)
VALUES (
    '/products/widget-1',
    'Product',
    '{"name": "Widget", "metadata": {"color": "blue", "size": "large"}, "tags": ["new", "featured"]}'
);

-- Using JSONB cast
INSERT INTO default (path, node_type, properties)
VALUES (
    '/products/gadget-1',
    'Product',
    '{"name": "Gadget", "price": 29.99}'::JSONB
);

Using JSONB_SET to build properties:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/products/gadget-2',
    'Product',
    JSONB_SET('{"name": "Gadget"}', '{color}', '"red"')
);

Timestamps

Include timestamp values in properties:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/events/launch',
    'Event',
    '{"name": "Launch", "event_time": "2024-01-15T10:00:00Z"}'
);

note

System columns created_at and updated_at are set automatically and cannot be specified in INSERT.

Geospatial Data

Geometry values use typed columns defined in the schema. The geometry data is stored separately from the JSON properties:

INSERT INTO default (path, node_type, properties, point)
VALUES (
    '/locations/san-francisco',
    'Location',
    '{"name": "San Francisco"}',
    ST_POINT(-122.4194, 37.7749)
);

Arrays

Include arrays in properties:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/sql-guide',
    'Article',
    '{"title": "SQL Guide", "tags": ["sql", "database", "tutorial"]}'
);

Vectors

Vector embeddings use typed columns defined in the schema. The vector data is stored separately from the JSON properties:

INSERT INTO default (path, node_type, properties, embedding)
VALUES (
    '/documents/doc-1',
    'Document',
    '{"title": "Document 1"}',
    ARRAY[0.1, 0.2, 0.3]::VECTOR(3)
);

Full-Text Search

Full-text search is schema-driven. Mark properties with the FULLTEXT keyword in the schema definition, and RaisinDB automatically indexes their content. No special column is needed at insert time — just insert the text properties normally:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/db-tutorial',
    'Article',
    '{"title": "Database Tutorial", "content": "A tutorial for beginners"}'
);

tip

Properties marked as FULLTEXT in the schema are automatically indexed for search. You can then query them using search_vector @@ TO_TSQUERY(...) or the fulltext_search() function.

Expressions in VALUES

Use expressions when building properties:

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/blog/example',
    'Article',
    JSONB_SET(
        '{"title": "Example Page", "status": "draft"}',
        '{slug}',
        TO_JSON(LOWER('Example Page'))
    )
);

Examples

Basic Node Insert

INSERT INTO default (path, node_type, properties)
VALUES (
    '/content/guides/raisindb-intro',
    'Article',
    '{
        "title": "Introduction to RaisinDB",
        "content": "RaisinDB is a hierarchical document database...",
        "status": "published",
        "author": "admin"
    }'
);

Insert Product with Metadata

INSERT INTO default (path, node_type, properties)
VALUES (
    '/products/premium-widget',
    'Product',
    '{
        "name": "Premium Widget",
        "price": 99.99,
        "metadata": {
            "color": "blue",
            "weight": 500,
            "features": ["waterproof", "durable"]
        }
    }'
);

Insert Location

INSERT INTO default (path, node_type, properties, location)
VALUES (
    '/stores/downtown',
    'Store',
    '{
        "name": "Downtown Store",
        "address": "123 Main St"
    }',
    ST_POINT(-122.4194, 37.7749)
);

Insert Event

INSERT INTO default (path, node_type, properties)
VALUES (
    '/events/product-launch',
    'Event',
    '{
        "name": "Product Launch",
        "description": "New product release",
        "event_date": "2024-06-15T14:00:00Z"
    }'
);

Batch Insert

INSERT INTO default (path, node_type, properties)
VALUES
    ('/content/tags/database', 'Tag', '{"name": "Database", "slug": "database", "description": "Database related content"}'),
    ('/content/tags/tutorial', 'Tag', '{"name": "Tutorial", "slug": "tutorial", "description": "Tutorial content"}'),
    ('/content/tags/guide', 'Tag', '{"name": "Guide", "slug": "guide", "description": "Guide content"}'),
    ('/content/tags/reference', 'Tag', '{"name": "Reference", "slug": "reference", "description": "Reference documentation"}');

Notes

• System columns (id, path, created_at, updated_at, version) are auto-generated
• The id column can optionally be provided; if omitted, a ULID is generated
• The node_type determines the schema used for validation (if schema validation is enabled)
• All user data goes in the properties JSONB column
• Constraint violations from schema validation will cause the INSERT to fail
• Invalid JSON in properties will cause an error