8 ms·

Show HN: ESQLate – Build minimum viable admin panels with just SQL

505 points by mattatkeyboard 7y ago

jimmar 7y ago
Interesting idea. Are you doing anything to protect against sql injection attacks?
- mattatkeyboard 7y ago
  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.
BjoernKW 7y ago
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.
trungdq88 7y ago
Interesting. How would you make this to support one-to-many/many-to-many relationships?
- BjoernKW 7y ago
  This being a tabular visualisation of SQL result sets you'd either have duplication in some columns or aggregated results. That's simply how SQL deals with 1:n and n:m relations.
- mattatkeyboard 7y ago
  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.
Gys 7y ago
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 7y ago
  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.
sandGorgon 7y ago
could you check the code ? i think esqlate-server is .gitignored
- mattatkeyboard 7y ago
  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-method https://github.com/forbesmyester/esqlate#docker-compose-meth... ) 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 7y ago
    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.
cube2222 7y ago
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
- emmelaich 7y ago
  > ... I really like the current direction of providing a lot of tooling with only SQL necessary to use them, ... Everything old is new again! Database Backed Web Sites: The Thinking Person's Guide to Web Publishing Philip Greenspun - 1997 http://dannyreviews.com/h/Database_Web.html http://dannyreviews.com/h/Database_Web.html
- mstade 7y ago
  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 7y ago
    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!
bsaul 7y ago
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.
- Cthulhu_ 7y ago
  There's plenty of database administration tools that do just that. I guess they could use work in the UX department to make it more user friendly though. But beyond pure data access you'll want features like access rights (SQL servers will offer that to a point), validations, and data/context specific visualizations.
- mickael-kerjean 7y ago
  > 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_211234.png https://archive.kerjean.me/public/2020/screenshot_20200117_2... 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
  - bsaul 7y ago
    very nice ! As an advice from a marketing POV : i wouldn't start by explaining that it lets you navigate ftp servers using file abstraction. This is very confusing, because most people already access ftp servers using tools like filezilla clients that do exactly that. The db -> file browser is a very interesting idea that i think should stand on its own (even if the underlying tech is similar).
  - zozbot234 7y ago
barrystaes 7y ago
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 7y ago
  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.
deleted 7y ago
[deleted]
saviorand 7y ago
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
rygxqpbsngav 7y ago
Used to do something similar with Microsoft Light switch.
Rowern 7y ago
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!
- cpursley 7y ago
  React Admin is awesome. Combine it with Hasura (automatic GraphQL on top of PostgreSQL) and you can build an entire back office admin suite (API endpoints and admin front end) in a matter of hours. You end up writing more SQL than react as react-admin is basically a CRUD form generator. This adaptor connects react-admin with Hasura: https://github.com/Steams/ra-data-hasura-graphql https://github.com/Steams/ra-data-hasura-graphql
- brentis 7y ago
  Been researching space as well. This is more feature complete IMO. https://react-material-dashboard.devias.io/dashboard https://react-material-dashboard.devias.io/dashboard
  - esistgut 7y ago
    The is just a web "template". React-admin is a full featured framework with multiple data backends.
- airstrike 7y ago
  Or, you know, just use Django ;-)
  - ianmobbs 7y ago
    This is a great option for stacks not built on Django!
  - deleted 7y ago
    [deleted]
xupybd 7y ago
Thank you so much I've wanted something like this for years but never invested the time into finding or building it.
eb0la 7y ago
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 7y ago
  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)
mattmanser 7y ago
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 7y ago
  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).
richieartoul 7y ago
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 7y ago
  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
jimmcslim 7y ago
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.pdf http://downloads.nakedobjects.net/resources/Pawson%20thesis....
mattbee 7y ago
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 7y ago
  A quick scan of the docs seems to indicate redash is read only.
howmayiannoyyou 7y ago
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.
tomerbd 7y ago
Brilliant. Interesting to see you used svelte! Anyone else has experience with it?
yehia2amer 7y ago
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 7y ago
  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
xwowsersx 7y ago
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 7y ago
  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 7y ago
    You might find Postgraphile interesting, if you like graphql: https://github.com/graphile/postgraphile https://github.com/graphile/postgraphile
    - hadem 7y ago
      You might also find Hasura interesting for instant GraphQL with Postgres: https://hasura.io/ https://hasura.io/
      - marviel 7y ago
        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.
        vosper 7y ago
        I have tried both, but only a bit and for a personal project. In the end I went with Hasura because I like the admin interface, and I think the way it maps graphQL to SQL makes for easier-to-read (and write) graphQL queries. I actually tried PostgREST first, because I had never used GraphQL and was more familiar with RESTful APIs. But I would go straight to Hasura if I was starting today. When you combine graphql was graphql-codegen you can get typechecked queries, which is really nice.
esamatti 7y ago
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
nautilus12 7y ago
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 7y ago
  The amount of code in this that talks to the DB is really really tiny. It is certainly possible to support multiple databases.
0xff00ffee 7y ago
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 7y ago
  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!
  - 0xff00ffee 7y ago
    MS Access... /shiver/ Let's just laugh at each other and call it even. :)
  - topspin 7y ago
    > 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.
