dml

DML Operations Example

This example demonstrates DML (Data Manipulation Language) support in an Airport Flight server, enabling data modification via INSERT, UPDATE, and DELETE statements with full transaction support including ROLLBACK.

Features

• INSERT: Add new rows to tables
• UPDATE: Modify existing rows by row ID
• DELETE: Remove rows by row ID
• Transactions: BEGIN, COMMIT, ROLLBACK support
• Row IDs: Automatic row identification for UPDATE/DELETE

Prerequisites

• Go 1.25+
• DuckDB 1.4+ (for client testing)
• Airport extension for DuckDB

Running the Server

Start the DML-enabled Flight server:

go run main.go

The server will start on localhost:50051 and output:

Airport DML server listening on :50051
Example catalog contains:
  - Schema: test
    - Table: users (writable with transaction support)

Testing with DuckDB

Start DuckDB and connect to the server:

duckdb

-- Install and load Airport extension
INSTALL airport FROM community;
LOAD airport;

-- Connect to the DML server
ATTACH '' AS demo (TYPE airport, LOCATION 'grpc://localhost:50051');

Basic DML Operations

-- Insert data
INSERT INTO demo.test.users (id, name, email) VALUES
    (1, 'Alice', 'alice@example.com'),
    (2, 'Bob', 'bob@example.com'),
    (3, 'Charlie', 'charlie@example.com');

-- View the data
SELECT * FROM demo.test.users;

-- Update a row
UPDATE demo.test.users SET name = 'Alicia' WHERE id = 1;

-- Delete a row
DELETE FROM demo.test.users WHERE id = 3;

-- Verify changes
SELECT * FROM demo.test.users;

Transaction Support

The transaction in the DuckDB works differently than traditional databases. (See the documentation for more details: https://airport.query.farm/transactions.html)

If a DML operation returns an error, the entire transaction is rolled back automatically, in otherwise it will be committed. The DuckDB client manages the transaction state, but not interacts with the airport backend for COMMIT/ROLLBACK commands.

You can get current transaction ID from the context in the table methods and use it to track the changes made within the transaction.

txId, ok := catalog.TransactionIDFromContext(ctx)

-- Insert data within transaction
INSERT INTO demo.test.users (id, name, email) 
VALUES (100, 'TxUser', 'tx@example.com');

-- Data is visible within the transaction
SELECT * FROM demo.test.users;

-- Duplicate ID to cause error
INSERT INTO demo.test.users (id, name, email) 
VALUES 
    (101, 'TxUser2', 'tx@example.com'),
    (101, 'Duplicate', 'tx@example.com');

-- TxUser2 is gone
SELECT * FROM demo.test.users;

Implementation Details

This example implements DML interfaces from the catalog package:

InsertableTable

type InsertableTable interface {
    Table
    Insert(ctx context.Context, rows array.RecordReader) (*DMLResult, error)
}

UpdatableTable

type UpdatableTable interface {
    Table
    Update(ctx context.Context, rowIDs []int64, rows array.RecordReader) (*DMLResult, error)
}

DeletableTable

type DeletableTable interface {
    Table
    Delete(ctx context.Context, rowIDs []int64) (*DMLResult, error)
}

TransactionManager

type TransactionManager interface {
    BeginTransaction(ctx context.Context) (string, error)
    CommitTransaction(ctx context.Context, txID string) error
    RollbackTransaction(ctx context.Context, txID string) error
    GetTransactionStatus(ctx context.Context, txID string) (TransactionState, bool)
}

Row ID Support

For UPDATE and DELETE operations, tables must expose a row ID column:

// Schema with rowid - required for UPDATE/DELETE
rowidMeta := arrow.NewMetadata([]string{"is_rowid"}, []string{"true"})
schema := arrow.NewSchema([]arrow.Field{
    {Name: "rowid", Type: arrow.PrimitiveTypes.Int64, Nullable: false, Metadata: rowidMeta},
    {Name: "id", Type: arrow.PrimitiveTypes.Int64, Nullable: false},
    {Name: "name", Type: arrow.BinaryTypes.String, Nullable: false},
}, nil)

The is_rowid metadata tells DuckDB which column to use for identifying rows during UPDATE/DELETE.

DMLResult

DML operations return a result with affected row count:

type DMLResult struct {
    AffectedRows  int64
    ReturningData array.RecordReader  // For INSERT...RETURNING
}

Next Steps

• Try the ddl example for CREATE/DROP/ALTER operations
• Try the basic example for simple read-only tables
• Read the main README for more advanced usage