CSharpDB REST API Reference

The CSharpDB REST API is now hosted by CSharpDB.Daemon by default. The daemon exposes the existing HTTP /api surface and the gRPC service from one long-running process backed by the same warm database client.

The REST surface enables cross-language interoperability over JSON/HTTP. Built with ASP.NET Core Minimal APIs, it includes OpenAPI documentation and an interactive Scalar UI when the daemon runs in Development mode.

Running the API

Run the combined daemon host from source:

dotnet run --project src/CSharpDB.Daemon/CSharpDB.Daemon.csproj

The daemon launch profile starts on https://localhost:49995 and http://localhost:49996. REST endpoints are available under /api, and gRPC endpoints are available from the same host.

Interactive documentation: In Development mode, open https://localhost:49995/scalar/v1 or http://localhost:49996/scalar/v1 in a browser to explore and test endpoints with the Scalar API explorer.

For a stable local HTTP port, set ASPNETCORE_URLS explicitly before starting the daemon:

$env:ConnectionStrings__CSharpDB = "Data Source=C:\data\sample.db"
$env:ASPNETCORE_URLS = "http://localhost:5820"
dotnet run --project src/CSharpDB.Daemon/CSharpDB.Daemon.csproj

With that override, the REST base URL is http://localhost:5820/api and Scalar is available at http://localhost:5820/scalar/v1.

Configuration

The current default database path and daemon host behavior are configured in src/CSharpDB.Daemon/appsettings.json:

{
  "ConnectionStrings": {
    "CSharpDB": "Data Source=csharpdb.db"
  },
  "CSharpDB": {
    "Daemon": {
      "EnableRestApi": true
    },
    "HostDatabase": {
      "OpenMode": "HybridIncrementalDurable",
      "ImplicitInsertExecutionMode": "ConcurrentWriteTransactions",
      "UseWriteOptimizedPreset": true,
      "HotTableNames": [],
      "HotCollectionNames": []
    }
  }
}

CSharpDB:Daemon:EnableRestApi controls whether the daemon maps the REST /api surface. The default is true. Set it to false only when the daemon should expose gRPC without REST.

CORS is enabled for all origins by default (development convenience). JSON responses use camelCase naming and omit null values.

The standalone CSharpDB.Api project remains available for REST-only hosting, but the recommended remote host is CSharpDB.Daemon so REST and gRPC clients share the same warm database process.

Structured logging, bounded histories, OpenTelemetry/OTLP, Prometheus, and health are configured under CSharpDB:Observability. Exporters are disabled by default; minimal health routes are enabled independently. See the configuration reference and Observability and Diagnostics guide.

Health and Metrics

Health probes are intentionally anonymous and minimal. Prometheus follows host security: API-key mode requires the configured key; security mode None accepts only the actual loopback peer unless Prometheus:AllowInsecureRemoteAccess=true is an explicit operator choice. Forwarded-address headers do not grant loopback access.

Endpoints

All endpoints are prefixed with /api.

Runtime Diagnostics

Runtime diagnostics are an optional additive client capability. Their polling requests suppress their own observation, and ordinary responses omit SQL text, values, credentials, connection strings, paths, exception messages, and raw exceptions.

In API-key mode diagnostics require the configured key. In security mode None, only a proven loopback peer is accepted unless AllowInsecureRemoteDiagnostics=true. Query detail additionally requires AllowSensitiveQueryDetailAccess=true. Authentication failures return 401, policy denials return 403, and clients without the optional diagnostics capability return 501. Cancellation is cooperative; there is no query/session kill route.

Database Info

GET /api/info

Returns a summary of the database.

Response:

{
  "dataSource": "csharpdb.db",
  "tableCount": 3,
  "indexCount": 2,
  "viewCount": 1,
  "triggerCount": 1,
  "procedureCount": 2
}

Storage Inspection

Read-only physical diagnostics endpoints for .db and .wal inspection.

GET /api/inspect

Run a full database file inspection.

Query parameters:

• includePages (default: false) — include per-page decoded details in the response
• path (optional) — override database path for this request

GET /api/inspect/wal

Inspect WAL header/frame/checksum state.

Query parameters:

• path (optional) — override database path for this request

GET /api/inspect/page/{id}

Inspect a single page by page id.

Query parameters:

• hex (default: false) — include page hex dump
• path (optional) — override database path for this request

GET /api/inspect/indexes

Validate index metadata and root tree reachability.

Query parameters:

• index (optional) — check one index by name
• sample (optional) — sample size hint for future index validation passes
• path (optional) — override database path for this request

