
SQLer – Turn Your SQL queries into RESTful APIs without any programming language - alash3al
https://github.com/alash3al/sqler
======
striking
See also: [http://postgrest.org/en/v5.2/](http://postgrest.org/en/v5.2/),
which works with usually zero configuration against PostgreSQL databases

~~~
wppick
This is awesome. It seems like any API is really just abstracting the database
(or other data store) behind it. But, if only we could just expose the db
directly and let users write their own queries to get exactly what they want.
That way they could select exactly the fields they want like with Graphql, and
write joins to simplify multiple requests into one. I wonder what is still
preventing this from being reality? Not enough permission support on the db,
or fears of performance issues from badly written user queries?

~~~
laurent123456
Once you give users direct access to your db, you can no longer change it.
That's why there are abstractions which allow changing the underlying data
layer without changing the public API.

~~~
frou_dh
One of the suggested ways to use PostgREST is that rather than pointing it at
the Postgres schema called "public" (where created tables live by default),
you point it at a schema consisting of nothing but SQL Views which communicate
with those tables. That way, the underlying table structure can be altered
without affecting the clients that consume the auto-generated HTTP interface.

It's more effort to have to maintain some extra SQL code for those views, but
with all the labour-saving PostgREST delivers in the first place, you still
come out ahead.

~~~
duncan-donuts
But if you’re going to do all that why not just put an abstraction in code
(that could actually have some automated tests)?

~~~
michelpp
There are test frameworks for postgres.

[https://pgtap.org/](https://pgtap.org/)

------
skrebbel
I really like the idea behind this. It's a bit like PostgREST, except with
tht, if you want custom SQL, you need to make views or stored procedures. With
SQLer you write the scripts on your application server, which has all the
usual pros and cons of having code live on the appserver vs in the database. I
think most people prefer the code in the appserver, which theoretically would
make SQLer the better choice at least from that perspective.

I sorely miss a comment about security authentication, however. How are
sessions done? Cookies? Etc?

More fundamentally, I find myself really liking many things that are inspired
by CGI. I think that when we moved from CGI to monolithic application servers
two decades ago, despite all the good reasons to do so, we threw a _lot_ of
babies out with the bathwater. There's an elegance to the "http request? just
run some code" model that only very recently got popular again with AWS Lambda
and the likes.

~~~
ruslan_talpa
This is nothing like postgrest :) In postgrest the query is mostly determined
by the the user calling the API, in SQLer the query is hardcoded by the api
developer, the user just supplies the parameters to the query.

------
andrew_wc_brown
I do something similar with Rails and Postgres. I've never been good at
publicizing tools I've built.

I think the biggest problem with Rails is Arel and by making it easier to
write raw queries that serve json straight from the database you get great
performance, composability and its still all really simple.

I don't need GraphQL because its so easy to write in one one endpoint
everything I need to get back from my database and then write endpoints for
all the smaller endpoints.

But yeah my project is basically handlebars meets SQL. I'm currently working
on writing my own template language and hope to port it to other common
languages.

[https://github.com/monsterboxpro/monster-
queries](https://github.com/monsterboxpro/monster-queries)

I get really depressed working on projects with a million small endpoints or
they take the other extreme of using GraphQL.

I'm quite tired of Full-stack development since things are taking 100x more
effort and not because things are getting more complicated because they can't
see the simpler solution.

------
openbasic
I love the idea behind this and PostgREST, but one thing keeps me from using
on projects with a team: versioning. I've been using it for personal projects
and, while doing CREATE POLICY, CREATE ROLE here and there is easy when
working alone, I can only expect a nightmare when working with a medium-sized
team.

Migrations are, of course, a possible solutions, but I'm yet to see a decent
toolset for managing schema changes.

[https://martinfowler.com/articles/evodb.html](https://martinfowler.com/articles/evodb.html)

[http://www.jeremyjarrell.com/using-flyway-db-with-
distribute...](http://www.jeremyjarrell.com/using-flyway-db-with-distributed-
version-control/)

~~~
ruslan_talpa
[https://docs.subzero.cloud/managing-
migrations/](https://docs.subzero.cloud/managing-migrations/)

------
dejaime
Don't know why exactly, but I'm not sure if that headline is for a new piece
of tech or a new security flaw.

------
crb002
Also try jHipster. Bakes you a full stack application from just the data
model: [https://github.com/jhipster/generator-
jhipster](https://github.com/jhipster/generator-jhipster)

~~~
tshanmu
Can second this, have multiple times in the past to quickly spin up
prototypes/internal apps, worked nicely.

------
perfunctory
> {{ .BindVar "name" .Input.user_name }}

Now, this definitely looks like a programming language construct. How long
before this project gets loops and conditionals? See for example ansible.

~~~
akvadrako
It uses HCL and the new unreleased version does have loops and conditionals:

[https://www.hashicorp.com/blog/terraform-0-1-2-preview](https://www.hashicorp.com/blog/terraform-0-1-2-preview)

------
Dowwie
Interesting use of hashicorp's configuration language. How has it been working
with it? Why use it rather than json, toml, or yaml?

I arrived at something similar recently in Rust, using actix-web. However, as
an open source author of prior work (Yosai, in python), I realize that
releasing this work is only the beginning. There's a lot more effort required
to make an open source project viable. I'd gladly talk with folks about how
one might do it, though.

It would be helpful to understand the demand for a project like this (and
postgrest et al). What are the use cases where people finding value in
creating enterprise-quality auto-generated endpoints?

------
hendry
Doesn't GraphQL eclipse a RESTful interface when interfacing with a database?

e.g. [https://github.com/prisma/prisma](https://github.com/prisma/prisma)

------
wjgilmore
Also have a look at DreamFactory
([https://www.dreamfactory.com/](https://www.dreamfactory.com/)), an open
source API generation and management platform. DreamFactory includes
connectors for 18 databases, numerous file systems, e-mail delivery, mobile
notifications, and more. Also API key management, roles, limiting, and
logging. _shameless plug_

------
xaduha
XQuery was much more suited for this than SQL will ever be, so much so that
some naive people made a thing called XRX
[https://en.wikipedia.org/wiki/XRX_(web_application_architect...](https://en.wikipedia.org/wiki/XRX_\(web_application_architecture\))

Did it take off? Of course not.

------
mozey
Reminds me of this [https://open.blogs.nytimes.com/2007/07/25/introducing-
dbslay...](https://open.blogs.nytimes.com/2007/07/25/introducing-dbslayer).
The source can still be found on github. Except dbslayer is supposed to be
readonly.

------
khc
> sqler uses nginx style configuration language (HCL)

nginx configuration looks nothing like HCL (which is json compatible)

------
spacemanmatt
I didn't see it mentioned, but OpenRESTy has my business for declarative JSON
APIs backed by PostgreSQL. It's nginx so it's got a bit of momentum working
for it.

------
Sahhaese
It's not critical but having a bcrypt function which doesn't take in the cost
parameter feels like bad practice for the future.

------
collyw
Something like Flask doesn't take too much more effort to achieve something
similar, and will be a lot more flexible, no?

------
bni
Its great, it would be even better if it could be deployed to AWS Lambda.

------
matte_black
Not really limited to just RESTful APIs, you can write an RPC api as well and
call stored procedures to perform all sorts of complex actions on your data.

