6 ms·

Show HN: SQLite Playground

210 points by nalgeon 5y ago

nalgeon 5y ago
What I've always lacked is something similar to JSFiddle / CodePen, but for SQLite. An online playground to quickly test the SQL query and share it with others. Here is what I wanted: — Binary database import, not just SQL schema. — Support both local and remote databases (by url). — Save the database and queries in the cloud. — Free of charge, no sign-up required. — The latest version of SQLite. — Minimalism. So I did it myself — an online SQLite playground for debugging and sharing SQL snippets. Sample database: https://sqlime.org/#gist:e012594111ce51f91590c4737e41a046 https://sqlime.org/#gist:e012594111ce51f91590c4737e41a046
leobg 5y ago
Very cool. What better way to learn than to test and play and iterate. Thanks for making this! And beautiful it is, too.
- nalgeon 5y ago
  Thanks a lot! Glad you find it useful.
pugworthy 5y ago
no .tables command?
- nalgeon 5y ago
  Nope — that's not SQL. Dot commands are available only in SQLite's CLI. But there are 'more > show tables' command in the toolbar and Ctrl+/ shortcut which do exactly that.
  - pugworthy 5y ago
    True, but this is a SQLite CLI, isn't it? Run in a web browser?
- bartvk 5y ago
  Heh, that's the first thing I tried, "show tables"
- Scorpiion 5y ago
  As others have mentioned there is a menu option to show the tables. You can also run this sql query to see the tables: SELECT name FROM sqlite_master WHERE type='table'
nicolasmelo12 5y ago
really nice that you've used wasm and no server. This seems really secure to test. I was thinking at first: Hey, this might be kinda dangerous, imagine iterating in a tool and sending my database to a server and any attacker retrieves the database. But since it runs everything inside of the browser with wasm i found it WAY more secure. Nice that you've done it, will probably use it a lot.
- Siira 5y ago
  It can't change under you on a whim though, can't it? Unless you host it yourself.
  - jazzyjackson 5y ago
    Sure but you can always keep the network tab in the devtools open and be confident nothing is getting exfiltrated
    - krageon 5y ago
      Once you see a connection doing so, it is too late :)
- nalgeon 5y ago
  Thanks! I've always liked the idea of SQL in the browser, since the Web SQL times. That part is provided by the excellent sql.js project btw: https://github.com/sql-js/sql.js/ https://github.com/sql-js/sql.js/
- gizlu 5y ago
  It would of course also increase server's potential attack layer. sqlite had some bugs, potentially exploitable, if you are able to invoke arbitrary statements https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html https://www.sqlite.org/security.html https://www.sqlite.org/security.html
Scaevolus 5y ago
How does this compare to phiresky's work? Can you include the FTS5 module?
- nalgeon 5y ago
  Their work is a great POC how to handle large databases in the browser. Mine is a tool for testing and sharing small snippets of SQL code.
shekhirin 5y ago
Good work, always happy to see new tools related to SQLite and Wasm! As I see, currently you're loading whole DB into the memory, so large databases might become a problem. Did you think of adopting https://wicg.github.io/file-system-access https://wicg.github.io/file-system-access, so users could query their databases directly from filesystem?
- chx 5y ago
  https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database... might be of interest too.
- nalgeon 5y ago
  Thanks! Not really — the playground is meant for small databases. Just as one won't develop the whole app in JSFiddle, they probably won't import a large database to the playground.
- krageon 5y ago
  > It is not a W3C Standard nor is it on the W3C Standards Track In other words, it should not be used. More concretely IIRC it only works on Chrome.
dspillett 5y ago
Nice. A good wrapper around sql.js has been in my (ever-growing) want/todo list for some time. Good to see a decent implementation of the idea!
- nalgeon 5y ago
  True, sql.js just asks for something like that :–) Thank you!
nemoniac 5y ago
I'm afraid I don't see the point of this at all. What does it offer that running sqlite from my command line does not?
- osmarks 5y ago
  Presumably use on bad devices with no CLI available. Also sharing URLs with people to show them a thing quickly.
