A Poor Man's API

[55555]

Check out this simple, easy API replacement: just set up Docker, APISIX, Grafana, Prometheus, etc.

Does anyone else miss the old days of just editing your site's code and adding new columns to your MYSQL database while people were surfing your website? That was fun.

Tip: Another way to turn your DB into an API is to just connect to it and then write SQL queries.

[EdwardDiego]

I miss aspects of those days, because I made a good bit of money from people who used MyISAM because "it's more performant" (or just because it was the default, yes, I'm dating myself here), and then corrupted their data because MyISAM didn't enforce constraints.

Other than the easy money, nah, don't miss the old days that much.

Having to use Reflector to decompile a core .NET library to figure out wtf it was doing because MSDN was inadequate, and the source was very much not open.

PHP 4 code bases that heavily leaned on dangerous globals.

J2EE.

Ah, the "good" ol days.

[tptacek]

Grafana and Prometheus aren't necessary, and Docker is at this point pretty close to a Linux platform feature. APISIX is a reasonable complaint --- the "poor man's API" is "using a big open source project that provides generic APIs", sure, that's not all that clever.

[ilrwbwrkhv]

Absolutely. It is shameful how much stuff needs to be run before you can even get started.

[ls15]

Or use https://github.com/mevdschee/php-crud-api and skip writing queries (for basic CRUD).

> Howto: Upload "api.php" to your webserver, configure it to connect to your database, have an instant full-featured REST API.

[quickthrower2]

hostgator.com still exists. install some wordpress, edit the PHP files, add some tables in MySQL, have fun!

[CharlieDigital]

You might like Directus.

It is nominally a "headless CMS", but it's so close to the SQL that I think of it more like API in a box.

[tasuki]

> Creating a full-fledged API [...]. You need to think about [...] the REST principles

I can assure you you don't need to do that. And most people don't.

It's perfectly possible to create a useful API without adhering to the REST principles.

[Edit, having read the rest of the article]: Case in point, this very PostgREST tool promoted in the article appears very useful, while not thinking about the REST principles.

[beachy]

If you're talking about HATEOAS (which many zealots equate one to one with REST) you may be pleased to know there is no reference to it whatsoever in this article.

[quickthrower2]

What is the acronym for when something needs to be done, so you create an endpoint to do that thing, maybe return HTML if convenient (if it is a page request) or JSON if it is XHR, and then move on to the next user story?

[nattaylor]

Reminds me of datasette, which does similar things for SQLite (and much more, too). Docs at https://docs.datasette.io/en/stable/json_api.html

Example: Retrieve JSON for https://fivethirtyeight.datasettes.com/polls/president_prima... simply with https://covid-19.datasettes.com/covid/ny_times_us_counties.j... (I added a filter for Texas)

[bmn__]

Big problems with the design of the response here, too. Example: https://fivethirtyeight.datasettes.com/polls/president_prima...

The schema is ad-hoc and requires a human to interpret it. Link relations (at the bottom) are expressed the wrong way: a machine could understand the "next" or "license" relations, but not an arbitrary "next_url" or "license_url" JSON key.

Recommendation: adopt relevant IETF/IANA standards and reformulate the response to take advantage of them.

[colonwqbang]

> when you’ve validated the requirements, you can keep the existing façade and replace PostgREST with your custom-developed API.

Why? What is it about postgrest which makes it unsuitable for "real" usage? The author doesn't mention it.

[madsbuch]

Postgres is amazing, but there are some issues you quickly hit with systems like this:

1. Side effects: Want to send an email when something happens

2. conditional permissions: Need access to data under certain circumstances not modelled by the security language?

3. Subset access: Access to parts of the row / document.

4. Aggregate access: Access to eg. a count of rows you can not access.

These are usually solved using serverless functions – or an API written in code.

Personally I err to the side of just writing some code, maybe because I enjoy that part of the project. Then I might be more inclined to use no-code solutions for the frontend, where others want the freedom and flexibility.

[k__]

I would go the other way around.

Why start with an SQL database? When I start something new, I don't want to fiddle around with tables every time I change my model.

[quickthrower2]

Business logic in your preferred language paradigm, rather than sprocs/triggers

[jansommer]

Agree. I've used PostgREST for production and it works great. No need to rewrite the API.

[maartenh]

Hm, using Apache APISIX for DDoS protection on a single node? That won't really stop a real DDoS. Not much you can do on a single server, if a botnet is saturating the network links to your server(s), without help from your infra provider.

This setup can be used to prevent the backends from being overloaded, which one can probably already do from a single host, and depending on the speed/amount of work by the backends done, not a lot of bandwidth is required to overload most systems that have a limited amount of request processing capacity.

I would argue that this is load management/shedding though, and not DDoS protection.

[411111111111111]

Hasn't DDoS protection always been a small misnomer?

You can't really protect against it, you can only have enough bandwidth to handle everything.

[siscia]

Maybe I am biased, but at this point I would find simpler to just give access to the DB.

Let user write their own SQL queries and meter how much time they use for billing or abuse prevention.

[falcolas]

Did you know that databases can read from disk?

Databases are huge security vulnerabilities and should always have some kind of shim over them. Never expose your relational DB publicly if you want any control over it.

[andai]

At that point you might as well just let them download the .sqlite file ;)

