5 ms·

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

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.

djdeutschebahn 4y ago
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.
ocdtrekkie 4y ago
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.
- sally_glance 4y ago
  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?
  - ocdtrekkie 4y ago
    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 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-plants/global-power-plants https://global-power-plants.datasettes.com/global-power-plan... is pretty neat.
    - masklinn 4y ago
      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).
      - simonw 4y ago
        https://datasette.io/plugins/datasette-vega https://datasette.io/plugins/datasette-vega is the original visualization plugin, built on Vega. Example here: https://datasette.io/content/stats?_facet=package&package=datasette-vega&_size=max#g.mark=line&g.x_column=date&g.x_type=temporal&g.y_column=downloads&g.y_type=quantitative 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.
0cf8612b2e1e 4y ago
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.
- sally_glance 4y ago
  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 :-)
mritchie712 4y ago
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.
- sally_glance 4y ago
  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).
simonw 4y ago
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 https://til.simonwillison.net site (and many other sites I run) is deployed like that, using GitHub Actions: https://github.com/simonw/til/blob/81fdedb8e9b047cb059ef22c600b95f6f6ef9c73/.github/workflows/build.yml#L83 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/ 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.
- sally_glance 4y ago
  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 :-)
  - simonw 4y ago
    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.

djdeutschebahn 4y ago

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.

ocdtrekkie 4y ago

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.

sally_glance 4y ago
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?
- ocdtrekkie 4y ago
  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 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-plants/global-power-plants https://global-power-plants.datasettes.com/global-power-plan... is pretty neat.
  - masklinn 4y ago
    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).
    - simonw 4y ago
      https://datasette.io/plugins/datasette-vega https://datasette.io/plugins/datasette-vega is the original visualization plugin, built on Vega. Example here: https://datasette.io/content/stats?_facet=package&package=datasette-vega&_size=max#g.mark=line&g.x_column=date&g.x_type=temporal&g.y_column=downloads&g.y_type=quantitative 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.

0cf8612b2e1e 4y ago

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.

sally_glance 4y ago
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 :-)

mritchie712 4y ago

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.

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

simonw 4y ago

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 https://til.simonwillison.net site (and many other sites I run) is deployed like that, using GitHub Actions: https://github.com/simonw/til/blob/81fdedb8e9b047cb059ef22c600b95f6f6ef9c73/.github/workflows/build.yml#L83 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/ 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.

sally_glance 4y ago
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 :-)
- simonw 4y ago
  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.