I ran throughout the next on BlueSky yesterday, and it instantly impressed this publish:
Protecting it chill within the API, as regular
— 🇵🇹 snipe, lixo tóxico ⭑⭒⭒⭒⭒ (@snipe.lol) February 20, 2025 at 6:28 AM
Deleting issues can have some fairly huge impacts downstream. Like, what occurs when you delete a warehouse that occurs to have … your entire merchandise in it?
Orphaned knowledge is usually a huge downside, so what can we do to forestall this?
In a database now we have Overseas Keys
I can’t simply go and DELETE a division, if that division has EMPLOYEE data referring to that DEPARTMENT desk document.
Right here’s what that appears like through cURL:
Jeffreys-Mac-mini:downloads thatjeffsmith$ curl -v --location --request DELETE 'https://in-the-cloud.com/ords/hrrest/staffing/departments/10'
>
* Request utterly despatched off
HTTP/1.1 555 555
Date: Thu, 20 Feb 2025 14:13:28 GMT
Content material-Sort: software/downside+json
Content material-Size: 782
Connection: keep-alive
{
"code": "UserDefinedResourceError",
"title": "Person Outlined Useful resource Error",
"message": "The request couldn't be processed for a consumer outlined useful resource",
"o:errorCode": "ORDS-25001",
"trigger": "An error occurred when evaluating a SQL assertion related to this useful resource. SQL Error Code 2292, Error Message: ORA-02292: integrity constraint (HRREST.EMP_DEPT_FK) violated - youngster document foundnnhttps://docs.oracle.com/error-help/db/ora-02292/",
"motion": "Confirm that the URI and payload are appropriately specified for the requested operation. If the problem persists then please contact the creator of the useful resource",
"sort": "tag:oracle.com,2020:error/UserDefinedResourceError",
"occasion": "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 document.
About ORDS Standing Code 555
Person Outlined Useful resource Error – the ‘consumer outlined useful resource’ is your REST API, and the database code you used to implement this API has bumped into an error.
Okay, however…
I don’t need database particulars being included on my API responses
Honest. The truth is, I might say, YES – DO NOT DO THAT, except the events utilizing your APIs are supposed to be working with or conscious of the underlying databases.
We will present a greater expertise for the API customers AND shield the database by coding the API to deal with this use case.
Exceptions to the rescue!
I’ve talked about this earlier than, however for implementing a GET vs a DELETE.
I’ve initially constructed the laziest API I may think about for doing a DELETE, on the subject of a row in a desk in your database.
A easy delete, and it assumes what you’re trying to delete, is there, and is gonna be deleted.
If we encounter the ORA-02292: integrity constraint violation as a result of it will orphan rows, we are able to code for that.
Okay, 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’ standing code, and a message saying why your request failed.
Wait, what’s a 422? NSFW Description. I initially did this with a 400, however this StackOverflow dialog/debate jogged my memory that choosing the right 4xx is half the enjoyable of being a developer.
I’m utilizing parameters to outline the output if the request, fails. So we’ll get a message saying ‘error,’ and we’ll have one other attribute referred to as ‘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 utterly despatched off
HTTP/1.1 422 Unprocessable Entity
Date: Thu, 20 Feb 2025 20:49:09 GMT
Content material-Sort: software/json
Switch-Encoding: chunked
Connection: keep-alive
Strict-Transport-Safety: max-age=31536000;includeSubDomains
X-Content material-Sort-Choices: nosniff
* Connection #0 to host in-the-cloud.com left intact
{
"standing":"error",
"message":"This division nonetheless has 6 workers assigned, can't delete."
}
So we’re not exhibiting something to point that we’re in an Oracle Database, nor any database object names, nor any Oracle-specific error messages.
What we ARE exposing is the variety of workers in Division 30. We would not need to try this, however I’m mirroring Snipe’s API response format, so I’m sharing that information with the API client.
However Jeff, we don’t have any overseas keys!
Ew, okay, then you definitely’re DELETE API might want to run some SQL to substantiate you’re not orphaning any rows in any ‘associated’ tables, and solely then difficulty the DELETE.
The Code
-- Generated by ORDS REST Knowledge Providers 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;
start
delete from departments
the place department_id = :id;
exception
when child_exists THEN
choose depend(*) into existing_count from workers the place department_id = :id;
:status_code := 422;
:standing := ''error'';
:message := ''This division nonetheless has '' || existing_count || '' workers assigned, can't delete.'';
finish;');
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 => 'standing',
p_bind_variable_name => 'standing',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
COMMIT;
END;