Or (as is the fashion recently) compile sqlite to wasm and run it in browser.

[jlengrand]

That's what I actually admire about Supabase [0].

The folks created an almost full fledged Firebase replacement by clipping pieces together (similar to the article, replacing APISIX with Kong) and it allows them to iterate super fast.

Pragmatic, and nice if you're on the receiving end of it .

[0] https://github.com/supabase/supabase#how-it-works

[KronisLV]

Here's a page that talks a bit more about Apache APISIX: https://apisix.apache.org/docs/apisix/getting-started/

I find it a bit ironic that they don't support Apache2 as the web server, you know, it being an Apache project and all, instead going for Nginx or OpenResty (though admittedly they're great projects).

Even nowadays Apache2 is pretty okay: https://blog.kronis.dev/tutorials/how-and-why-to-use-apache-... (especially if you disable .htaccess for less disk I/O and use it with a single file based config)

[senko]

Or just use an API generator like https://apibakery.com , https://amplication.com or another with your favorite framework and call it a day.

[bmn__]

API Bakery requires a user to tediously and manually replicate the DB schema. In my eyes, that makes it worse than the solution under discussion.

[ushakov]

Do I have to generate my API from scratch if the schema changes? If so, this solution is a no-go

[tiborsaas]

This is far from what I'd call "poor man's" thing.

I'd want to host it for free, so Vercel and Netlify comes to my mind. But to go real poor man style, I'd use Google sheets as a backend and throw some caching layer on top of it. Firebase or one of its competitors is also a good idea to get some API up and running for free.

[cpursley]

Stick it on the Render free or cheap tier. Or Supabase for free in just a single click:

https://supabase.com/pricing

You're better off starting with Postgres than having to migrate from some kind of no-sql setup later.

[brianjacobs]

Not to be mean, but from my reading this needs Heroku free tier which no longer exists? I am very sorry if I got that wrong.

[edmundsauto]

Supabase has a free tier with all this included, plus more. It's the best install of postgres I've seen and I'm obsessed with how it makes triggers, auth, etc., all included.

[quickthrower2]

NKOTB: https://neon.tech/

Free Tier includes:

compute up to 1 vCPU / 256 MB

storage up to 10 GiB

3 projects per user

[chairmanwow1]

Render is a fantastic replacement

[tptacek]

Bunch of providers have free tiers.

[pram]

Ehh this seems more annoying than just writing an MVP in (your language of choice) given you already have the expertise.

[blowski]

Many projects grow to the point that the limitations of writing everything in views and stored procedures becomes more painful than writing the CRUD code in Python, Node, Ruby, PHP, or whatever.

Personally, I’d give a shout for https://api-platform.com/.

[hanzhangzeng]

This is informational and reminds me of that funny github repo porsager/postgres. Thanks for sharing this!

[bmn__]

> PostgREST is a fast way to construct a RESTful API.

That's false. The responses don't even have hyperlinks.

[GolDDranks]

Just to clear things up a bit (as I remember not knowing about this side of "RESTfulness" a while ago myself): an important part of REST is relying on hypertext, that is pointing to other resources by using URLs. Just returning an ID of a referenced object/resource as a plain number isn't very RESTful, but returning an URL of that object/resource is.

[Aeolun]

If you already have relationships between tables, then it shouldn't be that hard to add right?

[edgyquant]

Why not just use postgraphile?

[cpursley]

Haskell (performant functional language) > JavaScript (soggy wet noodles)

[12tvende]

Pretty cool postgress feature

[bmn__]

It's third-party software.

[sgt]

Is APISIX any good?

[jjzhiyuan]

Depends on your needs of API Gateway, Ingress Controller, and Service Mesh.

According to Apache APISIX Slack[1] channel discussions and GitHub Issues/Discussions, most people focus on Security, Feature Rich, and Performance.

1. Security: As an Apache project, there have a lot of users and maintainers who are watching project activities, and the Security Team follows up very quickly.

2. Feature Rich: Just join the Slack channel and you will find that lots of questions are related to "features".

3. Performance: https://api7.ai/blog/apisix-kong-3-0-performance-comparison

[1] https://apisix.apache.org/slack