Hacker News Reader

Comment by sally_glance | Hacker News Reader

I tried datasette for the first time not long ago. To me it wasn't obvious what it gave me compared to my local Postgres setup.

I have a single docker-compose file with Postgres + pgAdmin and a host mount which I use for data exploration and ad-hoc analysis. I get a nice Web UI and can ingest quickly using the awesome Postgres FDWs.

Compared to datasette, it allows me to also transform the data by creating derived views and tables. The UI of pgAdmin also is much more fully-featured, but depending on the user that could well be a disadvantage. I guess the other thing datasette might be better at is the sharing use case by somehow exposing your instance and sharing links, I can do that with pgAdmin as well though.

Overall I'd probably need to try it out some more but currently lack the motivation because I can't think of any big advantage over my old setup.

View on HN

One big advantage is in publishing data online: that as one of Datasette's key original features, and it's something I use constantly:

    datasette publish vercel til.db --app simon-tils

It can deploy databases directly to Cloud Run, Heroku, Fly and Vercel. It can cost pennies per month depending on database size and traffic.

My https://til.simonwillison.net site (and many other sites I run) is deployed like that, using GitHub Actions: https://github.com/simonw/til/blob/81fdedb8e9b047cb059ef22c6...

See also the baked data pattern where I talk about this kind of deployment in a lot of detail: https://simonwillison.net/2021/Jul/28/baked-data/

For ad-hoc data analysis - assuming you already know your way around PostgreSQL - your pgAdmin in Docker solution is hard to beat.

How often do you create a new database?

My favourite thing about working with SQLite is that the overhead of new database creation is equivalent to the overhead of creating a file. In a given day I might casually create dozens of new databases, then throw away most of them. I never found myself doing that with MySQL or PostgreSQL.

That publish command looks sweet! For confidential data I'd have to setup some integration with our infra to get the required access control though. Maybe rather go with the package option using docker and a pipeline that deploys to our k8s where I could handle auth using an existing proxy...

My current workflow doesn't really involve creating databases, I mostly just create foreign tables, normal tables and views in a default DB. From time to time I then just dump-backup everything away and start fresh.

Thanks for the response and your tools in general though, I liked how everything just worked when I tried it :-)

For confidential data you could use one of the plugins that add authentication - datasette-auth0 or datasette-auth-github or datasette-auth-passwords are options there - but yeah, you're probably better off deploying to internet hosting if you already have an auth proxy setup somewhere.

Oh wow thx for the pointer, I overlooked those. For business I would need a more generic oauth- or saml-based one to work with our Keycloak, but for quick private/hobby sharing the password one looks perfect.

Meant to say internal hosting, not internet hosting.

Your preaching to the choir Simon.

...But the tenor ...whose pitch is a little off... up the back, Is listening even though he's heard it before :-)

I think the ability to so quickly create and consume an SQLite DB is one of the big advantages here. I need to use bigger DBs like PostGres and MSSQL (in previous roles) for bigger tasks, but SQLite is down there with fopen in terms of time to create a DB.

I find myself imagining schemas for problems that I'd like to put into SQLite and possibly DataSette.

Thanks for making DataSette Simon.

I do not think Datasette has much to offer the savvy analyst. The base experience is more-or-less what you can get from DBeaver. However, the plug-in system does dramatically change this: check out Simon’s example with the train announcement audio clips or any of the mapping examples.

Instead, I have used Datasette as a way to distribute data to less technical groups with a minimum of fuss. Without dedicated infrastructure, sharing data bigger than an Excel file can require quite a bit of work, requiring building some kind of custom query/search/visualization mechanism. With Datasette, after I have completed an analysis, I can dump as much data as I want into some (fairly wide) tables, give a three minute tutorial (This is like a web version of Excel, and this is how you filter rows), and leave them to explore. This has allowed me to offer alternatives reports by just creating a new view into the data. As the underlying technology is SQLite, it can handle large datasets without problem.

Thanks, I read this as a vote for the sharing use case. Actually the UI immediately reminded me of phpmyadmin, but trimmed down to the bare necessities. I'll be watching out for a chance to try it again when I have a share-with-less-technical-people use case :-)

Question: Did you add plugins?

Datasette is basically entirely plugins, such that you can create radically different "sites" and tools out of different combinations of plugins. Datasette without any plugins is useful, but where it's functionality shines is entirely in plugins.

I did not, just had a quick glance at what was available.

What is your use case and which plugins would you recommend? Providing some kind of bare bones report/dashboard with the visualization plugin?

In my case, I log the GPS data from my car, but wanted a private way to explore that (obviously sensitive) data.

https://datasette.io/plugins/datasette-cluster-map is a really neat example of a "just works" plugin: If you have latitude and longitude columns, it will plot them!

But there are numerous plugins for certain types of visualizations, there's a bunch for handling editing, inserts, uploads, etc.

Some of the examples are pretty neat examples of what you can do with a bit of tuning: https://global-power-plants.datasettes.com/global-power-plan... is pretty neat.

Will datasette automatically provide useful graph views of time series? Or is there a good plug-in of that?

One thing I’m trying to get better at is resource tracking (mostly energy) but it’s pretty clunky using a spreadsheet (likely because I’m not super knowledgeable and really don’t care).

https://datasette.io/plugins/datasette-vega is the original visualization plugin, built on Vega. Example here: https://datasette.io/content/stats?_facet=package&package=da...

I've been hoping to build a new one around Observable Plot for a while now.

This plugin still requires you to pick what columns you would like to see plotted. Ideally I'd like it to take an informed guess based on the data and maybe offer you some thumbnails of suggested charts which you could then click to view larger.

Does pgadmin have drag and drop to import files? So often I have a few parquet or CSVs from various sources (e.g. S3) and need to quickly summarize or join them. Quick imports and summarization is one thing I've always found painful in most tools.

If it has I haven't used it yet - I just have a collection of snippets to create foreign tables (e.g. one based on a CSV I put into the host mount before).

Do you have a solution (with your go-to-tools) to put data with coordinates directly into an interactive map, for example leaflet? That is something I really like (and use extensivly) about datasette respectivly the plugins.

Reply on news.ycombinator.com