6 ms·
Datasette is my data hammer
- darkteflon 4y agoInteresting project. I would've liked to have heard a brief comparison with Excel - what makes Datasette something that one should reach for over that? Is it just the ability to use SQL in-place?
- steve_adams_86 4y agoSome things which stand out to me at a glance: - it’s open source - it allows publishing to the web - it provides handy web-based tooling, like turning your data into a JSON api - it provides a user-friendly UI but also lets you dig deeper with plain old SQL (pro to some, con to others) - apparently has an extensible core with a rich plugin ecosystem which allows it to meet a wide range of needs I’m sure there’s more, but these are all really interesting to me.
- Lio 4y agoJust taking the first point, being open source means that I don't need a licence to use Datasette and that I can run it on all three OSs I have to work with (Linux, macOS and Windows).
- pletnes 4y agoIt’s got plugins for some stuff Excel don’t. I’ve used one for map/gis data to look at lat/long data on a map. Never seen that in Excel. The dataset was too big for Excel, too.
- simonw 4y agoWhen I've talked to startups in the past I've always warned them to be careful about accidentally building a product which competes with a potential customer's existing good Excel spreadsheet... so it's ironic that I've ended up myself focusing on something that falls into that bucket! Right now I'd say the big things are: 1. Size of data. Datasette is a great tool for when your data is too big for Excel but not so big that you need a data warehouse - think 100MB to 10GB range 2. Interoperability and automation. Building tools in other languages that interoperate with an Excel spreadsheet - a web crawler that updates it constantly over time for example - is possible but in my experience pretty hard. Writing to a SQLite file is usually much easier in comparison 3. Publishing data. This is a core concept in Datasette: the ability to publish your data online so other people can explore and interact with it. 4. Plugins. Writing plugins for Datasette is pretty easy, and there are over 100 now: https://datasette.io/plugins https://datasette.io/plugins - so if it doesn't do something you need, you can add that feature independently of the core project 5. SQLite generally. Some projects are a better fit for a database than a spreadsheet, and SQLite is a /really/ good (often underestimated) database.
- darkteflon 4y agoHi Simon, thanks so much for taking the time. Been following your excellent work with Django for many years. We took a look at DS several months ago (I believe it popped up here on HN) but I confess that until I read the linked article I hadn’t fully understood that it was a “hammer”, and a good one at that. At that time we set it aside in favour of AirTable which for our purposes - to relate this to one of your other comments here - is sort of Access with a web interface plus authentication and (frankly not very granular) authorisation. The main reason was because it seemed to us that Datasette didn’t have first class support for auth - that it was sort of positioned as a community or internal tool, rather than something we could build out a client interface on. I just had a look at the plugins and noted that there is something for auth, although it too looks rather to be for internal tooling. Is that a fair characterisation, or do we have it wrong? Because we love everything else about DS - open source, built on SQL and SQLite, etc. - and would vastly prefer to use it over AirTable.
- simonw 4y agoAirtable is a fantastic piece of software. I'd love Datasette to work as an alternative for it some day but there is a whole lot of stuff to build to get there. The most relevant plugin for what you're describing at the moment is probably the Auth0 one: https://datasette.io/plugins/datasette-auth0 https://datasette.io/plugins/datasette-auth0 Quite a lot of assembly is required at the moment if you want to do interesting custom things with authentication and permissions - the documentation here is the best starting point: https://docs.datasette.io/en/stable/authentication.html https://docs.datasette.io/en/stable/authentication.html
- darkteflon 4y agoFantastic - thanks Simon, much appreciated. Yes we generally like the AirTable UX, too, and it’s a good fit for client-facing stuff in addition to internal tooling. It is slow, though, and there are no native desktop or mobile clients. Mainly, though: building a business on it comes with all the usual pitfalls of VC-backed SaaS. It’s very helpful to hear your thoughts on the project’s trajectory. We’ll take another look at DS and the auth0 module, thanks. It looks very promising and we don’t mind a bit of assembly.
- morgango 4y agoI think the best comparison would be with Access, seems to be the all-in-one data tool that tried to be.
- simonw 4y agoYeah, I'm constantly surprised at Microsoft's lack of investment in Access. The world clearly needs a really great, easy to use personal database. Datasette isn't there yet but it's a useful ambition for the future.
- frogulis 4y agoOnly tangentially related: what is the best general-use single hand tool? My feeling is that it would be some kind of small sturdy knife. "When all you have is a knife, a lot of stuff looks passably doable"
- bradwood 4y agoContext is everything. Outdoors? Probably a very sharp machete. Working in a genius bar? Probably that weird-ass hextool screwdriver thing.
- klondike_klive 4y agoMaybe a billhook over a machete if you're going to be splitting logs for kindling. Bit more versatile.
- jeffreygoesto 4y agoI knew an outdoor person that did almost everything with an Opinel knife when being on tour.
- MonkeyClub 4y agoIndeed a blade is one of the most useful things that you can’t replicate with naturally occurring items (a log or a rock can be used to hammer things, for example, or vines can be twisted into rope). Opinel knives in particular are quite handy, thanks to the good quality of the blade. They can take a beating and keep their edge, plus they fold and fit in the pocket (up to No. 9 with ease), so they can fulfill the functions of fancier “survival knives”. Add a ferrocerium rod for easy fire starting, and a good book for downtime by the fire, and you’re golden.
- AlotOfReading 4y agoYou should definitely check out knapping if you're interested in how people used to produce blades solely from natural materials. Knapped stones can even be superior to modern knives in certain respects (sharpness, shape for purpose, weight, etc).
- brunooliv 4y agoCan Datasette be used with a Postgres RDS database with hundred GBs? Feels like it's mostly designed for smaller things? Also if it can, how would I go about it and if it can't, any similar tools I can use? This looks really cool!!
- jboynyc 4y agoIt's just for sqlite databases.
- lsb 4y agoYou can easily put a hundred GB or two into a SQLite database
- craftsquick 4y agoThis is a self-plug but shapedata.io does similar things on top of Postgres -- it is much younger than Datasette, so has far fewer features, but should give you a similar exploration experience!
- Fuzzwah 4y agoI'm keen to check this out, I tried to sign up but got a 403 during the google auth flow. Along with the message: The developer hasn’t given you access to this app. It’s currently being tested and it hasn’t been verified by Google. If you think you should have access, contact the developer
- craftsquick 4y agoReally sorry about that -- I should've turned off test mode now for Google OAuth. Login work ok now?
- Fuzzwah 4y agoYep, in now. Will play with it tomorrow. Thanks, looks like a great project.
- 4y ago
- sally_glance 4y agoI 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 agoDo 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 agoQuestion: 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 agoI 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 agoIn 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.
- romdev 4y agoClicked because I thought at first this was a retro computing post about a Commodore cassette tape drive. Thanks for the intro to this useful tool.
- jdblair 4y agoI'm showing my age, but the first thing I thought of when reading the headline was the Commodore Datasette. https://en.m.wikipedia.org/wiki/Commodore_Datasette https://en.m.wikipedia.org/wiki/Commodore_Datasette
- klondike_klive 4y agoI immediately thought of the musician Datassette - proponent of the micro-genre Business Funk. https://www.youtube.com/watch?v=zgWa7NoJGWc https://www.youtube.com/watch?v=zgWa7NoJGWc
- misterS 4y agoI did, too :) For those that don't know: Datasette is the author/curator of https://musicforprogramming.net https://musicforprogramming.net
- klondike_klive 4y ago...and he contributed music and SFX to Space Rubbish, an awesome bullet-hell Asteroids clone. https://vimeo.com/18472447 https://vimeo.com/18472447
- simonw 4y agoDatasette was named after that disk drive, because the first code I ever wrote was saved to a floppy disk using a C64. I had also assumed it would be a good unique name that I could subscribe to search results for... only to find that the Commodore 64 vintage computing community is still way more active than I had ever imagined!
- sgraaf 4y agoDatasette is such a neat project & tool. Simon, its developer, has built an entire ecosystem of tools around sqlite [1], Datasette [2], and automation through GH Actions, like git-scraping [3]. Also, he has been actively blogging for 20+ years [4]. I particularly enjoy his short TIL posts [5]. [1] https://github.com/simonw?tab=repositories&q=sqlite&sort=stargazers https://github.com/simonw?tab=repositories&q=sqlite&sort=sta... [2] https://github.com/simonw?tab=repositories&q=datasette&sort=stargazers https://github.com/simonw?tab=repositories&q=datasette&sort=... [3] https://simonwillison.net/2020/Oct/9/git-scraping/ https://simonwillison.net/2020/Oct/9/git-scraping/ [4] https://simonwillison.net/ https://simonwillison.net/ [5] https://til.simonwillison.net/ https://til.simonwillison.net/
- matteason 4y agoSimon also did some brilliant stuff with Datasette based on a 2-hour long mp3 of train announcements that ScotRail released as part of a Freedom of Information reponse. A journalist called Jon Brady found it initially on the ScotRail website and tweeted about it, I chopped it up into individual announcements and crowdsourced the transcription, and Simon built a dashboard to explore them all in Datasette. Simon's blog: https://simonwillison.net/2022/Aug/21/scotrail/ https://simonwillison.net/2022/Aug/21/scotrail/ The Datasette dashboard: https://scotrail.datasette.io/ https://scotrail.datasette.io/
- rudasn 4y agoHe's also the co-creator of django!
- adius 4y agoFYI: I’m currently working on a full-fledged SQLite hosting platform with similar features as Datasette. Check it out at https://airsequel.com https://airsequel.com. So far we provide an instant GraphQL API, an SQLite workbench, a spreadsheet UI, and dashboards.
- Roark66 4y agoI'm disappointed. I came here hoping for this: https://www.c64-wiki.com/wiki/Datassette https://www.c64-wiki.com/wiki/Datassette
- simonw 4y agoTwo things I love about this post: 1. It's by a data journalist. Datasette was originally designed for data journalism (though it's useful for all sorts of other things) so it's really great to see it being enthusiastically used in this way 2. Jeremia talks at length about Datastte's URLs! This is a key design principle of Datasette: anything you see (a filtered table, SQL query results, a configure chart visualization) should be reflected in the URL of the page, such that you can bookmark it and share it with others. And any time you can see data you should be able to add .json to that URL (or .csv or, via plugins, other extensions like .atom or .geojson) to get that data back in a useful format.
- rektide 4y agoOn 2., Tim Bray's write-up on uri/urls as the integration point powering AWS is a good write up on the virtues of urls at scale, as a universal integration point. https://www.tbray.org/ongoing/When/202x/2021/10/26/URI-based-Integration https://www.tbray.org/ongoing/When/202x/2021/10/26/URI-based...
- dgb23 4y agoAs for 2: I think this is quite generally a quality feature for web apps and dynamic web interfaces, that should always be considered. If they get too large/unwieldy there is always the option to use base64 encoding or other formats and tricks. Human readable URLs are always nice but I rather have something ugly than no URL state at all in many cases. If the output changes frequently over time, so it might get stale, then one can still add a timestamp and an additional warning in the UI with a link to recent data. What I'm saying is that there is rarely a good excuse to not do this. In fact I think when I haven't been following this principle it was typically due to self-inflicted, bad decisions paired with time constraints.
- deleted 4y ago[deleted]
- PurpleRamen 4y agoLooking through some of the examples[1], it seems the WebUI is pretty underwhelming and demands much optimization and modernization. Are there alternative UIs available? [1] https://datasette.io/examples https://datasette.io/examples
- everybodyknows 4y agoWhat specifically would you want to be different? It seems to me to serve the purpose well: https://global-power-plants.datasettes.com/global-power-plants/global-power-plants?country_long=United+States+of+America&_facet=geolocation_source#facet-geolocation_source https://global-power-plants.datasettes.com/global-power-plan...
- PurpleRamen 4y ago> What specifically would you want to be different? It's sold for data exploration, so i except some useful data-app with an up to date interface, dedicated for interactive navigation and presentation of data. > It seems to me to serve the purpose well: Not really. This looks like something that could even run on Internet Explorer 2. It has significant flaws in terms of UI and UX, and lacking nearly everything I except here. It's incredibly wasteful on space. I must scroll down two screens to even see some data. For some reason the map is high jacking the scroll down, and the map is so broken that I end with multiple earths displayed, with only one having actual data. Changes to the query are not in-place, the whole side is reloads every time. Adding a new filter only seems to work after a reload. And all information are really sparse and scattered randomly around. And finally, there seems to be no way to shape the actual output, which would be the main job of a data explorer.
- simonw 4y agoI'm definitely keen on suggestions for improvements I can make to the default UI. Datasette provides both a JSON API (easily enabled for CORS access) and supports custom templates, so it's possible to customize the UI any way you like. So far I've not seen many examples of extensive customization. I use the custom templates a lot myself - these four sites are all just regular Datasette with some custom templates: - https://datasette.io/ https://datasette.io/ - https://til.simonwillison.net/ https://til.simonwillison.net/ - https://www.niche-museums.com/ https://www.niche-museums.com/ - https://www.rockybeaches.com/us/pillar-point https://www.rockybeaches.com/us/pillar-point Source code is on GitHub for all four.
- account-5 4y agoI see there is a Mac desktop app, is there plans for similar for Linux or Windows? I'm assuming I can just install an run similar to jupyter notebooks in any case.
- simonw 4y agoYes, if you know how to use pip you can run "pip install datasette" and use it that way. I'd love to get the desktop app working on Linux and Windows. I did manage to get a prototype working on Windows, despite having VERY little experience working on that platform: https://github.com/simonw/datasette-app/issues/71 https://github.com/simonw/datasette-app/issues/71 The bit I'm stuck on is how to turn that prototype into an application with an installer that's signed so people can download and run it.
- mrbonner 4y agoIs there something equivalent to Datasette but with duckdb as the sql engine?
- simonw 4y agoI've been contemplating adding a plugin hook to Datasette so that it can work against engines other than SQLite, and my first two goals if I get that working are DuckDB and PostgreSQL. It's not on my immediate roadmap though.