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.
$ dotnet add package Comet1.RestSQLRestSQL 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.
There are two ways to use RestSQL:
The simplest way to get started is using the pre-built API project:
appsettings.json:{
"RestSQL": {
"ConfigFolder": "path/to/your/yaml/configs"
},
"Serilog": {
"MinimumLevel": {
"Default": "Information"
},
"WriteTo": [
{
"Name": "Console"
}
]
}
}
cd src/RestSQL.Api
dotnet run
Add RestSQL to your ASP.NET Core project:
// Program.cs
builder.Services.AddRestSQL();
// Configure middleware
app.UseRestSQL("path/to/config/folder");
RestSQL uses YAML files for configuration. You need two main configuration sections:
You can split these across files as you see fit, they will be merged. A single file is also fine.
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"
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_idLet'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: *postFieldsGet 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"
]
}
]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_idCapture 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)"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{id} in path?search=termdotnet testNote that docker should be running to be able to run the integration tests with test containers.
cd src/RestSQL.Api
dotnet runFor more examples, check out the integration tests in the tests/RestSQL.IntegrationTests directory.