- tfsh 5y ago
  The same argument could be said for most web-based playgrounds. I'd argue for small prototyping, playgrounds/pens offer an easier interface and enable sharing of artifacts
- severine 5y ago
  Five of the six points the OP mentions are relevant, specially #5: > — The latest version of SQLite. And minimalism is certainly appreciated also!
failingGrace 5y ago
This is great! Very useful - great work on getting it up and running. Coincidentally I threw together something very similar recently to run sqlite over csv files. It's very WIP right now and missing lots of useful things (e.g. currently every column is TEXT and maany more QOL things) but remarkably similar to sqlime! https://csvee.io/ https://csvee.io/
lixtra 5y ago
How does it differ from http://sqlfiddle.com/ http://sqlfiddle.com/ ?
- nalgeon 5y ago
  It's easier to say how are they similar: both are capable of executing SQL in the browser. The rest is different.
- nerdponx 5y ago
  I have been using https://www.db-fiddle.com/ https://www.db-fiddle.com/
chovybizzass 5y ago
Why are you asking for my github creds? You don't really expect me to give you my github password do you?
RobinL 5y ago
Love it! It'd be nice to have a couple more example datasets, and I'd promote them to the top level (rather than in 'more'). I think the first thing many users will want to do is just try to tool with some pre-provided datasets - and when it works nicely (which it does!) then maybe import their own data
- nalgeon 5y ago
  Thanks! I was even thinking of loading the demo database by default (instead of creating the empty one).
paperwork 5y ago
Looks great! I created something similar at http://sqlforever.com/ http://sqlforever.com/
chanar 5y ago
Awesome, looks like using sqlite in browser is trending and there are new tools created often. Even I created a tool :) https://news.ycombinator.com/item?id=25791207 https://news.ycombinator.com/item?id=25791207
banana_giraffe 5y ago
Looks good, only thing I'd like to see added is the wholenumber extension. It's surprisingly useful for creating mock data for tests.
citrus 5y ago
query results are not escaped. haha https://sqlime.org/#gist:d31fd00895d34821797ccde8f94169fe https://sqlime.org/#gist:d31fd00895d34821797ccde8f94169fe

210 points by nalgeon 5y ago

What I've always lacked is something similar to JSFiddle / CodePen, but for SQLite. An online playground to quickly test the SQL query and share it with others. Here is what I wanted: — Binary database import, not just SQL schema. — Support both local and remote databases (by url). — Save the database and queries in the cloud. — Free of charge, no sign-up required. — The latest version of SQLite. — Minimalism. So I did it myself — an online SQLite playground for debugging and sharing SQL snippets. Sample database: https://sqlime.org/#gist:e012594111ce51f91590c4737e41a046 https://sqlime.org/#gist:e012594111ce51f91590c4737e41a046

leobg 5y ago

Very cool. What better way to learn than to test and play and iterate. Thanks for making this! And beautiful it is, too.

nalgeon 5y ago
Thanks a lot! Glad you find it useful.

pugworthy 5y ago

no .tables command?

nalgeon 5y ago
Nope — that's not SQL. Dot commands are available only in SQLite's CLI. But there are 'more > show tables' command in the toolbar and Ctrl+/ shortcut which do exactly that.
- pugworthy 5y ago
  True, but this is a SQLite CLI, isn't it? Run in a web browser?
bartvk 5y ago
Heh, that's the first thing I tried, "show tables"
Scorpiion 5y ago
As others have mentioned there is a menu option to show the tables. You can also run this sql query to see the tables: SELECT name FROM sqlite_master WHERE type='table'

nicolasmelo12 5y ago

really nice that you've used wasm and no server. This seems really secure to test. I was thinking at first: Hey, this might be kinda dangerous, imagine iterating in a tool and sending my database to a server and any attacker retrieves the database. But since it runs everything inside of the browser with wasm i found it WAY more secure. Nice that you've done it, will probably use it a lot.

Siira 5y ago
It can't change under you on a whim though, can't it? Unless you host it yourself.
- jazzyjackson 5y ago
  Sure but you can always keep the network tab in the devtools open and be confident nothing is getting exfiltrated
  - krageon 5y ago
    Once you see a connection doing so, it is too late :)
