Ben Witt

Posted on Jul 1

A Generic Table API with Dapper in 80 Lines of C#

#api #csharp #dotnet #postgres

Sometimes you quickly need a read-only API on top of a PostgreSQL database.

No Entity Framework setup.

No code generation.

No endpoint per table.

Just query a known table and return the result.

The tempting version is fully dynamic:

GET /api/{table}

That is useful for demos, but dangerous if it means every table in the database suddenly becomes reachable from HTTP.

The safer version is still generic, but not unlimited:

one endpoint, known tables, allowed columns, bounded pagination, read-only database permissions.

That is the version I would use as a practical template.

The Goal

GET /api/transport_orders?$top=50&$skip=0&$orderby=created_at desc
GET /api/business_orders?$top=100
GET /api/handling_units?$top=25&$skip=25

Each call returns a normalized JSON object:

{
  "totalCount": 127,
  "items": []
}

Columns come from PostgreSQL as snake_case and are returned to the client as camelCase. JSON columns can be deserialized so the frontend does not have to parse JSON strings manually.

Setup

NuGet packages:

dotnet add package Dapper
dotnet add package Npgsql

That is it. No ORM, no migrations, no scaffolding.

The Important Boundary: Known Tables Only

The endpoint is generic, but the database surface is not.

Instead of accepting every table name that matches a regex, define the tables and columns the API is allowed to expose:

var tables = new Dictionary<string, TableDefinition>(StringComparer.OrdinalIgnoreCase)
{
    ["transport_orders"] = new(
        Schema: "public",
        Name: "transport_orders",
        Columns: new HashSet<string>(StringComparer.OrdinalIgnoreCase)
        {
            "id",
            "hu_id",
            "state",
            "source_node_name",
            "target_node_name",
            "created_at",
            "is_active",
            "route_nodes"
        }),

    ["business_orders"] = new(
        Schema: "public",
        Name: "business_orders",
        Columns: new HashSet<string>(StringComparer.OrdinalIgnoreCase)
        {
            "id",
            "order_number",
            "state",
            "created_at"
        })
};

This is the difference between a useful internal helper and an accidental database browser.

The Endpoint

Here is a complete minimal API example:

using System.Text.Json;
using System.Text.RegularExpressions;
using Dapper;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);

var rawConnectionString =
    Environment.GetEnvironmentVariable("DATABASE_URL")
    ?? builder.Configuration.GetConnectionString("PostgreSQL")
    ?? throw new InvalidOperationException("No connection string found.");

var connectionString = NormalizeConnectionString(rawConnectionString);

builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(connectionString));

builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(policy =>
        policy
            .WithOrigins("http://localhost:5173")
            .AllowAnyMethod()
            .AllowAnyHeader());
});

var tables = new Dictionary<string, TableDefinition>(StringComparer.OrdinalIgnoreCase)
{
    ["transport_orders"] = new(
        Schema: "public",
        Name: "transport_orders",
        Columns: new HashSet<string>(StringComparer.OrdinalIgnoreCase)
        {
            "id",
            "hu_id",
            "state",
            "source_node_name",
            "target_node_name",
            "created_at",
            "is_active",
            "route_nodes"
        }),

    ["business_orders"] = new(
        Schema: "public",
        Name: "business_orders",
        Columns: new HashSet<string>(StringComparer.OrdinalIgnoreCase)
        {
            "id",
            "order_number",
            "state",
            "created_at"
        })
};

var app = builder.Build();
app.UseCors();

app.MapGet("/api/{table}", async Task<IResult> (
    string table,
    HttpRequest request,
    NpgsqlDataSource dataSource,
    CancellationToken cancellationToken) =>
{
    if (!tables.TryGetValue(table, out var tableDefinition))
        return Results.NotFound($"Table '{table}' is not exposed by this API.");

    var top = ParseBoundedInt(request.Query["$top"], defaultValue: 100, min: 1, max: 500);
    var skip = ParseBoundedInt(request.Query["$skip"], defaultValue: 0, min: 0, max: 100_000);

    if (!TryBuildOrderBy(
            request.Query["$orderby"].FirstOrDefault(),
            tableDefinition,
            out var orderByClause,
            out var orderByError))
    {
        return Results.BadRequest(orderByError);
    }

    var qualifiedTable = QualifiedTableName(tableDefinition);
    var columnList = string.Join(", ", tableDefinition.Columns.Select(QuoteIdentifier));

    var countSql = $"SELECT COUNT(*) FROM {qualifiedTable}";
    var dataSql = $"""
        SELECT {columnList}
        FROM {qualifiedTable}
        {orderByClause}
        LIMIT @top OFFSET @skip
        """;

    await using var db = await dataSource.OpenConnectionAsync(cancellationToken);

    var totalCount = await db.ExecuteScalarAsync<long>(
        new CommandDefinition(countSql, cancellationToken: cancellationToken));

    var rows = await db.QueryAsync(
        new CommandDefinition(dataSql, new { top, skip }, cancellationToken: cancellationToken));

    return Results.Ok(new
    {
        totalCount,
        items = ToCamelCaseKeys(rows)
    });
});