Responses follow the diagnostics models documented in Storage Inspector.

Maintenance

POST /api/maintenance/migrate-foreign-keys

Validate or apply foreign-key retrofit migration for older databases whose tables do not yet persist FK metadata.

Request:

{
  "validateOnly": true,
  "backupDestinationPath": "pre-fk.backup.db",
  "violationSampleLimit": 100,
  "constraints": [
    {
      "tableName": "orders",
      "columnName": "customer_id",
      "referencedTableName": "customers",
      "referencedColumnName": "id",
      "onDelete": "setDefault",
      "onUpdate": "cascade"
    }
  ]
}

Response:

{
  "validateOnly": true,
  "succeeded": false,
  "backupDestinationPath": null,
  "affectedTables": 1,
  "appliedForeignKeys": 1,
  "copiedRows": 0,
  "violationCount": 1,
  "violations": [
    {
      "tableName": "orders",
      "columnName": "customer_id",
      "referencedTableName": "customers",
      "referencedColumnName": "id",
      "childKeyColumnName": "id",
      "childKeyValue": 42,
      "childValue": 999,
      "reason": "MissingReferencedParent"
    }
  ],
  "appliedConstraints": [
    {
      "tableName": "orders",
      "columnName": "customer_id",
      "referencedTableName": "customers",
      "referencedColumnName": "id",
      "constraintName": "fk_orders_customer_id_abcd1234",
      "supportingIndexName": "__fk_orders_customer_id_abcd1234",
      "onDelete": "setDefault",
      "onUpdate": "cascade"
    }
  ]
}

Notes:

• validateOnly = true previews the migration without mutating schema or data.
• backupDestinationPath is optional and is only used during apply mode.
• onDelete and onUpdate accept the lower-camel-case values restrict, noAction, cascade, setNull, and setDefault.
• Paths are resolved on the daemon host machine, not on the caller.

Tables

GET /api/tables

List all table names.

Response:

["users", "orders", "products"]

GET /api/tables/{name}/schema

Get the full schema for a table.

Response:

{
  "tableName": "users",
  "columns": [
    {
      "name": "id",
      "type": "Integer",
      "nullable": false,
      "isPrimaryKey": true,
      "isIdentity": true,
      "isRowVersion": false,
      "collation": null,
      "defaultSql": null,
      "schemaId": "bd8b9e88-dfd0-4df1-9477-cf5f6064ec7a",
      "declaredType": {
        "kind": "Integer",
        "length": null,
        "precision": null,
        "scale": null,
        "fractionalSecondsPrecision": null
      }
    },
    {
      "name": "active",
      "type": "Integer",
      "nullable": false,
      "isPrimaryKey": false,
      "isIdentity": false,
      "isRowVersion": false,
      "collation": null,
      "defaultSql": "1",
      "schemaId": "7fd8f17c-49e4-4b27-b91c-a12ddf2998f2",
      "declaredType": {
        "kind": "Boolean",
        "length": null,
        "precision": null,
        "scale": null,
        "fractionalSecondsPrecision": null
      }
    }
  ],
  "foreignKeys": [],
  "keyConstraints": [],
  "checkConstraints": [],
  "nextRowId": 2,
  "schemaId": "67a2381a-844c-452a-a331-f42eb9f8e0cf"
}

type is the compact physical carrier (Integer, Real, Decimal, Text, or Blob), not the declared SQL spelling. declaredType carries the logical kind and facets; it can be absent for legacy metadata. A rowversion is identified by isRowVersion: true even though its carrier is Blob. See the complete SQL data type reference.

GET /api/tables/{name}/count

Get the row count for a table.

Response:

{ "tableName": "users", "count": 42 }

DELETE /api/tables/{name}

Drop a table.

Response: 204 No Content

PATCH /api/tables/{name}/rename

Rename a table.

Request:

{ "newName": "customers" }

Response: 204 No Content

POST /api/tables/{name}/columns

Add a column to a table.

Request:

{ "columnName": "email", "type": "TEXT", "notNull": false }

The convenience endpoint accepts physical carrier names only: Integer, Real, Decimal, Text, or Blob (case-insensitive). Use POST /api/sql/execute for a logical or faceted declaration such as BOOLEAN, VARCHAR(100), or DATETIMEOFFSET(7).

Response: 204 No Content

DELETE /api/tables/{name}/columns/{col}

Drop a column.

Response: 204 No Content

PATCH /api/tables/{name}/columns/{col}/rename

