Gridlet.AspNetCore 1.3.0

<p align="center"> <img src="assets/gridlet-logo.png" width="160" height="160" alt="Gridlet logo" /> </p>

<h1 align="center">Gridlet</h1>

Gridlet is an embeddable ASP.NET Core NuGet package that adds a configurable, web-based database management interface to an existing application: browse schema, view and stream through data, inspect keys/indexes/relationships, and run queries, all from inside the host app using the host's own authentication, authorization, routing, logging, and deployment model.

<p align="center"> <img src="assets/screenshot-1.png" width="100%" alt="Gridlet table browser showing customer data" /> </p>

<p align="center"> <img src="assets/screenshot-2.png" height="280" alt="Gridlet published API preview showing a JSON response" /> <img src="assets/screenshot-3.png" height="280" alt="Gridlet query editor showing order summary results" /> </p>

Multiple providers

Register SQL Server and SQLite connections side by side, then switch between providers and their databases from the Gridlet header. Gridlet adapts its object browser and tools to the selected provider, surfacing the database objects and operations it supports, such as stored procedures and functions in SQL Server, while omitting concepts that do not apply to that provider.

.AddSqlServer(sqlServerConnectionString)
.AddSqlite(sqliteConnectionString);

<p align="center"> <img src="assets/screenshot-4.png" width="720" alt="Gridlet connection selector showing SQLite and SQL Server connections in one app" /> </p>

Quick start

In an app that already configures ASP.NET Core authentication and authorization:

using Gridlet;

var builder = WebApplication.CreateBuilder(args);

builder.Services
    .AddGridlet()
    .AddSqlServer(builder.Configuration.GetConnectionString("Default"));

var app = builder.Build();

app.MapGridlet();

app.Run();

Browse to /gridlet. Gridlet uses the host's default authorization policy. Add more connections by chaining .AddSqlServer(connectionString) or .AddSqlite(connectionString).

Developer configuration reference

AddGridlet registers Gridlet and accepts an optional Action<GridletOptions>. Chain AddSqlServer(...) or AddSqlite(...) once for each connection. Pass a connection string directly, or pass IConfiguration and its key in the standard ConnectionStrings section. Each method registers its provider, derives the connection label from the server or SQLite filename, and selects the connection string's initial database by default:

builder.Services
    .AddGridlet(options =>
    {
        options.Limits.DefaultPageSize = 50;
        options.Limits.MaxPageSize = 500;
        options.Limits.MaxQueryResultRows = 10_000;
        options.Limits.CommandTimeoutSeconds = 30;

        options.Security.AllowAnonymous = false;
        options.Security.AuthorizationPolicy = "GridletAccess";

        options.Storage.FilePath = "App_Data/gridlet-store.json";
    })
    .AddSqlServer(builder.Configuration, "Reporting", connection =>
    {
        connection.AllowSqlExecution = true;
        connection.AllowWrites = false;
        connection.AllowDdl = false;
    });

GridletOptions

The lower-level, provider-agnostic AddConnection(name, connectionString, providerName, configure) API remains available for custom registration:

AddConnection(configuration, connectionName, providerName, configure) resolves a value from the standard ConnectionStrings configuration section. Provider-specific registration is simpler for the built-in SQL Server and SQLite providers because it does not require a name or provider enum.

Per-connection options

Limit options

Security options

Authentication itself remains the host application's responsibility. Configure ASP.NET Core authentication and authorization before mapping Gridlet; Gridlet does not provide a separate login.

Storage options

Replace ISavedQueryStore and/or IPublishedEndpointStore after AddGridlet to use a database or another persistence mechanism. Gridlet uses TryAdd, so explicit host registrations take precedence.

Mapping and operational services

app.MapGridlet(pattern) maps the UI and its APIs under pattern, which defaults to /gridlet. The pattern may be changed, for example app.MapGridlet("/internal/database"). Configuration is validated when the endpoints are mapped, so invalid connection names or limit combinations fail at startup rather than on the first request.

Query execution, row writes, schema changes, and published endpoint invocations are sent to IGridletAuditSink. The default sink writes structured events through ILogger; register your own IGridletAuditSink before or after AddGridlet to persist them elsewhere (TryAdd preserves it).

Packages

