7 ms·

SQLite File Format Viewer

273 points by ilumanty 1y ago

mubou 1y ago
This is really cool.
NathanFlurry 1y ago
I've been enjoying watching you build this on X – nice work.
- invisal 1y ago
  Thanks so much :)
throw0101d 1y ago
Meta: always found it interesting that .dev was allowed to be a TLD: * https://en.wikipedia.org/wiki/.dev https://en.wikipedia.org/wiki/.dev More on-topic: another online option: * https://sqliteviewer.app https://sqliteviewer.app * https://inloop.github.io/sqlite-viewer/ https://inloop.github.io/sqlite-viewer/ Local app: * https://sqlitebrowser.org https://sqlitebrowser.org
- invisal 1y ago
  This is a different tool—SQLite File Format Viewer is made for anyone interested in exploring the internal structure of SQLite.
- pppone 1y ago
  I really like the VS Code extension for sqliteview.app - [0]. It also offers an edit feature for a reasonable price, in my opinion. I'm curious if there are any competing editor alternatives. [0] - https://vscode.sqliteviewer.app/ https://vscode.sqliteviewer.app/
  - Kwpolska 1y ago
    The only reasonable price for editing a SQLite database is $0.
    - lupusreal 1y ago
      Or $2000 for a perpetual license to the Sqlite Encryption Extension, if the database is encrypted ;) Honestly not a bad price if you're building it into a real product. Steep for hobbyists though.
latexr 1y ago
I especially like that it provides a sample database. Would be nice it were downloadable as a file, so we could explore it with our own tools as well and get a better feeling for how the tool works.
- TheRealPomax 1y ago
  https://github.com/invisal/sqlite-internal/tree/main/public https://github.com/invisal/sqlite-internal/tree/main/public, or just download it by copying the URL from your network tab when you load it.
graemep 1y ago
Very cool. It would be nice if it tied the format in a bit more to the schema and data being shown, but that is a very minor gripe given how nice a tool this is.
NoSalt 1y ago
Really nice, looks great! However, I am not a fan of uploading databases to "strange" sites on the internet, so I will probably never use this.
- olejorgenb 1y ago
  You can probably run it yourself: https://github.com/invisal/sqlite-internal https://github.com/invisal/sqlite-internal
- brulard 1y ago
  There is a github link so you can run it yourself
- jchw 1y ago
  AFAICT it's purely frontend, there is no uploading. Aside from running it yourself, another thing you can do is go into devtools and set the network throttling to "offline". (Note that this is not fool-proof, but it does prevent new connections from being established.)
- chungy 1y ago
  It's hard to know whether anything is actually uploaded, but thankfully SQLite databases are plentiful that don't contain sensitive data (and this, also, is why there is a sample database included). I loaded up my Star Trek database into the program to toy with: https://chiselapp.com/user/chungy/repository/startrek-db/uvlist https://chiselapp.com/user/chungy/repository/startrek-db/uvl...
- senkora 1y ago
  I wish that we had a browser version of “pledge” that would: 1. Permanently restrict the browser tab from accessing the network. 2. Show an indication of this in the browser UI outside of the content area. It would be perfect for this kind of app.
  - runjake 1y ago
    In this case, itt's easier to just run it locally: https://github.com/invisal/sqlite-internal https://github.com/invisal/sqlite-internal But you can get sort of what you want by: 1. Loading a site. 2. Go into the Web Dev Tools. 3. Select the Network tab. 4. Click on the "No Throttling" dropdown and check "Offline". 5. You should now see a yellow warning icon in your Network tab and it should prevent all network access. If you reload the tab you should get the "No Internet Connection" error page.
jchw 1y ago
This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page shorter than the other pages. You can see that illustrated clearly if you click into the first page of a database using this tool: the database header comes first, then the page header. This tool will undoubtedly come in handy for anyone who has a reason to be dealing with SQLite data structures directly for whatever reason, especially since the SQLite documentation is a bit terse at times.
- hinkley 1y ago
  I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.
  - jchw 1y ago
    If you want to maintain the properties that SQLite has for read use cases, you'll need to replicate a couple of features. At the very least, you'll probably want the format to still be page-based with a BTree structure. You really could get away with just using the SQLite format if you didn't mind the weirdness; a functional SQLite parser that can read tables would not be a significant amount of code. I think, though, that if you want to read the schema as SQLite understands it, you'd need to interpret the CREATE TABLE syntax, which would make it a bit more complex for sure. Otherwise, you can read tables and columns themselves relatively easily, and the values are all stringified.
    - hinkley 1y ago
      Yeah if I wasn’t clear I’m talking about a minimal file that SQLite can still open read only without errors, not a third party implementation. Though there might be a few tweaks that would allow SQLite to be a bit more lenient. For instance missing metadata that can be assumed. Maybe b tree nodes exceeding the usual load factor.