Rename a column.

Request:

{ "newName": "full_name" }

Response: 204 No Content

Rows

GET /api/tables/{name}/rows

Browse table rows with pagination.

Query parameters:

• page (default: 1) — Page number
• pageSize (default: 50, max: 1000) — Rows per page

Response:

{
  "columnNames": ["id", "name", "active"],
  "rows": [
    { "id": 1, "name": "Alice", "active": true },
    { "id": 2, "name": "Bob", "active": false }
  ],
  "totalRows": 3,
  "page": 1,
  "pageSize": 50,
  "totalPages": 1,
  "columnTypes": ["INTEGER", "TEXT", "BOOLEAN"]
}

columnTypes contains canonical logical SQL spellings. It is the appropriate field for interpreting values; unlike the schema endpoint's type field, it is not a physical-carrier list.

GET /api/tables/{name}/rows/{pkValue}

Get a single row by primary key.

Query parameters:

• pkColumn (default: "id") — Name of the primary key column

Response:

{ "id": 1, "name": "Alice", "age": 30 }

POST /api/tables/{name}/rows

Insert a new row.

Request:

{ "values": { "id": 4, "name": "Diana", "age": 28 } }

Response: 201 Created with { "rowsAffected": 1 }

PUT /api/tables/{name}/rows/{pkValue}

Update a row by primary key.

Query parameters:

• pkColumn (default: "id") — Name of the primary key column

Request:

{ "values": { "name": "Diana Updated", "age": 29 } }

Response: 200 OK with { "rowsAffected": 1 }

DELETE /api/tables/{name}/rows/{pkValue}

Delete a row by primary key.

Query parameters:

• pkColumn (default: "id") — Name of the primary key column

Response: 200 OK with { "rowsAffected": 1 }

REST row values use JSON's transport types and are then checked against the target column's declared SQL type. Integral numbers are read as signed 64-bit values, exact JSON decimals are retained as Decimal when possible, and JSON Booleans are normalized to 0/1 before logical column coercion. Send ordinary binary as {"$csharpdb":"binary-v1","base64":"..."} and a non-byte-aligned SQL bit string as {"$csharpdb":"bit-string-v1","base64":"...","bitLength":13}. Returned ordinary binary uses JSON base64; returned bit strings retain the tagged envelope and exact bit length.

Indexes

GET /api/indexes

List all indexes.

Response:

[
  { "name": "idx_category", "tableName": "products", "columnName": "category", "isUnique": false }
]

POST /api/indexes

Create an index.

Request:

{ "indexName": "idx_email", "tableName": "users", "columnName": "email", "isUnique": true }

Response: 201 Created with { "message": "Index 'idx_email' created." }

PUT /api/indexes/{name}

Update (drop and recreate) an index.

Request:

{ "newIndexName": "idx_user_email", "tableName": "users", "columnName": "email", "isUnique": true }

Response: 200 OK

DELETE /api/indexes/{name}

Drop an index.

Response: 200 OK with { "message": "Index 'idx_email' dropped." }

Views

GET /api/views

List all views.

Response:

["order_summary", "product_catalog"]

GET /api/views/{name}

Get a view definition.

Response:

{
  "viewName": "order_summary",
  "selectSql": "SELECT o.id, c.name, o.total FROM orders o INNER JOIN customers c ON o.customer_id = c.id"
}

GET /api/views/{name}/rows

Browse view results with pagination.

Query parameters:

• page (default: 1)
• pageSize (default: 50, max: 1000)

Response: Same shape as table browse (columns + rows).

POST /api/views

Create a view.

Request:

{ "viewName": "expensive_products", "selectSql": "SELECT name, price FROM products WHERE price > 50" }

Response: 201 Created

PUT /api/views/{name}

Update a view (drop and recreate).

Request:

{ "newViewName": "expensive_products", "selectSql": "SELECT name, price FROM products WHERE price > 100" }

Response: 200 OK

DELETE /api/views/{name}

Drop a view.

Response: 200 OK

Triggers

GET /api/triggers

List all triggers.

Response:

[
  {
    "name": "trg_update_stock",
    "tableName": "order_items",
    "timing": "After",
    "event": "Insert",
    "bodySql": "UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id"
  }
]

POST /api/triggers

Create a trigger.

Request:

{
  "triggerName": "trg_audit_insert",
  "tableName": "users",
  "timing": "After",
  "event": "Insert",
  "bodySql": "INSERT INTO audit_log VALUES ('INSERT', NEW.name)"
}

Timing values: "Before", "After"

