# Generating a MySQL-backed API

To generate a MySQL-backed API, login to your DreamFactory instance using an administrator account and click on the Services tab:

On the left side of the interface you'll see the Create button. Click this button to begin generating an API. You'll be presented with a single dropdown form control titled Select Service Type. You'll use this dropdown to both generate new APIs and configure additional authentication options. There's a lot to review in this menu, but for the moment let's stay on track and just navigate to Databases and then MySQL:

After selecting MySQL, you'll be presented with the following form:

Let's review these fields:

  • Name: The name will form part of your API URL, so you'll want to use a lowercase string with no spaces or special characters. Further, you'll want to typically choose something which allows you to easily identify the API's purpose. For instance for your MySQL-backed API you might choose a name such as mysql, corporate, or store.Keep in mind lowercasing the name is a requirement.
  • Label: The label is used for referential purposes within the administration interface and system-related API responses. You can use something less terse here, such as "MySQL-backed Corporate Database API".
  • Description: Like the label, the description is used for referential purposes within the administration interface and system-related API responses.
  • Active: This determines whether the API is active. By default it is set to active however if you're not yet ready to begin using the API or would like to later temporarily disable it, just return to this screen and toggle the checkbox.

After completing these fields, click on the Config tab located at the top of the interface. You'll be presented with the following form (I'll only present the top of the form since this one is fairly long):

This form might look a bit intimidating at first, however in most cases there are only a few fields you'll need to complete. Let's cover those first, followed by an overview of the optional fields.

# Required Configuration Fields

There are only five (sometimes six) fields which need to be completed in order to generate a database-backed API. These include:

  • Host: The database server's host address. This may be an IP address or domain name.
  • Port Number: The database server's port number. For instance on MySQL this is 3306.
  • Database: The name of the database you'd like to expose via the API.
  • Username: The username associated with the database user account used to connect to the database.
  • Password: The password associated with the database user account used to connect to the database.
  • Schema: If your database supports the concept of a schema, you may specify it here. MySQL doesn't support the concept of a schema, but many other databases do.

WARNING

Keep in mind you'll be generating an API which can in fact interact with the underlying database! While perhaps obvious, once you generate this API it means any data or schema manipulation requests you subsequently issue will in fact affect your database. Therefore be sure to connect to a test database when first experimenting with DreamFactory so you don't wind up issuing a request that you later come to regret.

# Optional Configuration Fields

Following the required fields you'll find a number of optional parameters. These can and do vary slightly according to the type of database you've selected, so don't be surprised if you see some variation below. Don't worry about this too much at the moment, because chances are you're not going to need to modify any of the optional configuration fields at this point in time. However we'd like to identify a few fields which are used more often than others:

  • Maximum Records: You can use this field to place an upper limit on the number of records returned.
  • Data Retrieval Caching Enabled: Enabling caching will dramatically improve performance. This field is used in conjunction with Cache Time to Live, introduced next.
  • Cache Time to Live (minutes): If data caching is enabled, you can use this field to specify the cache lifetime in minutes.

After completing the required fields in addition to any desired optional fields, press the Save button to generate your API. After a moment you'll see a pop up message indicating Service Saved Successfully. Congratulations you've just generated your first database-backed API! So what can you do with this cool new toy? Read on to learn more.

# A Note About API Capabilities

Most databases employ a user authorization system which gives administrators the ability to determine exactly what a user can do after successfully establishing a connection. In the case of MySQL, privileges are used for this purpose. Administrators can grant and revoke user privileges, and in doing so determine what databases a user can connect to, whether the user can create, retrieve, update, and delete records, and whether the user has the ability to manage the schema.

Because DreamFactory connects to your database on behalf of this user, the resulting API is logically constrained by that user's authorized capabilities. DreamFactory will however display a complete set of Swagger documentation regardless, so if you are attempting to interact with the API via the Swagger docs or via any other client and aren't obtaining the desired outcome, be sure to check your database user permissions to confirm the user can indeed carry out the desired task.

Further, keep in mind this can serve as an excellent way to further lock down your API. Although as you'll later learn DreamFactory offers some excellent security-related features for restricting API access, it certainly wouldn't hurt to additionally configure the connecting database user's privileges to reflect the desired API capabilities. For instance, if you intend for the API to be read-only, then create a database user with read-only authorization. If API read and create capabilities are desired, then configure the user accordingly.