nalgeon 5y ago
Thanks! I've always liked the idea of SQL in the browser, since the Web SQL times. That part is provided by the excellent sql.js project btw: https://github.com/sql-js/sql.js/ https://github.com/sql-js/sql.js/
gizlu 5y ago
It would of course also increase server's potential attack layer. sqlite had some bugs, potentially exploitable, if you are able to invoke arbitrary statements https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html https://www.sqlite.org/security.html https://www.sqlite.org/security.html

Scaevolus 5y ago

How does this compare to phiresky's work? Can you include the FTS5 module?

nalgeon 5y ago
Their work is a great POC how to handle large databases in the browser. Mine is a tool for testing and sharing small snippets of SQL code.

shekhirin 5y ago

Good work, always happy to see new tools related to SQLite and Wasm! As I see, currently you're loading whole DB into the memory, so large databases might become a problem. Did you think of adopting https://wicg.github.io/file-system-access https://wicg.github.io/file-system-access, so users could query their databases directly from filesystem?

chx 5y ago
https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database... might be of interest too.
nalgeon 5y ago
Thanks! Not really — the playground is meant for small databases. Just as one won't develop the whole app in JSFiddle, they probably won't import a large database to the playground.
krageon 5y ago
> It is not a W3C Standard nor is it on the W3C Standards Track In other words, it should not be used. More concretely IIRC it only works on Chrome.

dspillett 5y ago

Nice. A good wrapper around sql.js has been in my (ever-growing) want/todo list for some time. Good to see a decent implementation of the idea!

nalgeon 5y ago
True, sql.js just asks for something like that :–) Thank you!

nemoniac 5y ago

I'm afraid I don't see the point of this at all. What does it offer that running sqlite from my command line does not?

osmarks 5y ago
Presumably use on bad devices with no CLI available. Also sharing URLs with people to show them a thing quickly.
tfsh 5y ago
The same argument could be said for most web-based playgrounds. I'd argue for small prototyping, playgrounds/pens offer an easier interface and enable sharing of artifacts
severine 5y ago
Five of the six points the OP mentions are relevant, specially #5: > — The latest version of SQLite. And minimalism is certainly appreciated also!

failingGrace 5y ago

This is great! Very useful - great work on getting it up and running. Coincidentally I threw together something very similar recently to run sqlite over csv files. It's very WIP right now and missing lots of useful things (e.g. currently every column is TEXT and maany more QOL things) but remarkably similar to sqlime! https://csvee.io/ https://csvee.io/

lixtra 5y ago

How does it differ from http://sqlfiddle.com/ http://sqlfiddle.com/ ?

nalgeon 5y ago
It's easier to say how are they similar: both are capable of executing SQL in the browser. The rest is different.
nerdponx 5y ago
I have been using https://www.db-fiddle.com/ https://www.db-fiddle.com/

chovybizzass 5y ago

Why are you asking for my github creds? You don't really expect me to give you my github password do you?

RobinL 5y ago

Love it! It'd be nice to have a couple more example datasets, and I'd promote them to the top level (rather than in 'more'). I think the first thing many users will want to do is just try to tool with some pre-provided datasets - and when it works nicely (which it does!) then maybe import their own data

nalgeon 5y ago
Thanks! I was even thinking of loading the demo database by default (instead of creating the empty one).

paperwork 5y ago

Looks great! I created something similar at http://sqlforever.com/ http://sqlforever.com/

chanar 5y ago

Awesome, looks like using sqlite in browser is trending and there are new tools created often. Even I created a tool :) https://news.ycombinator.com/item?id=25791207 https://news.ycombinator.com/item?id=25791207

banana_giraffe 5y ago

Looks good, only thing I'd like to see added is the wholenumber extension. It's surprisingly useful for creating mock data for tests.

citrus 5y ago

query results are not escaped. haha https://sqlime.org/#gist:d31fd00895d34821797ccde8f94169fe https://sqlime.org/#gist:d31fd00895d34821797ccde8f94169fe