The provider boundary (IGridletProvider → ISchemaReader, ITableDataService, IQueryRunner) keeps the core and UI engine-neutral so providers such as Gridlet.Postgres and Gridlet.MySql can be added later without rewriting the product.

Repository layout

src/
  Gridlet.Core/          core abstractions + domain model
  Gridlet.AspNetCore/    host integration, API endpoints, embedded UI
  Gridlet.SqlServer/     SQL Server provider
  Gridlet.Sqlite/        SQLite provider
tests/
  Gridlet.Tests/         unit tests + in-memory endpoint/auth tests (no DB required)
  Gridlet.BrowserTests/  end-to-end browser tests for the embedded UI
  Gridlet.ConsumerCompileTest/  compile-time checks for the public consumer API
samples/
  Gridlet.Demo/          runnable demo against a local SQLite file

Demo

samples/Gridlet.Demo is the runnable sample project. It creates and seeds a local GridletSample.db SQLite database on first run (customers/products/orders plus a view and an audit trigger), and mounts Gridlet at /gridlet with anonymous access. It also registers an OddSecond ASP.NET Core authorization policy and includes a published endpoint definition in the sample gridlet-store.json for GET /gridlet/pub/samples/odd-second. The endpoint returns query results during odd-numbered UTC seconds and returns 403 Forbidden during even-numbered UTC seconds, demonstrating how a published endpoint can require a host-defined policy while the rest of the sample remains anonymous.

dotnet run --project samples/Gridlet.Demo
# → http://localhost:5088/gridlet
# retry this URL on consecutive seconds to see alternating 200/403 responses:
# → http://localhost:5088/gridlet/pub/samples/odd-second

Security model

• AuthN/AuthZ: Gridlet maps all endpoints inside one route group and applies RequireAuthorization() (or the policy named in Security.AuthorizationPolicy). It never invents its own login; it reuses whatever the host has configured.
• Identifiers: every schema/table/column name that reaches dynamic SQL is validated against live metadata and bracket-quoted; values always travel as parameters.
• Limits: page size, query row caps, and command timeouts are enforced from GridletOptions.Limits.
• SQL editor: can be disabled per connection (AllowSqlExecution = false). Statement-level write protection is intentionally delegated to the SQL login's own permissions: point Gridlet at a login that has exactly the rights its users should have.
• Feature gates: row editing (AllowWrites) and the table designer (AllowDdl) can each be switched off per connection; the UI hides the controls and the endpoints return 403.
• Designer safety: designer data types are validated against a whitelist, every identifier is bracket-quoted, and row values always travel as SQL parameters.
• Audit: queries, row writes, schema changes, and published-API invocations flow through IGridletAuditSink (default: structured logging); replace the sink to persist audit events.

An explicitly configured AuthorizationPolicy takes precedence over AllowAnonymous. This makes a named policy fail closed even if a development configuration layer also sets AllowAnonymous to true. Anonymous access is enabled only when AllowAnonymous is true and no named policy is set.

Separate database identity for published APIs

You can configure a second named connection for published endpoints so their SQL runs as a least-privileged database user:

options.AddConnection("Management", adminConnectionString, GridletProviderNames.SqlServer);

options.AddConnection(
    "PublishedApi",
    restrictedApiConnectionString,
    GridletProviderNames.SqlServer,
    configure: connection =>
{
    // Hide interactive mutation tools for this connection. These are Gridlet feature gates;
    // the restricted database user's GRANT/DENY permissions remain the security boundary.
    connection.AllowSqlExecution = false;
    connection.AllowWrites = false;
    connection.AllowDdl = false;
});

Select PublishedApi as the connection when publishing the endpoint. Gridlet stores that connection name with the endpoint and uses its connection string on invocation. This separation is currently selectable rather than mandatory: a publisher can still select Management, so the host must limit publishing to trusted administrators and review stored endpoint definitions. Gridlet does not yet have a dedicated execution connection that automatically overrides every published endpoint.

API publishing

Any query can be published as an HTTP endpoint from the query editor (Publish…), or via POST {mount}/api/published. Published endpoints:

• live at {mount}/pub/{route} (GET with query-string parameters, or POST, PUT, PATCH, and DELETE with a JSON body),
• bind @parameters in the SQL to request values (missing optional parameters become NULL),
• let the publisher declare each value parameter as auto, string, integer, number, or boolean; Gridlet performs no implicit filtering, ordering, or pagination,
• inherit Gridlet's authorization and can additionally require a named policy,
• are stored (together with saved queries) in a JSON file at options.Storage.FilePath, default gridlet-store.json under the content root; swap ISavedQueryStore / IPublishedEndpointStore to persist elsewhere.