milankragujevic 7y ago
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 7y ago
  Youp, Adminer works great for building a CRUD admin UI.
gavinray 7y ago
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
janci 7y ago
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...
_frkl 7y ago
This looks really useful. How is authentication and authorization handled? Can I connect it to my Openid provider?
- mattatkeyboard 7y ago
  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
nojvek 7y ago
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

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

mattatkeyboard 7y ago
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.

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.

trungdq88 7y ago

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

BjoernKW 7y ago
This being a tabular visualisation of SQL result sets you'd either have duplication in some columns or aggregated results. That's simply how SQL deals with 1:n and n:m relations.
mattatkeyboard 7y ago
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.

Gys 7y ago

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 7y ago
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.

sandGorgon 7y ago

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

mattatkeyboard 7y ago
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-method https://github.com/forbesmyester/esqlate#docker-compose-meth... ) 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 7y ago
  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.

cube2222 7y ago

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

emmelaich 7y ago
> ... I really like the current direction of providing a lot of tooling with only SQL necessary to use them, ... Everything old is new again! Database Backed Web Sites: The Thinking Person's Guide to Web Publishing Philip Greenspun - 1997 http://dannyreviews.com/h/Database_Web.html http://dannyreviews.com/h/Database_Web.html
mstade 7y ago
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 7y ago
  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!

bsaul 7y ago

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.

Cthulhu_ 7y ago
There's plenty of database administration tools that do just that. I guess they could use work in the UX department to make it more user friendly though. But beyond pure data access you'll want features like access rights (SQL servers will offer that to a point), validations, and data/context specific visualizations.
mickael-kerjean 7y ago
> 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_211234.png https://archive.kerjean.me/public/2020/screenshot_20200117_2... 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
- bsaul 7y ago
  very nice ! As an advice from a marketing POV : i wouldn't start by explaining that it lets you navigate ftp servers using file abstraction. This is very confusing, because most people already access ftp servers using tools like filezilla clients that do exactly that. The db -> file browser is a very interesting idea that i think should stand on its own (even if the underlying tech is similar).
- zozbot234 7y ago

barrystaes 7y ago

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 7y ago
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.

deleted 7y ago

[deleted]

saviorand 7y ago

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

rygxqpbsngav 7y ago

Used to do something similar with Microsoft Light switch.

Rowern 7y ago

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!

cpursley 7y ago
React Admin is awesome. Combine it with Hasura (automatic GraphQL on top of PostgreSQL) and you can build an entire back office admin suite (API endpoints and admin front end) in a matter of hours. You end up writing more SQL than react as react-admin is basically a CRUD form generator. This adaptor connects react-admin with Hasura: https://github.com/Steams/ra-data-hasura-graphql https://github.com/Steams/ra-data-hasura-graphql
brentis 7y ago
Been researching space as well. This is more feature complete IMO. https://react-material-dashboard.devias.io/dashboard https://react-material-dashboard.devias.io/dashboard
- esistgut 7y ago
  The is just a web "template". React-admin is a full featured framework with multiple data backends.
airstrike 7y ago
Or, you know, just use Django ;-)
- ianmobbs 7y ago
  This is a great option for stacks not built on Django!
- deleted 7y ago
  [deleted]

xupybd 7y ago

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

eb0la 7y ago

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 7y ago
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)

mattmanser 7y ago

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 7y ago
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).

richieartoul 7y ago

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 7y ago
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

jimmcslim 7y ago

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.pdf http://downloads.nakedobjects.net/resources/Pawson%20thesis....

mattbee 7y ago

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 7y ago
A quick scan of the docs seems to indicate redash is read only.

howmayiannoyyou 7y ago

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.

tomerbd 7y ago

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

yehia2amer 7y ago

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 7y ago
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

xwowsersx 7y ago

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 7y ago
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 7y ago
  You might find Postgraphile interesting, if you like graphql: https://github.com/graphile/postgraphile https://github.com/graphile/postgraphile
  - hadem 7y ago
    You might also find Hasura interesting for instant GraphQL with Postgres: https://hasura.io/ https://hasura.io/
    - marviel 7y ago
      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.
      - vosper 7y ago
        I have tried both, but only a bit and for a personal project. In the end I went with Hasura because I like the admin interface, and I think the way it maps graphQL to SQL makes for easier-to-read (and write) graphQL queries. I actually tried PostgREST first, because I had never used GraphQL and was more familiar with RESTful APIs. But I would go straight to Hasura if I was starting today. When you combine graphql was graphql-codegen you can get typechecked queries, which is really nice.

esamatti 7y ago

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

nautilus12 7y ago

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 7y ago
The amount of code in this that talks to the DB is really really tiny. It is certainly possible to support multiple databases.

0xff00ffee 7y ago

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 7y ago
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!
- 0xff00ffee 7y ago
  MS Access... /shiver/ Let's just laugh at each other and call it even. :)
- topspin 7y ago
  > 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.

milankragujevic 7y ago

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 7y ago
Youp, Adminer works great for building a CRUD admin UI.

gavinray 7y ago

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

janci 7y ago

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...

_frkl 7y ago

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

mattatkeyboard 7y ago
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

nojvek 7y ago

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