[HN Gopher] Show HN: ESQLate - Build minimum viable admin panels...
___________________________________________________________________
Show HN: ESQLate - Build minimum viable admin panels with just SQL
Author : mattatkeyboard
Score : 439 points
Date : 2020-01-17 08:56 UTC (14 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| jimmcslim wrote:
| There is also the idea of Naked Objects [1] based on a thesis
| from 2004 [2].
|
| [1] http://nakedobjects.org/
|
| [2]
| http://downloads.nakedobjects.net/resources/Pawson%20thesis....
| esamatti wrote:
| 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
| mattbee wrote:
| It looks excellent! But I know 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 wrote:
| A quick scan of the docs seems to indicate redash is read only.
| sandGorgon wrote:
| could you check the code ? i think esqlate-server is .gitignored
| mattatkeyboard wrote:
| esqlate-server lives at
| 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...
| ) 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 wrote:
| 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 wrote:
| 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
| emmelaich wrote:
| > ... 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
| mstade wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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
| 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 wrote:
| 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!
| yehia2amer wrote:
| This look similar to Oracle Apex: https://apex.oracle.com/en/
|
| from my experience very good for simple logic but once it's is
| complicated you hate yourself.
| darau1 wrote:
| 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
| bsaul wrote:
| 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.
| brainzap wrote:
| There are a lot of headless CMS which assists with the
| creation.
| mickael-kerjean wrote:
| > 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...
| It understands foreign keys and create relevant links to easily
| navigate through. The entire code is there:
| https://github.com/mickael-kerjean/filestash
| zozbot234 wrote:
| > 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 wrote:
| > 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). 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
| bsaul wrote:
| 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).
| cdrx wrote:
| I think forestadmin.com / jetadmin.io are the SQL introspection
| equivalent of the Django Admin. Both are commercial products
| though.
| vidarh wrote:
| For my current project, we're auto-generating a relatively
| full-featured CRUD interface with granular access controls and
| the works. [one detail to add there is that while it's
| primarily used as an admin interface now, it's not meant to be
| restricted to that - it has access control and workflow support
| built in that we're building product flows on, where the UI is
| still generated the same way]
|
| It's not _just_ from the SQL, but we use Sequel (Ruby ORM) to
| introspect the DB as a starting point, coupled with a plugin to
| Sequel that lets us annotate the models to provide more precise
| information that our Sinatra based API introspects to return
| JSON data to our React frontend that drives how the UI is
| presented. This extends to e.g. declaring which fields are most
| useful for users to search for relations that should be linked
| via foreign keys, for example (e.g. users are linked to by id,
| but when linking a user from another record, they are
| searchable by name and e-mail). As much as practical we auto-
| generation validations etc. from the database schema as well.
|
| I think doing this by SQL alone would be quite painful unless
| you sacrifice usability, because a typical database schema has
| too little information on how _users_ think about the data.
|
| E.g. we dialled back on using Postgres enum types because
| they're annoying to update and deal with if you frequently
| update the allowed values - but you need relatively little
| extra config to be able to generate very full-featured
| interfaces, and you can make most of that extra information
| entirely declarative. Foreign keys is another issue, as
| described above - a normal database schema just tells you how
| things are linked together, not how users would like to see it.
| E.g. knowing a comment was written by user 42 is much less
| useful than knowing it was written by bob@example.com; to allow
| user-friendly linking etc. you'll want more information. We
| refer to our additional layer of information about the
| structure of our data as our meta schema.
|
| You certainly _could_ put the parts that don 't fit perfectly
| in your database schema in your database anyway as regular
| data, and to some extent we do - e.g. the meta information that
| we augment our Sequel model classes with can be overridden by
| rows in a table.
|
| But the main reason we've not fully embraced that vs.
| annotating the model classes in code is that it makes version
| control painful, and if you update it with migrations it's
| really not saving that much vs. just putting it in the model
| classes (this might be different if you have other applications
| accessing the database and the database in effect needs to be a
| versioned API in itself accessible by multiple consumers, but
| in this case all the database access happens via the API
| exported from the model classes).
| Cthulhu_ wrote:
| 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.
| fulldecent2 wrote:
| Metabase?
| zubairq wrote:
| Yes yazz Pilot lets you build an admin panel by pointing at a
| dB and then it queries the database tables which you select
| visually
| barrystaes wrote:
| 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 wrote:
| 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.
| howmayiannoyyou wrote:
| 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.
| [deleted]
| tomerbd wrote:
| Brilliant. Interesting to see you used svelte! Anyone else has
| experience with it?
| nautilus12 wrote:
| 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 wrote:
| The amount of code in this that talks to the DB is really
| really tiny. It is certainly possible to support multiple
| databases.
| jimmar wrote:
| Interesting idea. Are you doing anything to protect against sql
| injection attacks?
| mattatkeyboard wrote:
| 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.
| xwowsersx wrote:
| Another worthy mention (not FE, but also exploiting power of
| sql): 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 wrote:
| 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 wrote:
| You might find Postgraphile interesting, if you like graphql:
| https://github.com/graphile/postgraphile
| hadem wrote:
| You might also find Hasura interesting for instant GraphQL
| with Postgres: https://hasura.io/
| marviel wrote:
| 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 wrote:
| 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.
| aidos wrote:
| 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 wrote:
| 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).
| [deleted]
| pagnol wrote:
| Mind explaining in more detail the difficulties you
| encountered?
| janci wrote:
| 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...
| saviorand wrote:
| 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
| xupybd wrote:
| Thank you so much I've wanted something like this for years but
| never invested the time into finding or building it.
| rygxqpbsngav wrote:
| Used to do something similar with Microsoft Light switch.
| Rowern wrote:
| 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/). Been using it for a
| year now and we went from 0 back office to a feature full one in
| no time!
| brentis wrote:
| Been researching space as well. This is more feature complete
| IMO.
|
| https://react-material-dashboard.devias.io/dashboard
| esistgut wrote:
| The is just a web "template". React-admin is a full featured
| framework with multiple data backends.
| airstrike wrote:
| Or, you know, just use Django ;-)
| ianmobbs wrote:
| This is a great option for stacks not built on Django!
| cpursley wrote:
| 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
| BjoernKW wrote:
| 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 wrote:
| Interesting. How would you make this to support one-to-many/many-
| to-many relationships?
| BjoernKW wrote:
| 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 wrote:
| 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.
| 0xff00ffee wrote:
| 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 wrote:
| 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 wrote:
| MS Access... / _shiver_ /
|
| Let's just laugh at each other and call it even. :)
| topspin wrote:
| > 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.
| tyingq wrote:
| There are decent top-to-bottom solutions like Quickbase and
| Google's Appmaker. I think "generic" is just hard to mix well
| into other code. Works better if you control the whole stack.
| gavinray wrote:
| 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 wrote:
| 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 wrote:
| The REST interface to this is pretty good. It's documented
| within esqlate-server
| (https://github.com/forbesmyester/esqlate-server)
| mattmanser wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| If you are looking for some simple SQL to table/report tools,
| give Poli a try: https://github.com/shzlw/poli
| milankragujevic wrote:
| I use adminer for this. https://www.adminer.org/ It's a single
| PHP file, and does everything I need.
| mantiniss wrote:
| Youp, Adminer works great for building a CRUD admin UI.
| Gys wrote:
| 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 wrote:
| 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.
___________________________________________________________________
(page generated 2020-01-17 23:00 UTC)