RestSQL

Overview

RestSQL is a versatile .NET tool designed to transform SQL queries defined in YAML into fully functional REST endpoints. This project can be deployed as a standalone API or integrated as a library within existing applications. It supports transactions, delivers nested JSON output, and accommodates multiple database providers, making it a robust solution for web services.

Key Features

• Flexible Architecture: Operates as both a standalone API and a library for smooth integration into existing projects.
• YAML-based Configuration: Utilize YAML files for easy configuration of database connections and endpoint definitions.
• Multi-database Support: Compatible with several databases, including PostgreSQL, SQL Server, MySQL, Oracle, and SQLite.
• Transaction Management: Automatically manages multiple write operations within a single transaction, ensuring data integrity.
• Nested JSON Output: Seamlessly create complex nested JSON structures in API responses.

Getting Started

RestSQL offers two primary usage options:

Standalone API (RestSQL.Api)

Quickly deploy a pre-built API project by configuring the appsettings.json:

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

Run the API with:

cd src/RestSQL.Api
dotnet run

Integration in Existing Project

To integrate RestSQL into an existing ASP.NET Core project, simply add the library:

// Program.cs
builder.Services.AddRestSQL();
app.UseRestSQL("path/to/config/folder");

Configuration Overview

Configuration in RestSQL is handled via YAML files, including sections for database connections and API endpoint definitions:

Example Configuration

Database 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"

Endpoint Configuration:

endpoints:
  - path: /api/posts
    method: GET
    statusCode: 200
    sqlQueries:
      posts:
        connectionName: blog
        sql: >
          select id post_id, title, description, creation_date, username
          from posts;
    outputStructure:
      type: Object
      isArray: true
      queryName: posts
      fields:
        - { type: Long, name: id, columnName: post_id }
        - { type: String, name: title, columnName: title }

Sample API 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 a New 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": []
}

Advanced Features

• Output Structure Transformation: Define intricate nested JSON outputs by linking SQL queries through a common key.
• Parameter Capture: Efficiently manage output from write operations.
• Parameter Binding: Supports capturing parameters from route data and query strings, enhancing flexibility in data handling.

For more information and usage examples, refer to the development setup and further documentation in the project repository.