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:
- Nested inserts
- Transactional support
Nested Inserts
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.
API Transactions and the rollback Parameter
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!