6 ms·
Show HN: SQLite disk page explorer
- Retr0id 2y agoNeat! It's surprisingly easy to sit down with a copy of the sqlite file format docs and start writing code to parse sqlite files (like this project does), and I'd strongly recommend it for all the same reasons listed in the "Why?" section of the readme here. https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
- nayuki 2y agoIndeed, the file format is easy to read! It took me a few hours in one sitting to implement a reader. I have some visualization tools too, but with way less features than the OP's software: https://www.nayuki.io/page/sqlite-database-file-visualizations https://www.nayuki.io/page/sqlite-database-file-visualizatio...
- Retr0id 2y agoBy the way, I just used this today, it was useful.
- luizfelberti 2y agoUpvoted for using Redbean. I've been using it recently and it has been absolutely amazing, the built-in functionality that it has exposed through the Lua interface makes it an extensively programmable proxy that you can sandbox the crap out of if you're familiar with the unixy bits of it
- deleted 2y ago[deleted]
- hoc 2y agoDid that for a security tool about more than ten years ago. Makes you love that tightly designed engine a bit more. I always thought an explorer or just an base lib would be fun. Great to see yours, especially with a MIT license. Tbanks for sharing.
- electroly 2y agoIt was revelatory to write an SQLite virtual filesystem module for a storage layer with extremely high latency (S3). Every seek matters. This would have helped; I looked at the reads executed by SQLite and tried to intuit what was going on inside the file. Protip: Use WITHOUT ROWID with monotonic IDs if you don't want your rows sprayed randomly around the file! That one change was the difference between SQLite-on-S3 being unusably slow and being fast enough. WITHOUT ROWID tables let you manage the physical clustering.
- ustad 2y agoDo you have a url describing your protip?
- maples37 2y agohttps://news.ycombinator.com/item?id=42965714 https://news.ycombinator.com/item?id=42965714 will work if you're just bookmarking for the future
- electroly 2y agoThe only thing I really have is the SQLite docs, and some of the advice in here does not match my own testing and I would recommend the opposite. https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html In particular, they say: > WITHOUT ROWID tables will work correctly (that is to say, they provide the correct answer) for tables with a single INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in that case. Hence, it is good design to avoid creating WITHOUT ROWID tables with single-column PRIMARY KEYs of type INTEGER. This has not proven correct in my testing, but perhaps other applications are different. IMO, you _should_ use WITHOUT ROWID on your tables with single-column PRIMARY KEYs of type INTEGER. With a 100ms seek time on S3 requests it's obvious that the WITHOUT ROWID table with monotonic IDs is benefitting from spatial locality and rowid tables are not. I suspect when giving their advice, they are not considering queries of contiguous ranges of ids (which happens naturally more often than you'd think when JOINs are involved).
- 2y ago
- grimgrin 2y agoredbean!! redbean is so good https://redbean.dev/ https://redbean.dev/ likely a bit outdated but: https://github.com/shmup/awesome-cosmopolitan?tab=readme-ov-file#redbean https://github.com/shmup/awesome-cosmopolitan?tab=readme-ov-...
- xrd 2y agoRedbean is indeed so cool. I always wanted to do something using Janet in the same way with images and compile-time programming. Fun possibilities. https://janet.guide/compilation-and-imagination/ https://janet.guide/compilation-and-imagination/
- snarfy 2y agoI did not have a very good first experience with cosmopolitan. I followed the examples, made a very basic hello world type app, and it consistently crashed. I know it does work but my experience definitely turned me off to it. A polyglot binary seemed like a bad idea in retrospect. This was back when cosmopolitan was first announced so I assume it's better now.
- deleted 2y ago[deleted]
- aappleby 2y agoTool works well.
- shlomo_z 2y agoThank you! As someone who isn't disciplined enough to sit through a course or class, this was a really good way to visualize what's going on under the hood, and how to structure my data more efficiently.
- artps 2y agoThat's really impressive! As someone who's worked with SQLite internals in the past, I know this kind of work and tinkering is no small feat. On a related note, I recently came across sqlite-repr [1] (built with Dioxus), which offers an interesting take on SQLite visualizations as well. [1] https://torymur.github.io/sqlite-repr/ https://torymur.github.io/sqlite-repr/
- simonw 2y agoThis is really neat. I posted an animated GIF screenshot here https://simonwillison.net/2025/Feb/6/sqlite-page-explorer/ https://simonwillison.net/2025/Feb/6/sqlite-page-explorer/
- QuadrupleA 2y agoHa, honored :) When I was hacking this together the thought did occur, "I bet this is up SimonW's alley." Thanks for all the great LLM experiments & writeups.
- jaseemabid 2y agoI recently wrote an explainer for the sqlite file format with some helpful diagrams. This might help. https://blog.jabid.in/2024/11/24/sqlite.html https://blog.jabid.in/2024/11/24/sqlite.html
- deleted 2y ago[deleted]
- jas39 2y agoA true masterpiece:)
- ks2048 2y agoThis looks cool, nice job. Maybe add a color-legend to the front page? I didn't know what the colors represented at first. It's kind of choking on a larger db (3.6GB, 942719 pages) - maybe you can paginate the pages.
- QuadrupleA 2y agoYou can hover to get the page types (title attributes) and learn the color scheme that way, although it's not super clear. And yeah, large DBs get slow; pages are iterated "server side" which can take a while when there are hundreds of thousands of them. Ideal would probably be some live-scrolling page-range API call, although maybe a crude "show next 10K pages" or something would work too.
- ShaggyHotDog 2y agoNeat. Being learning about databases and this is very helpful!
- wwader 2y agoThe sqlite source includes a CLI tool called showdb that is quite nice for poking around in database files
- rousbound_ 2y agoNice project, unfortunately when I run the executable it throws the error: "failed to run lua code: /index.lua:3: module 'utils' not found:".