In this post, I'm going to talk about an experimental MySQL component I developed using the MySQL 8 component subsystem.
I was recently inspired by this great article series from lefred, which explains how to extend MySQL using component infrastructure with a virus-scanning ability. After reading through all the steps, I discovered it is not so complicated to add custom functionalities to MySQL anymore. At least the basic ideas are straightforward to implement. If you like the idea at the end of this post, I recommend you to read those articles to see how another idea is implemented.
This new component infrastructure is a modern alternative to the older plugin ecosystem. For more information, you can check out how MySQL authors explained the difference here and here.
Component "httpclient"
The component is called "httpclient" and does only one job: adds the ability to send HTTP requests using an SQL function (UDF). It defines two functions http_request and http_request_nowait respectively.
Note that you can reproduce everything here by following the instructions on the GitHub repo. If you want to understand how it works, the code is well-documented and is only ~570 lines.
Let's see how it works.
Example #1 (simple GET request)
Send a basic GET request and show the raw response.
mysql> SELECT http_request('GET', 'https://dummyjson.com/products?limit=1') AS response\G
*************************** 1. row ***************************
response: {"products":[{"id":1,"title":"iPhone 9","description":"An apple mobile which is nothing like apple","price":549,"discountPercentage":12.96,"rating":4.69,"stock":94,"brand":"Apple","category":"smartphones","thumbnail":"https://i.dummyjson.com/data/products/1/thumbnail.jpg","images":["https://i.dummyjson.com/data/products/1/1.jpg","https://i.dummyjson.com/data/products/1/2.jpg","https://i.dummyjson.com/data/products/1/3.jpg","https://i.dummyjson.com/data/products/1/4.jpg","https://i.dummyjson.com/data/products/1/thumbnail.jpg"]}],"total":100,"skip":0,"limit":1}
Example #2 (simple POST request)
Send a basic POST request with form data and show the raw response.
mysql> SELECT http_request('POST', 'https://httpbin.org/anything', 'param1=value1¶m2=value2') AS response\G
*************************** 1. row ***************************
response: {
"args": {},
"data": "",
"files": {},
"form": {
"param1": "value1",
"param2": "value2"
},
"headers": {
"Accept": "*/*",
"Content-Length": "27",
"Content-Type": "application/x-www-form-urlencoded",
"Host": "httpbin.org",
"X-Amzn-Trace-Id": "Root=1-653c1b30-7b396ef834f927033316b2e7"
},
"json": null,
"method": "POST",
"origin": "87.249.139.162",
"url": "https://httpbin.org/anything"
}
Example #3 (JSON body)
Send a typical API request with JSON data and bearer token, and show the raw response. Note that you can set curl options using the last argument.
mysql> SELECT http_request('POST', 'https://httpbin.org/anything', '{"param1":"value1","param2":"value2"}', '{"Content-Type":"application/json"}', '{"CURLOPT_AUTHORIZATION":"Bearer XXX"}') AS response\G
*************************** 1. row ***************************
response: {
"args": {},
"data": "{\"param1\":\"value1\",\"param2\":\"value2\"}",
"files": {},
"form": {},
"headers": {
"Accept": "*/*",
"Authorization": "Bearer XXX",
"Content-Length": "37",
"Content-Type": "application/json",
"Host": "httpbin.org",
"X-Amzn-Trace-Id": "Root=1-653c1bbc-52e9f60c54b8b1251504519b"
},
"json": {
"param1": "value1",
"param2": "value2"
},
"method": "POST",
"origin": "87.249.139.162",
"url": "https://httpbin.org/anything"
}
Example #4 (JSON response parser)
Thanks to native JSON support in MySQL, you can parse the response using the json_value function.
mysql> SELECT JSON_VALUE(http_request('GET', 'https://dummyjson.com/products?limit=1'), '$.products[0].description') AS description;
+
| description |
+
| An apple mobile which is nothing like apple |
+
Example #5 (response as JSON table)
Even better, you can return the response as a table-like standard result set using the json_table function.
mysql> SELECT * FROM JSON_TABLE(http_request('GET', 'https://dummyjson.com/products?limit=3'), '$.products[*]' COLUMNS(r
owIndex FOR ORDINALITY, id INT PATH '$.id', title VARCHAR(100) PATH '$.title')) AS response;+
+
| rowIndex | id | title |
+
| 1 | 1 | iPhone 9 |
| 2 | 2 | iPhone X |
| 3 | 3 | Samsung Universe 9 |
+
Example #6 (don't wait for the response)
Fire and forget is also possible with http_request_nowait. It is basically the same function except that CURLOPT_TIMEOUT_MS is set to 1 and timeout errors are ignored.
mysql> SELECT http_request_nowait('POST', 'https://httpbin.org/anything', 'param1=value1¶m2=value2') AS response\G
Response: NULL
Example #7 (get global status for requests)
The component also keeps track of some simple statistics.
mysql> SHOW GLOBAL STATUS LIKE '%httpclient%';
+
| Variable_name | Value |
+
| httpclient.number_of_requests | 13 |
| httpclient.time_spent_ms | 12396 |
+
Okay, But Why?
I have been using MySQL for more than a decade and enjoy it. There is a great community around it, and an extensive feature set especially after version 8. However, it is still lagging behind Postgres in terms of community contributions. There are many amazing Postgres extensions, some of which are already industry standards. After getting around Postgres for some time I was fascinated by the plugin ecosystem, and I started to wonder why it was not the same for MySQL. Oracle's licensing could be one reason, but I still think the plugin architecture isn't intuitive for developers.
It seems Oracle developers have seen the problem and decided to create a more convenient way of writing extensions. It is possible to write safer and more advanced extensions with component services now. In short, they created many built-in services to help developers add new behaviors to the database without creating a mess. For example; logging, system variables, status variables, and privilege grants are all decoupled services.
Conclusion
You may think that sending HTTP requests to a database server is not a good idea. At least that's what I think. Supabase would disagree, as they created a neat plugin called pg_net for the same purpose. The reason I have chosen to add curl support is for nothing but fun and experiment. However, there might be still some legitimate use cases such as fetching some daily data from 3rd party services, fire-and-forget, some ad-hoc queries, testing and prototyping etc. As long as it is not abused and the requests are fast, it might be a handy tool.
Although MySQL source code is a different beast for a newcomer, I encourage everyone interested to go and try developing components. In the worst case, you would get a better feeling of mysql database internals.
