MySQL is a database management system that allows you to manage relational databases. A lot of users are using MySQL as their database engine, so it's no surprise that creating a MySQL REST API is highly in demand. Therefore, SlashAPI provide a connector to allow user build and publish a MySQL API.
After you create an account and log in to the dashboard, choose MySQL service on the collections page. To make MySQL API you need to provide your database credentials on the MySQL creation form.
The name of your table will be used as the API endpoint name. So, if database has a table called "users", the API URL will look like this:
https://v1.slashapi.com/<team>/mysql/<identifier>/users
To Get records from your MySQL table, you just need to put your table name to the base API URL.
For example, if you want to get records from your users table, your API endpoint will look like this:
<team>/mysql/<identifier>/users
In the GET records endpoint, you can use some of query parameters to allow you modify the fetched data. e.g sort by column, modify limit and pagination, etc.
The reserved word is order_by. order_by is used to sort the fetched data in either ascending or descending according to one or more columns.
/users?order_by=name
By default the order_by sorts the data in ascending order. You can use the keyword desc after the column name to sort the data in descending order.
/users?order_by=name.desc
You can also sort the fetched data using multiple columns, Multiple order uses a comma separated.
/users?order_by=name.asc,id.desc
SlashApi provides a page and per_page parameter that is used to paginate your data. SlashApi automatically takes care of setting the query's limit and offset based on the current page being viewed by the user. The current page is detected by the value of the page query string argument on the HTTP request.
To set the limit query, you need to set the per_page value in your query parameter to modify the number of items you would like to displayed. SlashApi set the default value for per_page option to 10. So, if you are not provide the per_page option in your API request, SlashApi will limit the number of items to 10.
Database queries are created as valid JSON documents. A query object consists of fields and operators that make up a complete query.
Query example:
/users?q={"name":"John"}
Multiple parameters;
/users?q={"name":"John","age":17}
| Abbr | Description | Example |
|---|---|---|
| $ne | Not equal | {"field":{"$ne":value}} |
| $like | Like operator | {"field":{"$like":value}} |
| $in | Match any value in array | {"field":{"$in":[value1,value2,...]}} |
| $nin | Not match any value in array | {"field":{"$nin":[value1,value2,...]}} |
| $or | Logical operator | {"$or":[{"field":"value"},{"field":"Another Value"}]} |
| $and | Logical operator | {"$and":[{"field":"value"},{"another_field":"value"}]} |
| Abbr | Description | Example |
|---|---|---|
| $gt | Greater than | {"field":{"$gt":value}} |
| $gte | Greater than equal | {"field":{"$gte":value}} |
| $lt | Less than | {"field":{"$lt":value}} |
| $lte | Less than equal | {"field":{"$lte":value}} |
| $between | Matches field value between two numeric values | {"field":{"$between":[1,5]}} |
Querying based on dates are done using the our predefined operator combined with date strings.
| Operator | Description |
|---|---|
| $date | Compare a column's value against a date |
| $day | Compare a column's value against a specific day |
| $month | Compare a column's value against a specific month |
| $year | Compare a column's value against a specific year |
Query Example:
// Query equal date
{"created_at":{"$date":"2021-07-01"}}
// Query between two dates
{"created_at":{"$gt":{"$date":"2021-07-01"},"$lt":{"$date":"2021-07-20"}}}
// Compare a column's value against a specific day:
{"created_at":{"$day":7}}
// Compare a column's value against a specific month:
{"created_at":{"$month":7}}
// Compare a column's value against a specific year:
{"created_at":{"$year":2021}}
You may not always want to select all columns from a database table. Using the select query parameter, you can specify a custom "select" clause for the query:
/users?select=id,first_name,last_name
Database tables are often related to one another. For example, a blog post may have many comments or an order could be related to the user who placed it. SlashApi also supports a variety of common relationship.
A one-to-many/Has Many relationship is used to define relationships where a single table is the parent to one or more child tables. For example, a blog post may have an infinite number of comments.
/posts?has_many=comments
SlashApi will automatically determine the proper foreign key column for the relation. By convention, SlashApi will take the "snake_case" name of the parent table and suffix it with _id. So, in the above example, SlashApi will assume the foreign key column on the comments table is post_id and the primary key column on the posts table is id
If your database not using the convention like above, you can define the foreignKey and localKey for the relation after the relation table like below:
/posts?has_many=comments(foreignKey|localKey)&primaryKey=primaryKey
For example, your primary key in the posts table is id_post, foreign key in the comments table is post_id, and the foreign key in the comments table is id_comment. Then, you can use the query string like below:
/posts?has_many=comments(post_id|id_comment)&primaryKey=id_post
A belongs_to relationship is a very basic type of database relationship. For example, a posts table might be associated with users table as the author. To define this relationship, you can use belongs_to followed with the relation table in the query parameter.
/posts?belongs_to=user
When you define belong_to parameter, SlashApi will attempt to find the relationship that has an id which matches the singular name of relation table suffixed with _id column on the parent table.
SlashApi determines the foreign key name by examining the name of the relationship table and suffixing the table name with _id. So, in the above case, SlashApi assumes that posts table has a user_id column. However, if the foreign key on the posts table is not user_id, you may pass a custom key name as the query parameter.
/posts?belongs_to=user(foreignKey|ownerKey)
You can also show multiple relations by define the relation table separated by comma.
/posts?has_many=first_relation_table,second_relation_table
You can also specify a custom "select" clause for the relationship tables by using a dot notation
/posts?has_many=comments&select=comments.id,comments.message
Sometimes, you need to filter your relationship data, you can define the relation table as an array with the name of the column as array key in the query parameter. For example, you want to show all post comments that have been approved, you can use comments.approved as a query parameter.
/posts?has_many=comments&q={"comments.approved":true}
Please refer to the Filtering section for the detail.
Sometimes you may want to count the number of related table for a given relationship without actually loading the data. To accomplish this, you may use the with_count query parameter. The with_count option which will place a {relation}_count attribute on the query result:
/posts?with_count=comments
The endpoint above will place comments_count field on the query result.
To get specific record of your table, you can specify the primary key as the identifier.
For example, if you want to get the records for posts table with id = 1, you can use the URL like this:
/posts/1
By default, SlashApi will use the id column as the identifier. If you are not using id as your identifier, you can can define the identifier in the URL path. For example, if you are using uuid column as the identifier, you can use the URL path like this:
/posts/uuid:123e4567-e89b-12d3-a456
or you can also use primaryKey query parameter to define the identifier.
/posts/123e4567-e89b-12d3-a456?primaryKey=uuid
SlashApi also provides an endpoint that may be used to insert records into your database table. This endpoint using the POST method and accepts an array of column names and values. Missing properties will be set to the default values when applicable.
SlashApi allows you to insert multiple rows with a single API request. You don't need to send multiple API requests for it. To insert multiple rows into your database table, you just need to pass an array in the data of your request body.
{
"data": [
{
"title": "First title",
"content": "First content"
},
{
"title": "Second title",
"content": "Second content"
}
]
}
{
"data": {
"title": "Some title",
"content": "Long text"
}
}
In order to return the value of your primary key, SlashApi needs to know the name of the primary key that you used. By default, SlashApi will use the id column as the primary key. If you are not using id as your primary key, you need to provide the primary key field in the request body. For example, if you are using uuid column as the primary key, you need to use a request body like below:
{
"data": {
"uuid": "123e4567-e89b-12d3-a456-426614174000",
"title": "Some title",
"content": "Long text"
},
"primaryKey": "uuid"
}
To update a row in a table, use the PATCH verb and specify the primary key as the identifier in your API URL path.
{
"data": {
"title": "Updated title",
"content": "Updated text"
}
}
When interacting with your database, SlashApi depends on the primary key of your table. By default, SlashApi will use id as the primary key. When updating data in your database, you need to provide the primary key that you used in your table (only if you are not using id as your primary key)
You can provide the primary key field using 2 options.
Endpoint path
In this example, we use uuid as the primary key.
/posts/uuid:123e4567-e89b-12d3-a456
Request Body
Another alternative, you can put the primary key name in request body
{
"data": {
"uuid": "123e4567-e89b-12d3-a456-426614174000",
"title": "Some title",
"content": "Long text"
},
"primaryKey": "uuid"
}
To Delete a row in a table, use the DELETE verb and specify the primary key as the identifier in your API URL path.