hamstergene 1y ago
This is super cool and will really help anyone studying how to build their own storage engines. A note: the table that describes the data structures (Field # | Description | Value) should also list the data type (uint8 uint16 int32 text etc.). If it's an enum ("Table Leaf"), reference other possible enum values. If its length depends on another field, reference that field No.
nraynaud 1y ago
this is a wonderful learning tool, thank you.
roelschroeven 1y ago
I tried with a simple database, and it just shows me a blank screen. The console in developer tools shows an exception (both in Firefox and Chromium): index-CGfYebmt.js:85 Uncaught RangeError: Offset is outside the bounds of the DataView at DataView.prototype.getUint32 (<anonymous>) at R0 (index-CGfYebmt.js:85:4683) at X0 (index-CGfYebmt.js:85:7966) at index-CGfYebmt.js:85:8157 at Array.map (<anonymous>) at Q0 (index-CGfYebmt.js:85:8150) at index-CGfYebmt.js:85:8240 at Object.useMemo (index-CGfYebmt.js:48:46087) at My.$.useMemo (index-CGfYebmt.js:17:7276) at V0 (index-CGfYebmt.js:85:8228) R0 @ index-CGfYebmt.js:85 X0 @ index-CGfYebmt.js:85 (anonymous) @ index-CGfYebmt.js:85 Q0 @ index-CGfYebmt.js:85 (anonymous) @ index-CGfYebmt.js:85 useMemo @ index-CGfYebmt.js:48 My.$.useMemo @ index-CGfYebmt.js:17 V0 @ index-CGfYebmt.js:85 tc @ index-CGfYebmt.js:48 bc @ index-CGfYebmt.js:48 lo @ index-CGfYebmt.js:48 No @ index-CGfYebmt.js:48 wm @ index-CGfYebmt.js:48 Xc @ index-CGfYebmt.js:48 Ao @ index-CGfYebmt.js:48 Qo @ index-CGfYebmt.js:48 me @ index-CGfYebmt.js:25
- invisal 1y ago
  Probably I might made mistake somewhere in my parser. Do you have small reproducible database with no sensitive database and add to the https://github.com/invisal/sqlite-internal/issues https://github.com/invisal/sqlite-internal/issues I will try to fix it
  - roelschroeven 1y ago
    Issue created: https://github.com/invisal/sqlite-internal/issues/3 https://github.com/invisal/sqlite-internal/issues/3
  - roelschroeven 1y ago
    Fixed now!
T3RMINATED 1y ago
[dead]

273 points by ilumanty 1y ago

mubou 1y ago

This is really cool.

NathanFlurry 1y ago

I've been enjoying watching you build this on X – nice work.

invisal 1y ago
Thanks so much :)

throw0101d 1y ago

Meta: always found it interesting that .dev was allowed to be a TLD: * https://en.wikipedia.org/wiki/.dev https://en.wikipedia.org/wiki/.dev More on-topic: another online option: * https://sqliteviewer.app https://sqliteviewer.app * https://inloop.github.io/sqlite-viewer/ https://inloop.github.io/sqlite-viewer/ Local app: * https://sqlitebrowser.org https://sqlitebrowser.org

invisal 1y ago
This is a different tool—SQLite File Format Viewer is made for anyone interested in exploring the internal structure of SQLite.
pppone 1y ago
I really like the VS Code extension for sqliteview.app - [0]. It also offers an edit feature for a reasonable price, in my opinion. I'm curious if there are any competing editor alternatives. [0] - https://vscode.sqliteviewer.app/ https://vscode.sqliteviewer.app/
- Kwpolska 1y ago
  The only reasonable price for editing a SQLite database is $0.
  - lupusreal 1y ago
    Or $2000 for a perpetual license to the Sqlite Encryption Extension, if the database is encrypted ;) Honestly not a bad price if you're building it into a real product. Steep for hobbyists though.

latexr 1y ago

I especially like that it provides a sample database. Would be nice it were downloadable as a file, so we could explore it with our own tools as well and get a better feeling for how the tool works.

TheRealPomax 1y ago
https://github.com/invisal/sqlite-internal/tree/main/public https://github.com/invisal/sqlite-internal/tree/main/public, or just download it by copying the URL from your network tab when you load it.

graemep 1y ago

Very cool. It would be nice if it tied the format in a bit more to the schema and data being shown, but that is a very minor gripe given how nice a tool this is.

NoSalt 1y ago

Really nice, looks great! However, I am not a fan of uploading databases to "strange" sites on the internet, so I will probably never use this.

