I spoke at Swiss PgDay in Switzerland in late June. The talk was about how to create a no-code API with the famous PostgreSQL database, the related PostgREST, and Apache APISIX, of course. I already wrote about the idea in a previous post. However, I wanted to improve it, if only slightly. PostgREST offers a powerful SELECT mechanism. To list all entities with a column equal to a value, you need the following command: curl /products?id=eq.1 id is the column
eq.1 corresponds to the WHERE clause In this case, the generated query is SELECT * FROM products WHERE id=1. The query syntax is powerful and allows you to express complex queries. However, as an API designer, I want to avoid exposing users to this complexity. For example, a regular API can manage entities by their ID, e.g., /products/1. In turn, you'd expect PostgREST to be able to do the same with primary keys. Unfortunately, it doesn't treat primary keys any differently than regular columns. Apache APISIX to the rescue. One of APISIX's best features is to rewrite requests, i.e., exposing /products/1 and forwarding /products?id=eq.1 to PostgREST. Let's do it. First, we need to capture the ID of the path parameter. For this, we need to replace the regular radix tree router with the radix tree with a parameter router. apisix:
    router:
        http: radixtree_uri_with_parameter The next step is to rewrite the URL. We use the proxy-rewrite plugin for this on a /products/:id route. Unfortunately, using the :id parameter above in the regular expression is impossible. We need to copy it to a place that is accessible. To do that, before the rewriting, we can leverage the serverless-pre-function. With the plugin, you can write Lua code directly. It's an excellent alternative to a full-fledged plugin for short, straightforward snippets. Here's the configuration: curl -i http://localhost:9180/apisix/admin/plugin_configs/1 -X PUT -d '
{
  "plugins": {
    "serverless-pre-function": {
      "phase": "rewrite",
      "functions" : [
        "return function(_, ctx)
          ctx.var.product_id = ctx.curr_req_matched.id;         #1
        end"
      ]
    },
    "proxy-rewrite": {
      "uri": "/products?id=eq.$product_id"                      #2
    }
  }
}' Copy the captured id variable to a place accessible to other plugins later on
Use it! Thanks to my colleague Zeping for pointing out the solution to me! You can expose the /products/1 REST-friendly URL and let APISIX rewrite it for PostgREST. Conclusion In this post, I've described using the proxy-rewrite plugin with a path variable. You can reuse the same technique with multiple variables. Keep also in mind that the serverless plugin is a hidden jewel; it can help you with small Lua snippets before moving to a full-fledged plugin. To go further: PostgREST
PostgREST tables and views
APISIX serverless plugin I spoke at Swiss PgDay in Switzerland in late June. The talk was about how to create a no-code API with the famous PostgreSQL database, the related PostgREST , and Apache APISIX , of course. I already wrote about the idea in a previous post . However, I wanted to improve it, if only slightly. Swiss PgDay PostgreSQL PostgREST Apache APISIX previous post PostgREST offers a powerful SELECT mechanism. To list all entities with a column equal to a value, you need the following command: SELECT curl /products?id=eq.1 curl /products?id=eq.1 id is the column eq.1 corresponds to the WHERE clause id is the column id eq.1 corresponds to the WHERE clause eq.1 WHERE In this case, the generated query is SELECT * FROM products WHERE id=1 . SELECT * FROM products WHERE id=1 The query syntax is powerful and allows you to express complex queries. However, as an API designer, I want to avoid exposing users to this complexity. For example, a regular API can manage entities by their ID, e.g. , /products/1 . In turn, you'd expect PostgREST to be able to do the same with primary keys. Unfortunately, it doesn't treat primary keys any differently than regular columns. Apache APISIX to the rescue. query syntax e.g. /products/1 One of APISIX's best features is to rewrite requests, i.e. , exposing /products/1 and forwarding /products?id=eq.1 to PostgREST. Let's do it. i.e. /products/1 /products?id=eq.1 First, we need to capture the ID of the path parameter. For this, we need to replace the regular radix tree router with the radix tree with a parameter router. apisix:
    router:
        http: radixtree_uri_with_parameter apisix:
    router:
        http: radixtree_uri_with_parameter The next step is to rewrite the URL. We use the proxy-rewrite plugin for this on a /products/:id route. Unfortunately, using the :id parameter above in the regular expression is impossible. We need to copy it to a place that is accessible. To do that, before the rewriting, we can leverage the serverless-pre-function . With the plugin, you can write Lua code directly. It's an excellent alternative to a full-fledged plugin for short, straightforward snippets. proxy-rewrite /products/:id :id serverless-pre-function Here's the configuration: curl -i http://localhost:9180/apisix/admin/plugin_configs/1 -X PUT -d '
{
  "plugins": {
    "serverless-pre-function": {
      "phase": "rewrite",
      "functions" : [
        "return function(_, ctx)
          ctx.var.product_id = ctx.curr_req_matched.id;         #1
        end"
      ]
    },
    "proxy-rewrite": {
      "uri": "/products?id=eq.$product_id"                      #2
    }
  }
}' curl -i http://localhost:9180/apisix/admin/plugin_configs/1 -X PUT -d '
{
  "plugins": {
    "serverless-pre-function": {
      "phase": "rewrite",
      "functions" : [
        "return function(_, ctx)
          ctx.var.product_id = ctx.curr_req_matched.id;         #1
        end"
      ]
    },
    "proxy-rewrite": {
      "uri": "/products?id=eq.$product_id"                      #2
    }
  }
}' Copy the captured id variable to a place accessible to other plugins later on Use it! Copy the captured id variable to a place accessible to other plugins later on id Use it! Thanks to my colleague Zeping for pointing out the solution to me! Zeping You can expose the /products/1 REST-friendly URL and let APISIX rewrite it for PostgREST. /products/1 Conclusion In this post, I've described using the proxy-rewrite plugin with a path variable. You can reuse the same technique with multiple variables. Keep also in mind that the serverless plugin is a hidden jewel; it can help you with small Lua snippets before moving to a full-fledged plugin. serverless To go further: To go further: PostgREST PostgREST tables and views APISIX serverless plugin PostgREST PostgREST PostgREST tables and views PostgREST tables and views APISIX serverless plugin APISIX serverless plugin

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Advanced Rate Limiting with Apache APISIX: Optimizing API Gateway Performance

Dynamic Image Watermarking Made Simple with imgproxy and Apache APISIX

Read all my posts!

Read My Stories

Follow @nicolas_frankel on Twitter

Too Long; Didn't Read

Make resilience your competitive advantage

Improving No-Code APIs with PostgreSQL, PostgREST, and Apache APISIX

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

16 Best Practices For Securing Your APIs with Apache APISIX - Part 1

Apache APISIX vs. Spring Cloud Gateway

Apache APISIX Without etcd?

Creating a Poor Man's API Using PostgREST

Creating a Simple gRPC Service on the Client Side

16 Best Practices For Securing Your APIs with Apache APISIX - Part 1

Apache APISIX vs. Spring Cloud Gateway

Apache APISIX Without etcd?

Creating a Poor Man's API Using PostgREST

Creating a Simple gRPC Service on the Client Side

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps