Generate OpenApi/RestApi endpoints for your database in under an hour

general open-api csharp

Introduction

This article details a list of steps and tools that can be used to quickly expose your data as a RestApi/OpenApi endpoint.

The following are the steps:-

  1. Select a database. I am going to use the AdventureWorks database.

  2. Use EntityFrameworkCore.Generator to generate the Data Api Builder command file. Something like this:- data-config.cmd.

  3. Download the dotnet tool microsoft.dataapibuilder.

  4. Generate the dab-config.json from the cmd file.

  5. Run the Data API Builder.

  6. Access the open-api yaml file using http://localhost:5000/swagger

  7. Validate the generated yaml file using redocly. Sample command:- redocly lint openapi.yaml

  8. Generate documentation - redocly build-docs openapi.yaml

  9. Use openapi-to-postman to generate a postman collection.

  10. Test the application using Postman.

  11. Use Kiota to generate a C# api client.

Select a database

Select your own database.

In my case, I use docker for the adventureworks database.

You can do the same by following chriseaton/docker-adventureworks.

docker run -p 1433:1433 \
 -e 'ACCEPT_EULA=Y' \
 -e 'MSSQL_SA_PASSWORD=My_password1' \
 -d chriseaton/adventureworks:latest

Generate and Execute the dab-config.cmd file

Once you have the database up and running using Docker, make sure you can connect to it using SSMS or any other tool you use to connect to the database.

We will now use the EntityFrameworkCore.Generator to generate the dab cmd file.

Install the tool:- dotnet tool install --global EntityFrameworkCore.Generator --prerelease.

Generate the dab-config.cmd file:- efg generate -f .\entity-generation.yml

The sample entity-generation.yml file. The sample yaml-entity.csx file.

Make sure that the connection string is correct in the entity-generation.yml file.

You can delete the Data folder that gets generated and only keep the cmd folder.

Merge the txt files to generated the cmd file. Here is the powershell command for that:- Get-Content -Path *.txt | Set-Content dab-config.cmd

Add the following lines at the top of the cmd file. Make sure you add the correct connection string.

@echo off

del /q dab-config.json 2>nul

dab init --database-type mssql --connection-string "@env('MSSQL_CONNECTION_STRING')" --config dab-config.json --host-mode development

Execute the cmd file which will generate the dab-config.json file

Start the application

dab start --config dab-config.json --verbose

Download the openapi file

http://localhost:5000/swagger

Validate the openapi file

redocly lint openapi.yaml

Generate the documentation

redocly build-docs openapi.yaml

Generate the postman file

npx openapi2postmanv2 -s spec.yaml -o collection.json -p -O folderStrategy=Tags,includeAuthInfoInExample=false

Use Kiota to generate a C# API client

kiota generate -l CSharp -c AdventureWorksApiClient -n KiotaClient.AdventureWorks.Api -d ../openapi.yaml -o ./Client

Source Code

Github - db-to-api