app.MapGet("/health", () => Results.Ok(new { status = "ok" }));

app.Run();

record TableDefinition(
    string Schema,
    string Name,
    IReadOnlySet<string> Columns);

Helper Functions

Normalize the connection string

Many cloud providers expose PostgreSQL connection strings as URLs:

postgresql://user:password@host:5432/database

Npgsql primarily documents key-value connection strings:

Host=...;Port=...;Database=...;Username=...;Password=...

This helper handles the common URL shape. If your provider includes important query parameters such as SSL settings, preserve them explicitly or use the key-value connection string directly.

static string NormalizeConnectionString(string value)
{
    if (!Uri.TryCreate(value, UriKind.Absolute, out var uri))
        return value;

    if (uri.Scheme is not ("postgres" or "postgresql"))
        return value;

    var userInfo = uri.UserInfo.Split(':', 2);

    var builder = new NpgsqlConnectionStringBuilder
    {
        Host = uri.Host,
        Port = uri.Port > 0 ? uri.Port : 5432,
        Database = Uri.UnescapeDataString(uri.AbsolutePath.TrimStart('/'))
    };

    if (userInfo.Length > 0)
        builder.Username = Uri.UnescapeDataString(userInfo[0]);

    if (userInfo.Length > 1)
        builder.Password = Uri.UnescapeDataString(userInfo[1]);

    return builder.ConnectionString;
}

Bound pagination

Never let the client decide unlimited page sizes.

static int ParseBoundedInt(
    string? value,
    int defaultValue,
    int min,
    int max)
{
    if (!int.TryParse(value, out var parsed))
        return defaultValue;

    return Math.Clamp(parsed, min, max);
}

Quote identifiers

Identifiers cannot be passed as SQL parameters. That is why the allow-list is the real protection. Quoting is still useful because it avoids problems with reserved words or unusual casing.

static string QuoteIdentifier(string identifier) =>
    "\"" + identifier.Replace("\"", "\"\"") + "\"";

static string QualifiedTableName(TableDefinition table) =>
    $"{QuoteIdentifier(table.Schema)}.{QuoteIdentifier(table.Name)}";

Validate `$orderby`

The $orderby parameter is not free-form SQL. It is a small language:

created_at desc
created_at desc, id asc

Every column must exist in the table allow-list.

static bool TryBuildOrderBy(
    string? orderBy,
    TableDefinition table,
    out string clause,
    out string? error)
{
    clause = string.Empty;
    error = null;

    if (string.IsNullOrWhiteSpace(orderBy))
        return true;

    var parts = orderBy.Split(
        ',',
        StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries);

    var fragments = new List<string>();

    foreach (var part in parts)
    {
        var match = Regex.Match(
            part,
            @"^(?<column>[a-zA-Z_][a-zA-Z0-9_]*)(\s+(?<direction>asc|desc))?$",
            RegexOptions.IgnoreCase);

        if (!match.Success)
        {
            error = "Invalid $orderby syntax.";
            return false;
        }

        var column = match.Groups["column"].Value;

        if (!table.Columns.Contains(column))
        {
            error = $"Column '{column}' is not allowed in $orderby.";
            return false;
        }

        var direction = match.Groups["direction"].Success
            ? match.Groups["direction"].Value.ToUpperInvariant()
            : "ASC";

        fragments.Add($"{QuoteIdentifier(column)} {direction}");
    }

    clause = fragments.Count > 0
        ? "ORDER BY " + string.Join(", ", fragments)
        : string.Empty;

    return true;
}

Convert `snake_case` keys to `camelCase`

Dapper returns column names as they come from the database. The API can normalize them for frontend use.

