Comet1.RestSQL 0.0.6

RestSQL Documentation

RestSQL is a lightweight .NET tool that turns SQL queries (defined in YAML) into ready-to-run REST endpoints. Works standalone or as a library, supports transactions, nested JSON output, and multiple database providers.

Getting Started

There are two ways to use RestSQL:

1. Standalone API (Using RestSQL.Api)

The simplest way to get started is using the pre-built API project:

1. Clone the repository
2. Configure your appsettings.json:

{
  "RestSQL": {
    "ConfigFolder": "path/to/your/yaml/configs"
  },
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information"
    },
    "WriteTo": [
      {
        "Name": "Console"
      }
    ]
  }
}

3. Run the project:

cd src/RestSQL.Api
dotnet run

2. Library Usage (Adding to Existing Project)

Add RestSQL to your ASP.NET Core project:

// Program.cs
builder.Services.AddRestSQL();

// Configure middleware
app.UseRestSQL("path/to/config/folder");

Configuration

RestSQL uses YAML files for configuration. You need two main configuration sections:

1. connections - Database connection definitions
2. endpoints - Endpoint definitions

You can split these across files as you see fit, they will be merged. A single file is also fine.

Database Connections

Define your database connections under connections:

connections:
  postgres1:
    type: PostgreSQL
    connectionString: "Host=localhost;Database=mydb;Username=user;Password=pass"
  mysql1:
    type: MySql
    connectionString: "Server=localhost;Database=mydb;User=user;Password=pass"
  oracle1:
    type: Oracle
    connectionString: "Data Source=localhost:1521/XEPDB1;User Id=system;Password=pass"
  sqlserver1:
    type: SqlServer
    connectionString: "Server=localhost;Database=mydb;User=sa;Password=pass;TrustServerCertificate=True"
  sqlite1:
    type: Sqlite
    connectionString: "Data Source=local.db"

Endpoint Configuration

Define REST endpoints:

endpoints:
  # Get all posts with tags
  - path: /api/posts
    method: GET
    statusCode: 200
    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts;
      tags: &tagsQuery
        connectionName: blog
        sql: >
          select *
          from tags;
    outputStructure:
      type: Object
      isArray: true
      queryName: posts
      fields: &postFields
        - { type: Long, name: id, columnName: post_id }
        - { type: String, name: title, columnName: title }
        - { type: String, name: description, columnName: description }
        - { type: String, name: username, columnName: username }
        - { type: String, name: creationDate, columnName: creation_date }
        - type: string
          isArray: true
          name: tags
          queryName: tags
          columnName: tag
          linkColumn: post_id

Example Blog API

Let's look at a complete blog post API example:

connections:
  blog:
    type: PostgreSQL
    connectionString: "Host=localhost;Database=restsql_blog;Username=restsql_blog;Password=restsql_blog"

endpoints:
  # Get all posts with tags
  - path: /api/posts
    method: GET
    statusCode: 200
    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts;
      tags: &tagsQuery
        connectionName: blog
        sql: >
          select *
          from tags;
    outputStructure:
      type: Object
      isArray: true
      queryName: posts
      fields: &postFields
        - { type: Long, name: id, columnName: post_id }
        - { type: String, name: title, columnName: title }
        - { type: String, name: description, columnName: description }
        - { type: String, name: username, columnName: username }
        - { type: String, name: creationDate, columnName: creation_date }
        - type: string
          isArray: true
          name: tags
          queryName: tags
          columnName: tag
          linkColumn: post_id
      
  # Get specific post
  - path: /api/posts/{id}
    method: GET
    statusCode: 200
    statusCodeOnEmptyResult: 404
    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts
          where id = :id::int;
      tags: &tagsQuery
        connectionName: blog
        sql: >
          select *
          from tags;
    outputStructure:
      type: Object
      isArray: true
      queryName: posts
      fields: *postFields

  # Create new post, and return the created post
  - path: /api/posts
    method: POST
    statusCode: 200
    writeOperations:
      - connectionName: blog
        sql: >
          insert into posts (title, description, creation_date, username)
          values (:title, :description, current_timestamp, :username)
          returning id;
        bodyType: Object
        outputCaptures:
          - columnName: id
            parameterName: post_id
    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts
          where id = @post_id;
      tags: *tagsQuery
    outputStructure:
      type: Object
      queryName: posts
      fields: *postFields

Making Requests

Get posts:

GET /api/posts

Response:
[
  {
    "id": 1,
    "title": "The Joys of Async/Await",
    "description": "A deep dive into non-blocking operations in C#.",
    "creationDate": "2025-10-25T15:03:04",
    "username": "alice_codes",
    "tags": ["C#", "Async"]
  }
]

Create post:

POST /api/posts
{
  "title": "PostgreSQL vs MySQL",
  "description": "A performance comparison",
  "username": "bob_devs"
}

Response:
{
  "id": 2,
  "title": "PostgreSQL vs MySQL",
  "description": "A performance comparison",
  "username": "bob_devs",
  "creationDate": "06/11/2025 10:30:41",
  "tags": []
}

Get specific post:

GET /api/posts/2

Response:
[
  {
    "id": 2,
    "title": "PostgreSQL vs MySQL: A Performance Review",
    "description": "Comparing the speed and features of two popular databases.",
    "username": "alice_codes",
    "creationDate": "22/10/2025 22:00:00",
    "tags": [
      "PostgreSQL",
      "Database"
    ]
  }
]

Advanced Features

Output Structure Transformation

You can define complex nested JSON structures. Queries can be linked through a linkColumn.

    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts;
      tags:
        connectionName: blog
        sql: >
          select *
          from tags;
    outputStructure:
      type: Object
      isArray: true
      queryName: posts
      fields:
        - { type: Long, name: id, columnName: post_id }
        - { type: String, name: title, columnName: title }
        - { type: String, name: description, columnName: description }
        - { type: String, name: username, columnName: username }
        - { type: String, name: creationDate, columnName: creation_date }
        - type: string
          isArray: true
          name: tags
          queryName: tags
          columnName: tag
          linkColumn: post_id

Parameter Capture

Capture output from write operations:

writeOperations:
  - connectionName: postgres1
    sql: "INSERT INTO posts ... RETURNING id"
    outputCaptures:
      - columnName: id
        parameterName: postId
  - connectionName: postgres1
    sql: "INSERT INTO tags (post_id, tag) VALUES (@postId, @tag)"

Transaction Support

Multiple write operations are automatically wrapped in a transaction:

writeOperations:
  - connectionName: postgres1
    sql: "INSERT INTO posts ..."
  - connectionName: postgres1
    sql: "INSERT INTO tags ..."  # Rolled back if posts insert fails

Parameter Binding

• Route parameters: {id} in path
• Query parameters: ?search=term
• Request body: JSON object or value

Development Setup

1. Clone the repository
2. Install .NET 9.0 SDK
3. Run tests:

dotnet test

Note that docker should be running to be able to run the integration tests with test containers.

4. Start the API:

cd src/RestSQL.Api
dotnet run

For more examples, check out the integration tests in the tests/RestSQL.IntegrationTests directory.

Supported Databases

• PostgreSQL
• SQL Server
• MySQL
• Oracle
• SQLite