
Show HN: ESQLate – Build minimum viable admin panels with just SQL - mattatkeyboard
https://github.com/forbesmyester/esqlate
======
Rowern
This looks pretty interesting! If anyone is looking for a tool to build a full
fledge Back Office I warmly recommend react-admin
([https://github.com/marmelab/react-admin/](https://github.com/marmelab/react-
admin/)). Been using it for a year now and we went from 0 back office to a
feature full one in no time!

~~~
airstrike
Or, you know, just use Django ;-)

~~~
ianmobbs
This is a great option for stacks not built on Django!

------
xwowsersx
Another worthy mention (not FE, but also exploiting power of sql):
[https://github.com/PostgREST/postgrest](https://github.com/PostgREST/postgrest)

"PostgREST serves a fully RESTful API from any existing PostgreSQL database.
It provides a cleaner, more standards-compliant, faster API than you are
likely to write from scratch."

~~~
geewee
PostgREST is really hard to figure out how to use though. I tried it a year or
two ago because I was really excited about the posibillity, but the setup was
super unintuitive, and the docs were hard to follow. I got a simple case
working, but in the end I gave up on using it for anything serious - I simply
wouldn't trust myself to be able to debug it if something went wrong.

~~~
marviel
You might find Postgraphile interesting, if you like graphql:
[https://github.com/graphile/postgraphile](https://github.com/graphile/postgraphile)

~~~
hadem
You might also find Hasura interesting for instant GraphQL with Postgres:
[https://hasura.io/](https://hasura.io/)

~~~
marviel
Thanks! Have you tried both systems? I've never heard of hasura, so don't know
the extent to which they compare. At a glance it looks like they serve largely
the same problem.

~~~
aidos
We’re using Hasura. Not affiliated but we do pay for their support (great team
to work with).

Hasura have taken a few unusual steps that actually turn out to be good
choices. They have their own version of row level security that you configure
via the admin console. It plays very nicely with their subscription queries so
there’s very little load on the dB (they effectively poll the dB and build a
temp table of the config Params of each connected client so they can pull back
the data for every subscription in a single query). It works really well in
practice.

We kicked the tyres on most the products in the space (and even had our own
implementation using socket io). There were a couple of things about Hasura
that didn’t seem to make sense but after a quick call with them we decided
that between their answers, and the team themselves, it was the product we
liked and trusted the most.

Highly recommended.

~~~
gavinray
The Hasura team are incredible. Not only that they're great people and
responsive, but they have one of the most absurdly talented devteams I've ever
seen (especially in regards to their Haskell devs).

------
cube2222
Great work! I once was a fan of custom well tailored DSL's for each tool.
However, nowadays I really like the current direction of providing a lot of
tooling with only SQL necessary to use them, even with the dialects differing
a bit.

I've made my own contribution here too, with a tool to join and analyse data
in various databases and file formats (JSON, CSV, Excel) using plain SQL,
OctoSQL:
[https://github.com/cube2222/octosql](https://github.com/cube2222/octosql)

~~~
mstade
This is really cool stuff, thanks for sharing!

I'm working on a desktop application which has feature to let users query data
across a bunch of different data sources, like csv, excel, and some others.
The way I've implemented this is by first scanning all applicable files and
populating a SQLite database with all the data from these files, a table per
file essentially, and then allowing users to execute queries against the
database. The database is updated whenever any of these files are changed on
disk, but any writes to the database are not persisted back into the files on
disk, so it's really a one-way kind of flow. This was all implemented mostly
as a proof-of-concept, but it's been massively useful so we're probably going
to expand on it this year, perhaps by allowing bi-directional workflows.

Even if the use cases and approach is different, it's nice and validating to
see other projects with similar thoughts and ideas. Again, thanks for sharing!

~~~
cube2222
Glad you like it!

I think I've seen an open source project with the approach described by you
too.

However, I think philosophically we differ in that one of our goals is to push
down as much work as possible to the underlying databases and our next big
upcoming milestone is streaming (Kafka, possibly database change streams).

But yes, it's great that a kind of ecosystem is forming around those ideas!

~~~
mstade
Absolutely agree regarding difference in approach – and I'm by no means
suggesting that yours is in any way worse! In fact, I believe yours is much,
much better given the more general use cases it supports. Ours is inherently
stateful and requires careful coordination, which works for us because the
system is pretty much self contained and it makes things easier, but I don't
think it's a good generic solution to be honest.

My point was really that it's nice and validating to see the the whole idea of
a unified way to "query all the things!" as it were isn't all that novel. :o)

By the way, if you can think of the name (or a URL even!) of that project you
mention I'd be very interesting to take a look at that too. Much obliged!

~~~
cube2222
I didn't want to come of as suggesting you meant any of that, not at all! Just
comparing approaches.

I think it was this:
[https://github.com/simonw/datasette](https://github.com/simonw/datasette)
though supports only CSV files.

There's also Apache Drill which works with a lot of data sources, which is
philosophically closer to OctoSQL.

~~~
mstade
Haha, I'm sorry – I didn't mean to sound accusatory or anything like that.
Text based communication can be tricky! :o) I very much appreciate the
comparison!

Also, many thanks for the link and Apache Drill mention, will definitely look
into these as well. Much obliged!

------
jimmcslim
There is also the idea of Naked Objects [1] based on a thesis from 2004 [2].

[1] [http://nakedobjects.org/](http://nakedobjects.org/)

[2]
[http://downloads.nakedobjects.net/resources/Pawson%20thesis....](http://downloads.nakedobjects.net/resources/Pawson%20thesis.pdf)

------
BjoernKW
That looks quite useful. The name's pretty clever, too.

Tools like these are a viable, low-code (to use a hyped-up term) alternative
to creating full-blown back-ends with all the boilerplate code that comes with
them. Often these back-ends add little more than a REST API or a simple CRUD
interface on top of an SQL database.

RDBMS on the other hand are immensely powerful tools that unfortunately more
often than are used for little more than simple data storage.

------
bsaul
i was stunned the first time i saw web frameworks like django and symfony
automatically build a full featured admin panel based on model's definition.

I wonder if there aren't projects aimed a building just an administration GUI
based on SQL introspection mixed with simple configuration files. Just point
at a db, and there you go.

Side note : what is the current state of codegen based on api and model
specifications ? I feel like 90% of most projects could be automatically
generated just based on that.

~~~
mickael-kerjean
> building just an administration GUI based on SQL introspection

I built exactly this. The idea was to make databases look and feel like a file
manager where databases are shown as folder, tables as subfolders and rows are
shown as files that once open shows a fully editable form that look like this:
[https://archive.kerjean.me/public/2020/screenshot_20200117_2...](https://archive.kerjean.me/public/2020/screenshot_20200117_211234.png)
It understands foreign keys and create relevant links to easily navigate
through. The entire code is there: [https://github.com/mickael-
kerjean/filestash](https://github.com/mickael-kerjean/filestash)

~~~
zozbot234
> I built exactly this. The idea was to make databases look and feel like a
> file manager where databases are shown as folder, tables as subfolders and
> rows are shown as files that once open shows a fully editable form ... It
> understands foreign keys and create relevant links to easily navigate
> through.

I thought that was the whole point of e.g. LibreOffice Base and the like?
(including Paradox, Access, etc.) So you built a web-based clone?

~~~
mickael-kerjean
> So you built a web-based clone?

It's more of an elaborate answer to the infamous ftp comment from when Dropbox
did launch in 2007 here on HN
([https://news.ycombinator.com/item?id=8863](https://news.ycombinator.com/item?id=8863)).
At its root, the project tries to solve the Dropbox problem by abstracting the
storage aspect so that you can bring your own backend by implementing a simple
interface.

The mysql plugin is just an implementation of that model to enable non nerds
to crud a database without braking anything. I did a few one of those
implementations: FTP, SFTP, S3, ... Mysql is just one of those "for the sake
of science" one to be put in the same bag as the LDAP one:
[https://www.filestash.app/ldap-browser.html](https://www.filestash.app/ldap-
browser.html)

------
esamatti
This looks great! I've used sqlpad[1] for similar purposes which is bit more
low level as it allows to write the queries in the browser directly.

[1]:
[https://github.com/rickbergfalk/sqlpad](https://github.com/rickbergfalk/sqlpad)

------
barrystaes
I think this is the long way around to simply making a DB procedure? Those
have parameters, can easily be run by external tools like phpMyAdmin or an
IDE, and if the database constraints are sane will help avoid invalid input.

~~~
mattatkeyboard
That is true, and I considered this approach. But DB procedures cannot link to
each other and the target users are different - giving some people a DB
procedure is much harder than giving them a web interface.

------
tomerbd
Brilliant. Interesting to see you used svelte! Anyone else has experience with
it?

------
mattmanser
So I made something similar to this at a couple of my jobs. We could basically
add a SQL script as a file to a folder and it would automatically have a new
report, downloadable, formatted, run on-demand. Even a pretty basic one is
pretty handy, as they only take a day or two. I hadn't got round to adding
variables, I like the solution.

One of the things I added is that you could add column formatting in the field
names, so a $c would turn it into a currency field, there was one to turn it
into boolean checkbox, etc.:

    
    
        SELECT o.amount, p.price [price$c], p.price * o.amount [total$c]
        FROM orders o
        JOIN products p
    

It had other features too like you could set column widths by using @ like
[amount@75] or even column grouping by dot notation [online.sales],
[online.total_value$c], [instore.sales], [instore.total_value$c].

~~~
mattatkeyboard
It does basic left/right aligning for strings/numbers etc.

It's a nice feature/idea but I'm torn between implementing it and also
allowing you to do it in SQL...

I agree that concat('$', table.amount) as amount should be right aligned, but
mine would leave it left aligned (as it became a string).

------
_frkl
This looks really useful. How is authentication and authorization handled? Can
I connect it to my Openid provider?

~~~
mattatkeyboard
Authentication is not handled at all. Given it is a project with a front and
back end (using a REST interface between) I think the correct approach is to
do authentication on a load balancer. The approach is fully documented here:
[https://github.com/forbesmyester/esqlate/issues/5](https://github.com/forbesmyester/esqlate/issues/5)

------
mattbee
It looks excellent! But I know [https://redash.io](https://redash.io) already
and used it in my last business - does this do something different? I'm always
hungry for better SQL business admin tools though.

~~~
tyingq
A quick scan of the docs seems to indicate redash is read only.

------
jimmar
Interesting idea. Are you doing anything to protect against sql injection
attacks?

~~~
mattatkeyboard
Yeh. It send through the key/values in HTTP POST and then you have the SQL and
K/C's on the server, the SQL itself is not sent. Server passes them through as
parameters to the Node.JS `pg` module so that takes care of defeating SQL
injection.

------
richieartoul
This is cool. I built something similar awhile back for a side project. I had
a folder that I would dump files into and each file would have a title,
description, and SQL statement.

When I visited a specific page on my site (password protected) I had a bit of
Go code that would read all the the files, execute all the (read-only) SQL
statements and then dump them into an HTML template that created a table for
each one.

Made it dead simple to keep track of some simple metrics like daily sign ups,
number of users currently on the site, churn, etc.

Hopefully I can just reach for this next time and not write my own hacky tool
:)

~~~
yuegui
If you are looking for some simple SQL to table/report tools, give Poli a try:
[https://github.com/shzlw/poli](https://github.com/shzlw/poli)

------
Gys
Nice work. I have also build a (mysql) sql based dashboard for our internal
use. It mainly shows agregated lists of data and allows to click on many items
to see more details.

We use it to get more insight in our data. It does not allow a user to enter
anything.

Basically my app only uses a list of queries. To allow that any selected field
is clickable, the result field should be like 'report|name|field,field' so the
app knows this is a special link (report) refering to another defined query
('name') and should be passed some values (record specific, here
'field,field').

~~~
mattatkeyboard
Yeh sounds like you had a similar revelation to me :-) This can also do the
linking between "reports" or "forms". Happy you built one, makes me think this
has at least some chance of success.

------
milankragujevic
I use adminer for this. [https://www.adminer.org/](https://www.adminer.org/)
It's a single PHP file, and does everything I need.

~~~
mantiniss
Youp, Adminer works great for building a CRUD admin UI.

------
howmayiannoyyou
Good, but, I don't want to write UI configs in JSON. Making the UI should be
as minimally difficult as the rest of the app makes tasks.

------
gavinray
Super neat idea and tool!

I tried to clone it and run it locally, but it doesn't run. Tried with both
yarn and npm.

    
    
        > node-sass src/index.scss > public/index.css
    
        sh: 1: node-sass: not found
        npm ERR! file sh
        npm ERR! code ELIFECYCLE
        npm ERR! errno ENOENT

------
eb0la
I believe admin panels are the MVP implementation for this idea.

Almost every business software is a select/insert/update. If you can do it
over a view, or you have a trigger on insert/update/delete you are able to
have a customer facing product with minimal manual operation.

~~~
mattatkeyboard
The REST interface to this is pretty good. It's documented within esqlate-
server ([https://github.com/forbesmyester/esqlate-
server](https://github.com/forbesmyester/esqlate-server))

------
trungdq88
Interesting. How would you make this to support one-to-many/many-to-many
relationships?

~~~
mattatkeyboard
You write the SQL into a JSON file. Users then can then execute that SQL with
their own parameters. So the many-to-many is in your capability... A key
reason for this is that it itself should not be clever.

------
yehia2amer
This look similar to Oracle Apex:
[https://apex.oracle.com/en/](https://apex.oracle.com/en/)

from my experience very good for simple logic but once it's is complicated you
hate yourself.

~~~
darau1
Guys on my team struggle with Apex daily since we've started using it, which
has lead to me look for a better, FOSS alternative. This looks like it might
be the one

------
nojvek
Seeing a variety of tools to interact with databases makes me happy.

I quit my job recently to work on a similar idea.

[https://boomsql.com](https://boomsql.com). Still working on a landing page
and MVP

------
saviorand
This is awesome! Saves so much work on building simple admin panels. I have
experience in custom software development, and that's such a huge pile of work
for developers it's insane

------
nautilus12
I wonder if there is anything like this but for a larger swath of tools, like
say anything that is JDBC or ODBC compliant. More like data virtualization
tools like dremio, denodo, atscale.

~~~
mattatkeyboard
The amount of code in this that talks to the DB is really really tiny. It is
certainly possible to support multiple databases.

------
janci
Why not have the admin panels configuration stored in a database itself
instead of json? And use the admin panels to add more admin panels...

------
xupybd
Thank you so much I've wanted something like this for years but never invested
the time into finding or building it.

------
sandGorgon
could you check the code ? i think esqlate-server is .gitignored

~~~
mattatkeyboard
esqlate-server lives at [https://github.com/forbesmyester/esqlate-
server](https://github.com/forbesmyester/esqlate-server)

It might be the instructions for install a bit off but I've tested on MacOS
and Ubuntu (both BASH)... Which method is your install? If it's Docker Compose
( [https://github.com/forbesmyester/esqlate#docker-compose-
meth...](https://github.com/forbesmyester/esqlate#docker-compose-method) ) it
has a BASH line there in the README that does the checkout for you.

[ ! -d "esqlate-server" ] && git clone git@github.com:forbesmyester/esqlate-
server.git esqlate-server

~~~
matthewaveryusa
You may want to revisit how you do this. At the very least use git submodules
so a particular version of your code pulls the correct version of a
dependency.

------
rygxqpbsngav
Used to do something similar with Microsoft Light switch.

------
0xff00ffee
Does anyone else find it humorous that the majority of top-level comments in
this thread start with, "Cool! I built something just like this <insert link
to example> because <commercial solution> sucks!"

This tells me that (a) it is a common problem, and (b) it is not solved well.
I'm guilty too: I manage a large MySQL DB and the admin panels are essentially
CRUD UI rendered from the schema. The alt solutions I've clicked on here try
to add more functionality, but it seems that's where things break, because
every solution is different beyond the 1st-order CRUD UI. This seems to
indicate there is no generic solution beyond that?

~~~
Twisell
Feel free to laugh at me but I often miss MS Access layout tools.

MS Access is totally unscalable and due to it's proprietary licence I can't
recommend it to anyone... but it was super freaking easy to stich together a
graphical UI usable by non expert users.

I'm a SQL/GIS/Dev based on PostgreSQL/PostGis and I would totally harass my
boss to buy such a solution if it were to exist. I have no time to learn
latest front-end flavor so being able to quickly deploy any customizable AND
simple GUI between my SQL illiterate colleagues an our database would by
priceless!

If such solution does exist please have a laugh at me for being ignorant and I
would really appreciate if you could send some links to that product!

~~~
topspin
> Feel free to laugh at me but I often miss MS Access layout tools

Hell, I miss Paradox. In DOS. There was a direct relationship between the
tables and the UI so you got CRUD as a side effect.

I built several real application using it, but the best was a true
"enterprise" application used across three facilities. The "backend" was a
Netware file system and the key to making it scale was to cache necessary data
locally and batch writes to shared tables. Contention had to be avoided, not
because Paradox couldn't multiplex readers and writers, but because the
performance was so poor.

It was still spinning when I moved on. Have no idea how long they maintained
it, but I'll bet whatever they replaced it with cost an order of magnitude
more. It was a part of every dollar of revenue that outfit earned employing
~1000 people.

