RestSQL: 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.
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, multiple database providers, and OAuth 2.0/OpenID Connect (OIDC) authentication.
Getting Started
There are two ways to use RestSQL:
1. Standalone API (Using RestSQL.Api)
Build from source
The simplest way to get started is using the pre-built API project:
Set the RestSQL:ConfigFolder key using the environment variable name RestSQL__ConfigFolder (double underscore maps to a colon in ASP.NET configuration).
Example Docker run (mount local configs and set env):
Linux Docker (host.docker.internal): older Docker engines on Linux don't provide host.docker.internal by default. You can add it at container startup with --add-host (Docker 20.10+ with host-gateway):
For secured endpoints, define your OIDC/OAuth 2.0 configuration globally under authentication. This section is optional.
authentication:
authority: https://auth.example.com/
audience: https://api.example.com
scopes:
- write:posts
### Endpoint Configuration
Define REST endpoints. Endpoints can include optional authorization settings.
## Example Blog API
Let's look at a complete blog post API example:
```yaml
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
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 (with Authorization):
You must include an Authorization: Bearer <token> header where the token grants the write:posts scope.
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 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.