static string ToCamelCase(string value)
{
    if (string.IsNullOrWhiteSpace(value))
        return value;

    var parts = value.Split('_', StringSplitOptions.RemoveEmptyEntries);

    if (parts.Length == 0)
        return value;

    return parts[0].ToLowerInvariant()
           + string.Concat(parts.Skip(1).Select(part =>
               char.ToUpperInvariant(part[0]) + part[1..].ToLowerInvariant()));
}

static IEnumerable<Dictionary<string, object?>> ToCamelCaseKeys(IEnumerable<dynamic> rows) =>
    rows.Select(row => ((IDictionary<string, object?>)row)
        .ToDictionary(
            pair => ToCamelCase(pair.Key),
            pair => TryParseJson(pair.Value)));

Parse JSON strings carefully

If you query dynamically with Dapper, JSONB values may arrive as strings. This helper parses strings that look like JSON objects or arrays.

This is a convenience heuristic, not a perfect type system. If you know the schema, prefer type-aware JSON mapping.

static object? TryParseJson(object? value)
{
    if (value is not string text)
        return value;

    var trimmed = text.TrimStart();

    if (trimmed.Length == 0 || (trimmed[0] is not ('{' or '[')))
        return value;

    try
    {
        using var document = JsonDocument.Parse(text);
        return document.RootElement.Clone();
    }
    catch (JsonException)
    {
        return value;
    }
}

Security: What This Protects

This version protects against the most important mistakes in a dynamic read endpoint:

unknown tables are rejected
unknown columns are not selected
$orderby can only use allowed columns
identifiers are quoted
$top and $skip are bounded
SQL values still use parameters
CORS is restricted to a known frontend origin

But this is still not a full production data access layer.

For production, also use:

a dedicated read-only PostgreSQL user
authentication and authorization at the API boundary
row-level or tenant-level filtering if needed
explicit decisions about sensitive columns
logging and rate limiting
optional total counts for large tables

The most important point:

A generic endpoint should not mean generic database access.

The Result

GET /api/transport_orders?$top=5&$orderby=created_at desc

{
  "totalCount": 127,
  "items": [
    {
      "id": "c720153c-3d9f-4764-8d5a-87bd5ed1594a",
      "huId": "341994",
      "state": "Failed",
      "sourceNodeName": "IP01",
      "targetNodeName": "A01-L-001-02-1",
      "createdAt": "2026-03-26T13:41:38.966Z",
      "isActive": false,
      "routeNodes": ["IP01", "A01-L-001-02-1"]
    }
  ]
}

snake_case from PostgreSQL arrives at the client as camelCase. JSON arrays such as routeNodes can be returned as JSON arrays instead of strings.

When This Makes Sense

This approach fits when:

you need a quick read-only API over existing tables
the exposed tables are known and stable
you want direct SQL without ORM setup
the API is for an internal tool, demo, or prototype
the shape is simple: list rows, page rows, sort rows

It does not fit when:

complex joins or aggregations are required
business logic belongs in the API layer
fine-grained access control is required
rows must be filtered per tenant or user
the database contains sensitive columns you cannot safely expose

Conclusion

Dapper is a good fit for this kind of endpoint: small surface, direct SQL, minimal ceremony.

But the naive version is too open. A regex around the table name is not enough. The safer pattern is:

one generic endpoint
explicit table allow-list
explicit column allow-list
bounded pagination
validated sorting
read-only database credentials

That keeps the useful part of the idea without turning the API into an accidental database browser.

References

Npgsql connection string parameters: https://www.npgsql.org/doc/connection-string-parameters.html
Npgsql JSON mapping: https://www.npgsql.org/doc/types/json.html

DEV Community

A Generic Table API with Dapper in 80 Lines of C#

The Goal

Setup

The Important Boundary: Known Tables Only

The Endpoint

Helper Functions

Normalize the connection string

Bound pagination

Quote identifiers

Validate `$orderby`

Convert `snake_case` keys to `camelCase`

Parse JSON strings carefully

Security: What This Protects

The Result

When This Makes Sense

Conclusion

References

Top comments (0)

The Goal

Setup

The Important Boundary: Known Tables Only

The Endpoint

Helper Functions

Normalize the connection string

Bound pagination

Quote identifiers

Validate $orderby

Convert snake_case keys to camelCase

Parse JSON strings carefully

Security: What This Protects

The Result

When This Makes Sense

Conclusion

References

Validate `$orderby`

Convert `snake_case` keys to `camelCase`