Event values: "Insert", "Update", "Delete"

Response: 201 Created

PUT /api/triggers/{name}

Update a trigger (drop and recreate).

Response: 200 OK

DELETE /api/triggers/{name}

Drop a trigger.

Response: 200 OK

SQL Execution

POST /api/sql/execute

Execute an arbitrary SQL statement.

Request:

{ "sql": "SELECT name, price FROM products WHERE price > 10 ORDER BY price DESC" }

Response (query):

{
  "isQuery": true,
  "columnNames": ["name", "price"],
  "columnTypes": ["TEXT", "DECIMAL(10,2)"],
  "rows": [
    { "name": "Widget", "price": 29.99 },
    { "name": "Gadget", "price": 14.99 }
  ],
  "rowsAffected": 0,
  "error": null,
  "elapsedMs": 1.23,
  "columnNullability": [false, false],
  "columns": [
    {
      "name": "name",
      "type": "Text",
      "nullable": false,
      "isPrimaryKey": false,
      "isIdentity": false,
      "isRowVersion": false,
      "collation": null,
      "defaultSql": null,
      "schemaId": "00000000-0000-0000-0000-000000000000",
      "declaredType": { "kind": "Text", "length": null, "precision": null, "scale": null, "fractionalSecondsPrecision": null }
    },
    {
      "name": "price",
      "type": "Decimal",
      "nullable": false,
      "isPrimaryKey": false,
      "isIdentity": false,
      "isRowVersion": false,
      "collation": null,
      "defaultSql": null,
      "schemaId": "00000000-0000-0000-0000-000000000000",
      "declaredType": { "kind": "Decimal", "length": null, "precision": 10, "scale": 2, "fractionalSecondsPrecision": null }
    }
  ]
}

columnTypes is the canonical SQL type list. The optional structured columns list also exposes each result's physical type, declared logical descriptor, nullability, and rowversion flag.

Response (mutation):

{
  "isQuery": false,
  "columnNames": null,
  "columnTypes": null,
  "rows": null,
  "rowsAffected": 3,
  "error": null,
  "elapsedMs": 0.87,
  "columnNullability": null,
  "columns": null
}

Procedures

Table-backed procedure catalog (__procedures) with strict parameter metadata validation and transactional execution.

GET /api/procedures

List procedure metadata.

GET /api/procedures/{name}

Get one procedure definition.

POST /api/procedures

Create a procedure.

Request:

{
  "name": "GetUserById",
  "bodySql": "SELECT * FROM users WHERE id = @id;",
  "parameters": [
    { "name": "id", "type": "INTEGER", "required": true, "default": null, "description": "User ID" }
  ],
  "description": "Lookup user by ID",
  "isEnabled": true
}

PUT /api/procedures/{name}

Update (or rename) a procedure.

Request:

{
  "newName": "GetUserById",
  "bodySql": "SELECT * FROM users WHERE id = @id;",
  "parameters": [
    { "name": "id", "type": "INTEGER", "required": true }
  ],
  "description": "Updated description",
  "isEnabled": true
}

DELETE /api/procedures/{name}

Delete a procedure.

POST /api/procedures/{name}/execute

Execute a stored procedure by name.

Request:

{
  "args": {
    "id": 123
  }
}

Response (success):

{
  "procedureName": "GetUserById",
  "succeeded": true,
  "statements": [
    {
      "statementIndex": 0,
      "statementText": "SELECT * FROM users WHERE id = @id;",
      "isQuery": true,
      "columnNames": ["id", "name"],
      "rows": [{ "id": 123, "name": "Alice" }],
      "rowsAffected": 1,
      "elapsedMs": 0.34
    }
  ],
  "error": null,
  "failedStatementIndex": null,
  "elapsedMs": 0.51
}

Response (validation/runtime failure): 400 Bad Request with the same shape and succeeded = false.

Error Handling

The API uses standard HTTP status codes and returns structured error responses:

Error response format:

{
  "error": "Table 'nonexistent' not found.",
  "code": "TableNotFound"
}

In development mode, a detail field with a stack trace is included.

See Also

• Getting Started Tutorial — Engine API walkthrough
• Multi-Writer gRPC Daemon — Daemon runtime model and remote-host guidance
• Observability and Diagnostics — Logging, runtime models, OpenTelemetry, Prometheus, health, privacy, and troubleshooting
• Storage Architecture Deep Dive — How the engine works internally
• CLI Reference — Interactive REPL commands
• Sample Datasets — Ready-to-run SQL scripts