DreamFactory is best known for its ability to generate REST APIs for databases such as SQL Server, MySQL, and PostgreSQL. While it's undeniably convenient to gain immediate access to autogenerated API endpoints for interacting tables, views, and stored procedures, projects would quickly run aground if the capabilities ended there. For instance, developers will likely want to use database functions when querying the database, and will likely require transactional support in order to ensure multi-table insertions and queries are treated as an atomic unit. Fortunately DreamFactory supports transactions in REST APIs, and this post will present an example demonstrating this capability.
Consider a database that contains information about company supplies and their respective locations. The related tables might look like this:
CREATE TABLE `supplies` (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE locations (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name varchar(100) not null,
supply_id INT unsigned,
CONSTRAINT fk_supply
FOREIGN KEY (supply_id)
REFERENCES supplies(id)
);
I'm oversimplifying a bit, but the point is each supply found in the supplies table is associated with a location found in the locations table. Because a supply couldn't be found without an associated location, it's critical that this relationship is intact for each cataloged supply. Therefore you'll always want to insert a new record into the locations table when creating a new supplies record. And if either INSERT statement fails, the entire operation should be rolled back. Therefore when performing such a task via a REST API, you'll want to ensure two features are supported:
When DreamFactory generates a new database-backed REST API, it tries to learn everything it can about the schema, including the table relationships. DreamFactory will then assign convenient aliases to these foreign key relationships, allowing the client to subsequently use these aliases for actions such as table joins. They're also useful for nested inserts and nested updates, because you can specify the alias as part of a request payload:
{
"resource": [
{
"name": "Stapler",
"locations_by_supply_id": [
{
"name": "Closet"
}
]
}
]
}
If you pass this payload to POST https://example.com/api/v2/some_database/supplies, you should subsequently see new records in the supplies and locations tables. But what if the payload were incorrectly defined, and the client specified title as the locations attribute instead of name? If this were to occur, the supplies table insert request would succeed but the locations insert request would fail! DreamFactory would return an error message that looks like this: Batch Error: Not all requested records could be created.
This unexpected outcome can be remedied by passing rollback=true along as a URL parameter:
<code>POST https://example.com/api/v2/some_database/supplies?rollback=true</code>
Calling the endpoint with a malformed payload will now cause both queries to be rolled back should either query fail. It really is that simple!