Quickstart: Use Data API builder with MySQL

In this Quickstart, you build a set of Data API builder configuration files to target a local MySQL database.

Prerequisites

• Docker
• .NET 8
• A data management client
  • If you don't have a client installed, install Azure Data Studio

Install the Data API builder CLI

Install the Microsoft.DataApiBuilder package from NuGet as a .NET tool.

1. Use dotnet tool install to install the latest version of the Microsoft.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
   

2. 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. Then, you can seed a new container with sample data.

1. Get the latest copy of the mysql:8 container image from Docker Hub.

   docker pull mysql:8
   

2. Start the docker container by setting the password and publishing port 3306. Replace <your-password> with a custom password.

   docker run \
       --publish 3306:3306 \
       --env "MYSQL_ROOT_PASSWORD=<your-password>" \
       --detach \
       mysql:8
   

3. Connect to your local database using your preferred data management environment. Examples include, but aren't limited to: MySQL Workbench, Azure Data Studio, and the MySQL shell 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;Port=3306;Uid=root;Pwd=<your-password>;. Replace <your-password> with the password you set earlier.

4. Create a new bookshelf database and use the database for your remaining queries.

   CREATE DATABASE IF NOT EXISTS bookshelf;
   
   USE bookshelf;
   

5. Create a new dbo.authors table and seed the table with basic data.

   CREATE TABLE IF NOT EXISTS authors
   (
       id INT NOT NULL PRIMARY KEY,
       first_name VARCHAR(100) NOT NULL,
       middle_name VARCHAR(100),
       last_name VARCHAR(100) NOT NULL
   );
   
   INSERT INTO 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');
   

Create configuration files

Create a baseline configuration file using the DAB CLI. Then, add a development configuration file with your current credentials.

1. 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 the Database=bookshelf value to the connection string.

   dab init --database-type "mysql" --host-mode "Development" --connection-string "Server=localhost;Port=3306;Database=bookshelf;Uid=root;Pwd=<your-password>;"
   

2. Add an Author entity using dab add.

   dab add Author --source "authors" --permissions "anonymous:*"
   

3. Observe your current dab-config.json configuration file. The file should include a baseline implementation of your API with a single entity, a REST API endpoint, and a GraphQL endpoint.

Test API with the local database

Now, start the Data API builder tool to validate that your configuration files are merged during development.

1. Use dab start to run the tool and create API endpoints for your entity.

   dab start
   

2. 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.

3. 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.

4. 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.

Next step