Response shape

Invocations stream their first result set as JSON, so server memory stays bounded no matter how large the result is (only one batch of rows is held at a time). The response body is:

{ "rows": [ { "col": "value" }, ... ], "rowCount": 123 }

rows streams first; rowCount is only known once every row has been sent, so it trails the array. A statement with no result set returns { "recordsAffected": N } instead. There is no truncated field: published endpoints are uncapped by default (see below), so there is normally nothing to truncate.

Because the 200 OK status and the first rows are already on the wire, a failure that occurs after streaming has begun cannot change the status code. Such a failure closes the JSON with an "error" field ({ "rows": [ ... ], "rowCount": N, "error": "message" }), which consumers should check for before trusting a partial result. Failures that occur before the first byte (routing, authorization, parameter binding, connection resolution, or an immediate query error) still return a clean 4xx/5xx status with { "error": "message" }. The api.invoke audit event is written when the stream finishes, so a mid-stream failure is recorded as succeeded: false.

Row cap

Published endpoints are uncapped by default. They stream every row, independent of the global MaxQueryResultRows limit (which continues to govern the UI and ad-hoc query editor). An endpoint can opt into a cap via the optional maxRows field on POST {mount}/api/published:

• omitted / null: uncapped (stream every row),
• 0 or less: uncapped,
• a positive number: cap at that many rows.

Because the default is uncapped, pagination is deliberately query-authored. For example, publish page and page_size as required integer parameters and use them directly in SQL Server:

SELECT *
FROM dbo.Customers
ORDER BY CustomerId
OFFSET ((@page - 1) * @page_size) ROWS
FETCH NEXT @page_size ROWS ONLY;

Feature status

• Explicitly configured SQL Server connections
• Browse databases, tables, views, stored procedures, functions
• Streaming, sortable data grid for tables and views
• Inspect columns, keys, indexes, constraints, relationships
• View source of views/procedures/functions
• Ad-hoc query editor with multiple result sets, messages, timing
• Safety limits, query timeouts, audit logging
• Configurable mount path, host auth reuse
• Create tables visually; add/edit/remove columns (drop table/column included)
• Edit table rows where permitted (insert/update/delete with NULL support)
• Saved queries
• Export results and table data (CSV/JSON)
• Publish queries/operations as protected API endpoints
• Resizable grid columns (data grids and query results)
• Create/edit views, stored procedures, and functions from the UI
• Discover, create, edit, and delete database triggers
• Create/edit indexes and primary/foreign keys
• Server-side full-table export (current export covers the loaded rows)

Provider status

• SQL Server support through the Gridlet.SqlServer provider
• SQLite support through the Gridlet.Sqlite provider, with provider-specific schema, query, write, trigger, and DDL coverage

SQLite exposes its primary database and schema as main. It supports tables, views, indexes, foreign keys, generated columns, row editing, and table-designer DDL; stored procedures, functions, and user-created schemas are not SQLite features and are omitted from the UI.

Development

dotnet build
pwsh tests/Gridlet.BrowserTests/bin/Debug/net10.0/playwright.ps1 install chromium # first run only
dotnet test

Tests run against an in-memory fake provider, temporary SQLite databases, and the real endpoint pipeline. No SQL Server is needed, so they also run in CI (.github/workflows/ci.yml). Browser tests start Gridlet on an ephemeral loopback port and use headless Chromium; install its pinned Playwright browser once after cloning or after updating the Playwright package.

Third-party software

Gridlet's browser UI is implemented in plain HTML, CSS, and JavaScript; it does not bundle a third-party front-end framework, editor, icon set, or web font.

The distributable packages use the following third-party projects at runtime:

The test project additionally uses xUnit.net and its Visual Studio runner under the Apache License 2.0, plus Microsoft's MIT-licensed ASP.NET Core TestHost and .NET test SDK. These development dependencies are not bundled into Gridlet's distributable packages.

Copyrights remain with their respective owners. The in-app About → Licences tab provides the runtime notices to Gridlet users; complete license texts and notices are available from the linked projects.