DEV Community: Zulfiqar Ali

How to call external API from SQL server trigger

Zulfiqar Ali — Wed, 08 Mar 2023 12:21:58 +0000

Enabling Advanced Options and xp_cmdshell in SQL Server

SQL Server is a relational database management system that supports various advanced features.
However, some of these features are disabled by default for security reasons.
In this blog post,
we will learn how to enable advanced options and xp_cmdshell in SQL Server and use them to call an
external API inside a trigger.

Enabling Advanced Options

To enable advanced options in SQL Server, execute the following code:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

The above code enables advanced options, including xp_cmdshell, which is disabled by default.

Enabling xp_cmdshell

Note: that using xp_cmdshell to run external commands from within SQL Server can pose security risks and should be used
with caution.
Make sure to thoroughly vet and validate any external commands or APIs before using them within SQL Server.

The xp_cmdshell is a system stored procedure in SQL Server that allows running command-line
commands on the operating system. However, enabling xp_cmdshell can be a security risk as it can
allow malicious users to execute harmful commands. Therefore, it should be enabled only when needed.

To enable xp_cmdshell, execute the following code:

sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

The above code enables xp_cmdshell, allowing you to run external commands from within SQL Server.

Using CURL for API Requests

Once xp_cmdshell is enabled, you can use it to run external commands from within SQL Server.
One way to make HTTP requests to external APIs is to use the command-line tool curl.

Here's an example of how to use curl to send a request and receive a response within a SQL Server trigger:

CREATE TRIGGER MyTrigger ON MyTable
AFTER INSERT
AS
BEGIN
    DECLARE @url VARCHAR(1000);
    DECLARE @cmd VARCHAR(2000);
    DECLARE @response VARCHAR(MAX);

    -- set the URL to the API endpoint
    SET @url = 'https://api.example.com/some-endpoint';

    -- build the curl command with necessary parameters
    SET @cmd = 'curl -X POST -H "Content-Type: application/json" -d ''{ "param1": "value1", "param2": "value2" }'' ' + @url;

    -- execute the curl command and store the response
    EXEC @response = xp_cmdshell @cmd;

    -- process the response as needed
    -- ...
END

In the above code, we create a trigger named MyTrigger on a table named MyTable, which runs after an insert operation
on the table. Within the trigger, we declare three variables, @url, @cmd, and @response, which will be used to store the
API endpoint URL, the curl command, and the API response, respectively.

We set the @url variable to the API endpoint URL, and then we build the curl command in the @cmd variable.
In this example, we are using the POST method, sending a JSON payload with two parameters, "param1" and "param2"
and setting the "Content-Type" header to "application/json."

We then execute the curl command using the xp_cmdshell stored procedure, and store the API response in the @response
variable.

Finally, we can process the API response as needed within the trigger.

That's pretty much it!
For more articles like this, follow my blog at JuniorDev Blog
Thanks for reading :)

Routinejs, An Express inspired blazingly fast Nodejs router

Zulfiqar Ali — Sun, 16 Oct 2022 03:36:23 +0000

Hello there,
I am here to introduce you to Routine, An Express inspired and compatible Nodejs router.

Documentation is still a work in progress but the core framework api is stable and ready to use.
Please consider leaving a star at Routine's Github repo

Some high level features of Routine

✅ It is 5 times faster than Express (benchmark code is also available within the repo)
✅ Typescript support is built-in
✅ Radix trie based router
✅ Supports regex as url-path
✅ Middleware and nested route support
✅ Supports almost all existing Expressjs plugins/middlewares
✅ Built-in body parser and cookie-parser
✅ Built in json responder using fast-safe-stringify which is faster than native JSON.stringify and also detects circular objects

Benchmark comparing Routine with Express and Hapi, Koa was also tested but it failed to process so many requests and kept giving ERPIPE error, You can run these benchmarks yourself as well, simply refer to the benchmarks folder within the repo

Code examples below are also available on CodeSandbox here

Let's start with a simple Hello World example in Typescript

//Importing Routine class along with some types
import Routine, { Request, Response } from "@juniordev/routinejs";

//Creating a new instance of Routine
const app = new Routine();

//Registering a GET route on path '/'
app.get(`/`, (req: Request, res: Response) => {
  res.json({
    msg: "Hello Routine"
  });
});

//Starting the server on PORT 8080, default port is also 8080
app.listen(8080);

This starts the server at http://localhost:8080 and when we visit this url, we receive {"msg": "Hello Routine"} since we have a GET route registered at / path.

Note: The built-in .json method uses fast-safe-stringify which is faster than native JSON.stringify and also detects and prevents [Circular] Object within Response, So it is highly recommended to use this method for sending json payload to client.

Let's have a look at POST route

//Importing Routine class along with some types
import Routine, { Request, Response } from "@juniordev/routinejs";

//Creating a new instance of Routine
const app = new Routine();

//Registering a GET route on path '/'
app.get(`/`, (req: Request, res: Response) => {
  res.json({
    msg: "Hello Routine"
  });
});

//Registering a POST route also on path `/`
app.post(`/`, (req: Request, res: Response) => {
  //Automatic request body parsing without any plugin  
  //Sending the request body back to the client
  res.json(req.body);
});

//Starting the server on PORT 8080, default port is also 8080
app.listen(8080);

This time, we receive whatever we send as body to the server, if we send {"msg": "Hello Routine"} as JSON payload to the server, we receive {"msg": "Hello Routine"} back.

Routine has all major http verbs built-in, such as
GET, POST, PUT, PATCH and DELETE

Named & Query Params

//Importing Routine class along with some types
import Routine, { Request, Response } from "@juniordev/routinejs";

//Creating a new instance of Routine
const app = new Routine();

//Registering a GET route on path '/:name'
app.get(`/:name`, (req: Request, res: Response) => {
  res.json({
    name: req.params.name,
    query: req.query
  });
});

//Starting the server on PORT 8080, default port is also 8080
app.listen(8080);

Visiting http://localhost:8080/routine?hello=world would return us

{
  "name": "routine",
  "query": {
    "hello": "world"
  }
}

Note: You can put any valid regex as path and routine will try to match it

Using middlewares

//Registering a middleware
app.use((req: Request, res: Response, next: NextFunction) => {
  console.log("middleware called");
  next();
});

Middleware functionality is same as Express, that is why Routine is almost fully compatible with Express plugins, and is tested with popular ones such as Morgan, Express Validator etc

Lastly, let's see how nested routes are implemented

//src/router.ts
import { Router, Request, Response } from "@juniordev/routinejs";

const router = new Router();

router.get(`/router`, (req: Request, res: Response) => {
  res.json({
    msg: "from router"
  });
});

export default router;

//src/index.ts
import Routine, { NextFunction, Request, Response } from "@juniordev/routinejs";
import router from "./router";

//Creating a new instance of Routine
const app = new Routine();

//Using a nested router
app.use(`/nested`, router);

app.listen(8080)

When we visit http://localhost:8080/nested/router, server returns {"msg":"from router"}

All the above http methods, named or query params, middlewares can also be applied to nested routes as well, even so much so that nested routes can have more nested routes, and it does NOT degrade route matching performance due to startup/compile time route compilation

This was a basic introduction to Routine, many features such as cancel function, global async error handling etc is not yet talked about, but will be covered in future posts, you can read the docs for async error handling caveats for now though, its a good read i promise :)