I ran across the following on BlueSky yesterday, and it directly inspired this post:
Keeping it chill in the API, as usual
— 🇵🇹 snipe, lixo tóxico â‘â’â’â’â’ (@snipe.lol) February 20, 2025 at 6:28 AM
[image or embed]
Deleting things can have some pretty big impacts downstream. Like, what happens if you delete a warehouse that happens to have … all of your products in it?
Orphaned data can be a big problem, so what do we do to prevent this?
In a database we have Foreign Keys
I can’t just go and DELETE a department, if that department has EMPLOYEE records referring to that DEPARTMENT table record.
Here’s what that looks like via cURL:
Jeffreys-Mac-mini:downloads thatjeffsmith$ curl -v --location --request DELETE 'https://in-the-cloud.com/ords/hrrest/staffing/departments/10'
>
* Request completely sent off
< HTTP/1.1 555 555
< Date: Thu, 20 Feb 2025 14:13:28 GMT
< Content-Type: application/problem+json
< Content-Length: 782
< Connection: keep-alive
<
{
"code": "UserDefinedResourceError",
"title": "User Defined Resource Error",
"message": "The request could not be processed for a user defined resource",
"o:errorCode": "ORDS-25001",
"cause": "An error occurred when evaluating a SQL statement associated with this resource. SQL Error Code 2292, Error Message: ORA-02292: integrity constraint (HRREST.EMP_DEPT_FK) violated - child record found\n\nhttps://docs.oracle.com/error-help/db/ora-02292/",
"action": "Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource",
"type": "tag:oracle.com,2020:error/UserDefinedResourceError",
"instance": "tag:oracle.com,2020:ecid/98375da7ee8042fde6250b5c238c2411"
* Connection #0 to host in-the-cloud.com left intactThe EMP_DEPT_FK constraint has prevented the database from deleting the record.
About ORDS Status Code 555
User Defined Resource Error – the ‘user defined resource’ is your REST API, and the database code you used to implement this API has ran into an error.
Ok, but…
I don’t want database details being included on my API responses
Fair. In fact, I would say, YES – DO NOT DO THAT, unless the parties using your APIs are meant to be working with or aware of the underlying databases.
We can provide a better experience for the API consumers AND protect the database by coding the API to handle this use case.
Exceptions to the rescue!
I’ve talked about this before, but for implementing a GET vs a DELETE.
I’ve initially built the laziest API I could imagine for doing a DELETE, when it comes to a row in a table in your database.
A simple delete, and it assumes what you’re looking to delete, is there, and is gonna be deleted.
If we encounter the ORA-02292: integrity constraint violation because it would orphan rows, we can code for that.
Ok, so now we’re trapping the ORA-02292 with an Exception, and we’re returning a 422 ‘Unprocessable Entity’ vs a 555 ‘your code had a boo-boo’ status code, and a message saying why your request failed.
Wait, what’s a 422? NSFW Description. I originally did this with a 400, but this StackOverflow conversation/debate reminded me that picking the right 4xx is half the fun of being a developer.
I’m using parameters to define the output if the request, fails. So we’ll get a message saying ‘error,’ and we’ll have another attribute called ‘message’ that prints why the request wasn’t honored.
Jeffreys-Mac-mini:downloads thatjeffsmith$ curl -v --location --request DELETE 'https://in-the-cloud.com/ords/hrrest/staffing/departments/30'
>
* Request completely sent off
< HTTP/1.1 422 Unprocessable Entity
< Date: Thu, 20 Feb 2025 20:49:09 GMT
< Content-Type: application/json
< Transfer-Encoding: chunked
< Connection: keep-alive
< Strict-Transport-Security: max-age=31536000;includeSubDomains
< X-Content-Type-Options: nosniff
<
* Connection #0 to host in-the-cloud.com left intact
{
"status":"error",
"message":"This department still has 6 employees assigned, cannot delete."
}
So we’re not showing anything to indicate that we’re in an Oracle Database, nor any database object names, nor any Oracle-specific error messages.
What we ARE exposing is the number of employees in Department 30. We might not want to do that, but I’m mirroring Snipe’s API response format, so I am sharing that info with the API consumer.
But Jeff, we don’t have any foreign keys!
Ew, ok, then you’re DELETE API will need to run some SQL to confirm you’re not orphaning any rows in any ‘related’ tables, and only then issue the DELETE.
The Code
-- Generated by ORDS REST Data Services 24.3.2.r3121009
-- Schema: HRREST Date: Thu Feb 20 09:33:00 2025
--
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'HRREST',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'hrrest',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'staffing',
p_base_path => '/staffing/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'staffing',
p_pattern => 'departments/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'staffing',
p_pattern => 'departments/:id',
p_method => 'DELETE',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'DECLARE
child_exists exception;
pragma exception_init( child_exists, -2292 );
existing_count integer;
begin
delete from departments
where department_id = :id;
exception
when child_exists THEN
select count(*) into existing_count from employees where department_id = :id;
:status_code := 422;
:status := ''error'';
:message := ''This department still has '' || existing_count || '' employees assigned, cannot delete.'';
end;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'staffing',
p_pattern => 'departments/:id',
p_method => 'DELETE',
p_name => 'message',
p_bind_variable_name => 'message',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'staffing',
p_pattern => 'departments/:id',
p_method => 'DELETE',
p_name => 'status',
p_bind_variable_name => 'status',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
COMMIT;
END;