olejorgenb 1y ago
You can probably run it yourself: https://github.com/invisal/sqlite-internal https://github.com/invisal/sqlite-internal
brulard 1y ago
There is a github link so you can run it yourself
jchw 1y ago
AFAICT it's purely frontend, there is no uploading. Aside from running it yourself, another thing you can do is go into devtools and set the network throttling to "offline". (Note that this is not fool-proof, but it does prevent new connections from being established.)
chungy 1y ago
It's hard to know whether anything is actually uploaded, but thankfully SQLite databases are plentiful that don't contain sensitive data (and this, also, is why there is a sample database included). I loaded up my Star Trek database into the program to toy with: https://chiselapp.com/user/chungy/repository/startrek-db/uvlist https://chiselapp.com/user/chungy/repository/startrek-db/uvl...
senkora 1y ago
I wish that we had a browser version of “pledge” that would: 1. Permanently restrict the browser tab from accessing the network. 2. Show an indication of this in the browser UI outside of the content area. It would be perfect for this kind of app.
- runjake 1y ago
  In this case, itt's easier to just run it locally: https://github.com/invisal/sqlite-internal https://github.com/invisal/sqlite-internal But you can get sort of what you want by: 1. Loading a site. 2. Go into the Web Dev Tools. 3. Select the Network tab. 4. Click on the "No Throttling" dropdown and check "Offline". 5. You should now see a yellow warning icon in your Network tab and it should prevent all network access. If you reload the tab you should get the "No Internet Connection" error page.

jchw 1y ago

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page shorter than the other pages. You can see that illustrated clearly if you click into the first page of a database using this tool: the database header comes first, then the page header. This tool will undoubtedly come in handy for anyone who has a reason to be dealing with SQLite data structures directly for whatever reason, especially since the SQLite documentation is a bit terse at times.

hinkley 1y ago
I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.
- jchw 1y ago
  If you want to maintain the properties that SQLite has for read use cases, you'll need to replicate a couple of features. At the very least, you'll probably want the format to still be page-based with a BTree structure. You really could get away with just using the SQLite format if you didn't mind the weirdness; a functional SQLite parser that can read tables would not be a significant amount of code. I think, though, that if you want to read the schema as SQLite understands it, you'd need to interpret the CREATE TABLE syntax, which would make it a bit more complex for sure. Otherwise, you can read tables and columns themselves relatively easily, and the values are all stringified.
  - hinkley 1y ago
    Yeah if I wasn’t clear I’m talking about a minimal file that SQLite can still open read only without errors, not a third party implementation. Though there might be a few tweaks that would allow SQLite to be a bit more lenient. For instance missing metadata that can be assumed. Maybe b tree nodes exceeding the usual load factor.

hamstergene 1y ago

This is super cool and will really help anyone studying how to build their own storage engines. A note: the table that describes the data structures (Field # | Description | Value) should also list the data type (uint8 uint16 int32 text etc.). If it's an enum ("Table Leaf"), reference other possible enum values. If its length depends on another field, reference that field No.

nraynaud 1y ago

this is a wonderful learning tool, thank you.

roelschroeven 1y ago

I tried with a simple database, and it just shows me a blank screen. The console in developer tools shows an exception (both in Firefox and Chromium): index-CGfYebmt.js:85 Uncaught RangeError: Offset is outside the bounds of the DataView at DataView.prototype.getUint32 (<anonymous>) at R0 (index-CGfYebmt.js:85:4683) at X0 (index-CGfYebmt.js:85:7966) at index-CGfYebmt.js:85:8157 at Array.map (<anonymous>) at Q0 (index-CGfYebmt.js:85:8150) at index-CGfYebmt.js:85:8240 at Object.useMemo (index-CGfYebmt.js:48:46087) at My.$.useMemo (index-CGfYebmt.js:17:7276) at V0 (index-CGfYebmt.js:85:8228) R0 @ index-CGfYebmt.js:85 X0 @ index-CGfYebmt.js:85 (anonymous) @ index-CGfYebmt.js:85 Q0 @ index-CGfYebmt.js:85 (anonymous) @ index-CGfYebmt.js:85 useMemo @ index-CGfYebmt.js:48 My.$.useMemo @ index-CGfYebmt.js:17 V0 @ index-CGfYebmt.js:85 tc @ index-CGfYebmt.js:48 bc @ index-CGfYebmt.js:48 lo @ index-CGfYebmt.js:48 No @ index-CGfYebmt.js:48 wm @ index-CGfYebmt.js:48 Xc @ index-CGfYebmt.js:48 Ao @ index-CGfYebmt.js:48 Qo @ index-CGfYebmt.js:48 me @ index-CGfYebmt.js:25

invisal 1y ago
Probably I might made mistake somewhere in my parser. Do you have small reproducible database with no sensitive database and add to the https://github.com/invisal/sqlite-internal/issues https://github.com/invisal/sqlite-internal/issues I will try to fix it
- roelschroeven 1y ago
  Issue created: https://github.com/invisal/sqlite-internal/issues/3 https://github.com/invisal/sqlite-internal/issues/3
- roelschroeven 1y ago
  Fixed now!

T3RMINATED 1y ago

[dead]