Name: Comet1.RestSQL
Author: comet1

Comet1.RestSQL v0.0.15 — nugetz

docker run --rm -p 7017:8080 \
  -v $(pwd)/my-yaml-configs:/app/config \
  -e RestSQL__ConfigFolder=/app/config \
  cometone/restsql:latest

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

// Optional: Configure Authentication (read from appsettings.json or global YAML)
builder.Services.AddRestSQLAuthentication(builder.Configuration);

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

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"

authentication:
  authority: https://auth.example.com/
  audience: https://api.example.com
  scopes:
    - write:posts

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
  # Requires authorization
  - path: /api/posts
    method: POST
    statusCode: 200
    authorize: true                 # Requires a valid JWT bearer token
    authorizationScope: write:posts # Token must contain this scope claim
    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

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

POST /api/posts
Authorization: Bearer <JWT_TOKEN_WITH_WRITE:POSTS_SCOPE>

{
  "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 /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"
    ]
  }
]

    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

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

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

dotnet test

cd src/RestSQL.Api
dotnet run

comet1/Comet1.RestSQLv0.0.15

Get Started

Readme

RestSQL

Getting Started

1. Standalone API (Using RestSQL.Api)

Build from source

Alternatively, use docker

2. Library Usage (Adding to Existing Project)

Configuration

Database Connections

Authentication Configuration

Endpoint Configuration

Example Blog API

Making Requests

Advanced Features

Output Structure Transformation

Parameter Capture

Transaction Support

Parameter Binding

Development Setup

Supported Databases