CSharpDB REST API Reference

The CSharpDB REST API exposes the full database feature set over HTTP, enabling cross-language interoperability. Built with ASP.NET Core Minimal APIs, it includes OpenAPI documentation and an interactive Scalar UI.

Running the API

dotnet run --project src/CSharpDB.Api

The API starts on http://localhost:61818 (HTTP) and https://localhost:61819 (HTTPS).

Interactive documentation: Open http://localhost:61818/scalar/v1 in a browser to explore and test endpoints with the Scalar API explorer.

Configuration

The default database path is configured in src/CSharpDB.Api/appsettings.json:

{
  "ConnectionStrings": {
    "CSharpDB": "Data Source=csharpdb.db"
  }
}

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

Endpoints

All endpoints are prefixed with /api.

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": "Restrict"
    }
  ]
}

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": "Restrict"
    }
  ]
}

Notes:

• validateOnly = true previews the migration without mutating schema or data.
• backupDestinationPath is optional and is only used during apply mode.
• Paths are resolved on the API 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 },
    { "name": "name", "type": "Text", "nullable": false, "isPrimaryKey": false, "isIdentity": false },
    { "name": "age", "type": "Integer", "nullable": true, "isPrimaryKey": false, "isIdentity": false }
  ]
}

GET /api/tables/{name}/count

Get the row count for a table.

Response:

{ "count": 42 }

DELETE /api/tables/{name}

Drop a table.

Response: 200 OK with { "message": "Table 'users' dropped." }

PATCH /api/tables/{name}/rename

Rename a table.

Request:

{ "newName": "customers" }

Response: 200 OK with { "message": "Table renamed from 'users' to 'customers'." }

POST /api/tables/{name}/columns

Add a column to a table.

Request:

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

Response: 200 OK with { "message": "Column 'email' added to 'users'." }

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

Drop a column.

Response: 200 OK with { "message": "Column 'email' dropped from 'users'." }

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

Rename a column.

Request:

{ "newName": "full_name" }

Response: 200 OK with { "message": "Column renamed from 'name' to 'full_name' in 'users'." }

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:

{
  "tableName": "users",
  "page": 1,
  "pageSize": 50,
  "totalRows": 3,
  "columns": ["id", "name", "age"],
  "rows": [
    { "id": 1, "name": "Alice", "age": 30 },
    { "id": 2, "name": "Bob", "age": 25 }
  ]
}

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 { "message": "Row inserted into 'users'.", "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 { "message": "Row updated in 'users'.", "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 { "message": "Row deleted from 'users'.", "rowsAffected": 1 }

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"],
  "rows": [
    { "name": "Widget", "price": 29.99 },
    { "name": "Gadget", "price": 14.99 }
  ],
  "rowsAffected": 0,
  "elapsed": 1.23
}

Response (mutation):

{
  "isQuery": false,
  "columnNames": null,
  "rows": null,
  "rowsAffected": 3,
  "elapsed": 0.87
}

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
• Architecture Guide — How the engine works internally
• CLI Reference — Interactive REPL commands
• Sample Datasets — Ready-to-run SQL scripts