Quickstart: Use Data API builder with SQL
In this Quickstart, you build a set of Data API builder configuration files to target a local SQL database.
Prerequisites
- Docker
- .NET 8
- A data management client
- If you don't have a client installed, install Azure Data Studio
Tip
Alternatively, open this Quickstart in GitHub Codespaces with all developer prerequisites already installed. Simply bring your own Azure subscription. GitHub accounts include an entitlement of storage and core hours at no cost. For more information, see included storage and core hours for GitHub accounts.
Install the Data API builder CLI
Install the Microsoft.DataApiBuilder
package from NuGet as a .NET tool.
Use
dotnet tool install
to install the latest version of theMicrosoft.DataApiBuilder
with the--global
argument.dotnet tool install --global Microsoft.DataApiBuilder
Note
If the package is already installed, you will update the package instead using
dotnet tool update
.dotnet tool update --global Microsoft.DataApiBuilder
Verify that the tool is installed with
dotnet tool list
using the--global
argument.dotnet tool list --global
Configure the local database
Start by configuring and running the local database to set the relevant credentials. Then, you can seed the database with sample data.
Get the latest copy of the
mcr.microsoft.com/mssql/server:2022-latest
container image from Docker Hub.docker pull mcr.microsoft.com/mssql/server:2022-latest
Start the docker container by setting the password, accepting the end-user license agreement (EULA), and publishing port 1433. Replace
<your-password>
with a custom password.docker run \ --env "ACCEPT_EULA=Y" \ --env "MSSQL_SA_PASSWORD=<your-password>" \ --publish 1433:1433 \ --detach \ mcr.microsoft.com/mssql/server:2022-latest
Connect to your local database using your preferred data management environment. Examples include, but aren't limited to: SQL Server Management Studio, Azure Data Studio, and the SQL Server extension for Visual Studio Code.
Tip
If you're using default networking for your Docker Linux container images, the connection string will likely be
Server=localhost,1433;User Id=sa;Password=<your-password>;TrustServerCertificate=True;Encrypt=True;
. Replace<your-password>
with the password you set earlier.Create a new
bookshelf
database and use the database for your remaining queries.DROP DATABASE IF EXISTS bookshelf; GO CREATE DATABASE bookshelf; GO USE bookshelf; GO
Create a new
dbo.authors
table and seed the table with basic data.DROP TABLE IF EXISTS dbo.authors; GO CREATE TABLE dbo.authors ( id int not null primary key, first_name nvarchar(100) not null, middle_name nvarchar(100) null, last_name nvarchar(100) not null ) GO INSERT INTO dbo.authors VALUES (01, 'Henry', null, 'Ross'), (02, 'Jacob', 'A.', 'Hancock'), (03, 'Sydney', null, 'Mattos'), (04, 'Jordan', null, 'Mitchell'), (05, 'Victoria', null, 'Burke'), (06, 'Vance', null, 'DeLeon'), (07, 'Reed', null, 'Flores'), (08, 'Felix', null, 'Henderson'), (09, 'Avery', null, 'Howard'), (10, 'Violet', null, 'Martinez') GO
Create configuration files
Create a baseline configuration file using the DAB CLI. Then, add a development configuration file with your current credentials.
Create a typical configuration file using
dab init
. Add the--connection-string
argument with your database connection string from the first section. Replace<your-password>
with the password you set earlier in this guide. Also, add theDatabase=bookshelf
value to the connection string.dab init --database-type "mssql" --host-mode "Development" --connection-string "Server=localhost,1433;User Id=sa;Database=bookshelf;Password=<your-password>;TrustServerCertificate=True;Encrypt=True;"
Add an Author entity using
dab add
.dab add Author --source "dbo.authors" --permissions "anonymous:*"
Test API with the local database
Now, start the Data API builder tool to validate that your configuration files are merged during development.
Use
dab start
to run the tool and create API endpoints for your entity.dab start
The output of the tool should include the address to use to navigate to the running API.
Successfully completed runtime initialization. info: Microsoft.Hosting.Lifetime[14] Now listening on: <http://localhost:5000> info: Microsoft.Hosting.Lifetime[0]
Tip
In this example, the application is running on
localhost
at port 5000. Your running application may have a different address and port.First, try the API manually by issuing a GET request to
/api/Author
.Tip
In this example, the URL would be
https://localhost:5000/api/Author
. You can navigate to this URL using your web browser.Next, navigate to the Swagger documentation page at
/swagger
.Tip
In this example, the URL would be
https://localhost:5000/swagger
. Again, you can navigate to this URL using your web browser.