7 ms·

Show HN: SQL workbench in the browser

117 points by tobilg 3y ago

xnx 3y ago
[flagged]
- deleted 3y ago
  [deleted]
abtinf 3y ago
This is interesting. Any chance you will open source it?
- tobilg 3y ago
  Eventually at a later point in time! I‘m currently integrating the AI-based generation of queries…
  - shdh 3y ago
    Nice, I was working on something similar. Using meta queries to fill my system prompt with the entire schema of the db in a condensed format. It was working quite well! Would love to hear about your approach.
    - tobilg 3y ago
      Cool, my approach is basically the same, put the schema in the system prompt automatically, and the user prompt from the UI, and render the resulting SQL back to the UI. Question is more where to host this "on the cheap" because this is a free service, and I can't just spend hundreds of Dollars/month to keep it running... Do you have any recommendations?
      - shdh 3y ago
        If you're open sourcing it just include a docker-compose in the root directory. I'm sure most people would like to self-host. In general I don't think this would eat too many resources, just throw it on a VPS using systemd.
tobilg 3y ago
The SQL Workbench is built upon DuckDB WASM, Perspective.js and React. It supports the querying remote and local data (Parquet, CSV and JSON), data visualizations and the sharing of multiple queries via URL. There‘s also a tutorial blog post at https://tobilg.com/using-duckdb-wasm-for-in-browser-data-engineering https://tobilg.com/using-duckdb-wasm-for-in-browser-data-eng... that explains common usage patterns. Happy to answer any questions!
- paddy_m 3y ago
  Perspective is an awesome table library, very powerful and fast. I get the sense it's mostly used for internal finance applications and not for web apps much.
  - dav43 3y ago
    I integrated the perspective js into the datasette.io as a plugin as I was dealing with a larger number of rows. Its not bad. The map by GPS isn't as great as Kepler.gl, and for some reason, perspective doesn't work so well in corporate offices that may be using Remote Browser Instances... had some issues.
9dev 3y ago
I’ve been working on something similar to this, but using the WASM build of SQLite, which is amazing. But then I started implementing a small file browser for the Origin private FS API, just so I could manage the database files when debugging, you see - and I got so thoroughly sidetracked, I only noticed when I finished the multi-modal CSV file editor which could show CSV files as sortable tables, using a streaming, web worker based parser no less. Shortly after I found the whole thing way too bothersome and forgot about it completely until now. It’s a dangerous business, creating side projects. Before you know it, you build streaming parsers :)
- simonw 3y ago
  My version of this kind of thing the WASM build of Python which includes a WASM build of SQLite, running my Datasette server-side web application entirely in the browser. Here's a SQL query executed against that parquet file of AWS edge locations: https://lite.datasette.io/?parquet=https://raw.githubusercontent.com/tobilg/aws-edge-locations/main/data/aws-edge-locations.parquet#/data?sql=select+rowid%2C+%5Bindex%5D%2C+code%2C+city%2C+state%2C+country%2C+countryCode%2C+count%2C+latitude%2C+longitude%2C+region%2C+pricingRegion+from+%5Baws-edge-locations%5D+order+by+rowid+limit+101 https://lite.datasette.io/?parquet=https://raw.githubusercon...
gelatocar 3y ago
This is great, I've been meaning to build something similar for some time. I tried to run the queries from the tutorial but hit lots of CORS errors loading the datasets, is there any way that you have found to work around those?
- deleted 3y ago
  [deleted]
- tobilg 3y ago
  I'm sorry, I think I fixed this. Can you check? Thanks for letting me know!
  - gelatocar 3y ago
    Sorry, it seems to still be happening, running: SELECT count(*) FROM 'https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_2023-01.parquet'; Shows the error: Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_2023-01.parquet https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_20.... (Reason: CORS header ‘Access-Control-Allow-Origin’ missing). Status code: 200. I get the same issue in both firefox and brave edit: actually accessing that file directly gets a Access Denied error for me
    - tobilg 3y ago
      Sorry, can you please check again? https://sql-workbench.com/#queries=v0,SELECT-count(*)-FROM-'https%3A%2F%2Fdata.quacking.cloud%2Fnyc%20taxi%20data%2Fyellow_tripdata_2023%2001.parquet'~ https://sql-workbench.com/#queries=v0,SELECT-count(*)-FROM-'... Thanks!
      - gelatocar 3y ago
        Yep, looking good now, thanks! Other datasets that don't have CORS headers still don't work ie. https://sql-workbench.com/#config=%7B%22plugin%22%3A%22Datagrid%22%2C%22plugin_config%22%3A%7B%22columns%22%3A%7B%7D%2C%22editable%22%3Afalse%2C%22scroll_lock%22%3Afalse%7D%2C%22title%22%3A%22Export%22%2C%22group_by%22%3A%5B%5D%2C%22split_by%22%3A%5B%5D%2C%22columns%22%3A%5B%22ERROR%22%5D%2C%22filter%22%3A%5B%5D%2C%22sort%22%3A%5B%5D%2C%22expressions%22%3A%5B%5D%2C%22aggregates%22%3A%7B%7D%7D&queries=v0,SELECT-count(*)-FROM-'https%3A%2F%2Fd37ci6vzurychx.cloudfront.net%2Ftrip%20data%2Fgreen_tripdata_2023%2001.parquet'~ https://sql-workbench.com/#config=%7B%22plugin%22%3A%22Datag... but I'm guessing that's inherent limitation of running this without a backend
archiewood 3y ago
This is a relatively similar architecture to that we are building at Evidence.dev (open-source data viz framework) Architecture: https://evidence.dev/blog/why-we-built-usql/ https://evidence.dev/blog/why-we-built-usql/ 1. Query SQL databases, APIs, or local data (eg CSV) 2. Compile all the data sources into Parquet files 3. DuckDB-WASM in the browser that allows you to aggregate across sources 4. Users write code in DuckDB SQL and Markdown, enriched with viz components (built in Svelte) Some things that we have learned in the process: - It can be pretty performant up to about 20M rows of data in the parquet files, - Above a certain level, for speed, it's helpful to sort your data in your parquet files to take advantage of DuckDB's predicate pushdown - It's helpful to map DB types into a smaller set of Arrow types when you convert to Parquet - otherwise you have to consider a lot of different cases in the browser when you render in JS - DuckDB-WASM still has some rough edges, though is improving fast
- tobilg 3y ago
  I love Evidence.dev, it’s a great tool!
  - archiewood 3y ago
    We're really excited to see all the stuff built on DuckDB-WASM too! I love how fast your workbench runs, and how effortlessly it renders 60k rows in the browser Some UX feedback, if you're open to it: - It was initially unintiutive for me that I needed to highlight a whole SQL statement to get Ctrl-Enter to run it. Also a prompt that this was the correct shortcut would help! - I dragged in a CSV file, and the name was too long to show up in your table explorer, so I couldn't tell what the table name had been called (it turned out I needed to `select * from table_name.csv` - the csv postfix was unexpected - The CSV file had headers, but they were not auto detected - would be good to be able to configure this
    - tobilg 3y ago
      Thanks for the feedback! Regarding the run query shortcut, if you open the page, there‘s a comment banner on the top of the editor that mentions it. It‘s not the first time that I hear/read that people overlooked it though. What would be a more intuitive way from your POV? I‘ll look into the horizontal scrolling/CSV header issues, thanks!
T3RMINATED 3y ago
[dead]

117 points by tobilg 3y ago

xnx 3y ago

[flagged]

deleted 3y ago
[deleted]

abtinf 3y ago

This is interesting. Any chance you will open source it?

tobilg 3y ago
Eventually at a later point in time! I‘m currently integrating the AI-based generation of queries…
- shdh 3y ago
  Nice, I was working on something similar. Using meta queries to fill my system prompt with the entire schema of the db in a condensed format. It was working quite well! Would love to hear about your approach.
  - tobilg 3y ago
    Cool, my approach is basically the same, put the schema in the system prompt automatically, and the user prompt from the UI, and render the resulting SQL back to the UI. Question is more where to host this "on the cheap" because this is a free service, and I can't just spend hundreds of Dollars/month to keep it running... Do you have any recommendations?
    - shdh 3y ago
      If you're open sourcing it just include a docker-compose in the root directory. I'm sure most people would like to self-host. In general I don't think this would eat too many resources, just throw it on a VPS using systemd.

tobilg 3y ago

The SQL Workbench is built upon DuckDB WASM, Perspective.js and React. It supports the querying remote and local data (Parquet, CSV and JSON), data visualizations and the sharing of multiple queries via URL. There‘s also a tutorial blog post at https://tobilg.com/using-duckdb-wasm-for-in-browser-data-engineering https://tobilg.com/using-duckdb-wasm-for-in-browser-data-eng... that explains common usage patterns. Happy to answer any questions!

paddy_m 3y ago
Perspective is an awesome table library, very powerful and fast. I get the sense it's mostly used for internal finance applications and not for web apps much.
- dav43 3y ago
  I integrated the perspective js into the datasette.io as a plugin as I was dealing with a larger number of rows. Its not bad. The map by GPS isn't as great as Kepler.gl, and for some reason, perspective doesn't work so well in corporate offices that may be using Remote Browser Instances... had some issues.

9dev 3y ago

I’ve been working on something similar to this, but using the WASM build of SQLite, which is amazing. But then I started implementing a small file browser for the Origin private FS API, just so I could manage the database files when debugging, you see - and I got so thoroughly sidetracked, I only noticed when I finished the multi-modal CSV file editor which could show CSV files as sortable tables, using a streaming, web worker based parser no less. Shortly after I found the whole thing way too bothersome and forgot about it completely until now. It’s a dangerous business, creating side projects. Before you know it, you build streaming parsers :)

simonw 3y ago
My version of this kind of thing the WASM build of Python which includes a WASM build of SQLite, running my Datasette server-side web application entirely in the browser. Here's a SQL query executed against that parquet file of AWS edge locations: https://lite.datasette.io/?parquet=https://raw.githubusercontent.com/tobilg/aws-edge-locations/main/data/aws-edge-locations.parquet#/data?sql=select+rowid%2C+%5Bindex%5D%2C+code%2C+city%2C+state%2C+country%2C+countryCode%2C+count%2C+latitude%2C+longitude%2C+region%2C+pricingRegion+from+%5Baws-edge-locations%5D+order+by+rowid+limit+101 https://lite.datasette.io/?parquet=https://raw.githubusercon...

gelatocar 3y ago

This is great, I've been meaning to build something similar for some time. I tried to run the queries from the tutorial but hit lots of CORS errors loading the datasets, is there any way that you have found to work around those?

deleted 3y ago
[deleted]
tobilg 3y ago
I'm sorry, I think I fixed this. Can you check? Thanks for letting me know!
- gelatocar 3y ago
  Sorry, it seems to still be happening, running: SELECT count(*) FROM 'https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_2023-01.parquet'; Shows the error: Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_2023-01.parquet https://data.quacking.cloud/nyc-taxi-data/yellow_tripdata_20.... (Reason: CORS header ‘Access-Control-Allow-Origin’ missing). Status code: 200. I get the same issue in both firefox and brave edit: actually accessing that file directly gets a Access Denied error for me
  - tobilg 3y ago
    Sorry, can you please check again? https://sql-workbench.com/#queries=v0,SELECT-count(*)-FROM-'https%3A%2F%2Fdata.quacking.cloud%2Fnyc%20taxi%20data%2Fyellow_tripdata_2023%2001.parquet'~ https://sql-workbench.com/#queries=v0,SELECT-count(*)-FROM-'... Thanks!
    - gelatocar 3y ago
      Yep, looking good now, thanks! Other datasets that don't have CORS headers still don't work ie. https://sql-workbench.com/#config=%7B%22plugin%22%3A%22Datagrid%22%2C%22plugin_config%22%3A%7B%22columns%22%3A%7B%7D%2C%22editable%22%3Afalse%2C%22scroll_lock%22%3Afalse%7D%2C%22title%22%3A%22Export%22%2C%22group_by%22%3A%5B%5D%2C%22split_by%22%3A%5B%5D%2C%22columns%22%3A%5B%22ERROR%22%5D%2C%22filter%22%3A%5B%5D%2C%22sort%22%3A%5B%5D%2C%22expressions%22%3A%5B%5D%2C%22aggregates%22%3A%7B%7D%7D&queries=v0,SELECT-count(*)-FROM-'https%3A%2F%2Fd37ci6vzurychx.cloudfront.net%2Ftrip%20data%2Fgreen_tripdata_2023%2001.parquet'~ https://sql-workbench.com/#config=%7B%22plugin%22%3A%22Datag... but I'm guessing that's inherent limitation of running this without a backend

archiewood 3y ago

This is a relatively similar architecture to that we are building at Evidence.dev (open-source data viz framework) Architecture: https://evidence.dev/blog/why-we-built-usql/ https://evidence.dev/blog/why-we-built-usql/ 1. Query SQL databases, APIs, or local data (eg CSV) 2. Compile all the data sources into Parquet files 3. DuckDB-WASM in the browser that allows you to aggregate across sources 4. Users write code in DuckDB SQL and Markdown, enriched with viz components (built in Svelte) Some things that we have learned in the process: - It can be pretty performant up to about 20M rows of data in the parquet files, - Above a certain level, for speed, it's helpful to sort your data in your parquet files to take advantage of DuckDB's predicate pushdown - It's helpful to map DB types into a smaller set of Arrow types when you convert to Parquet - otherwise you have to consider a lot of different cases in the browser when you render in JS - DuckDB-WASM still has some rough edges, though is improving fast

tobilg 3y ago
I love Evidence.dev, it’s a great tool!
- archiewood 3y ago
  We're really excited to see all the stuff built on DuckDB-WASM too! I love how fast your workbench runs, and how effortlessly it renders 60k rows in the browser Some UX feedback, if you're open to it: - It was initially unintiutive for me that I needed to highlight a whole SQL statement to get Ctrl-Enter to run it. Also a prompt that this was the correct shortcut would help! - I dragged in a CSV file, and the name was too long to show up in your table explorer, so I couldn't tell what the table name had been called (it turned out I needed to `select * from table_name.csv` - the csv postfix was unexpected - The CSV file had headers, but they were not auto detected - would be good to be able to configure this
  - tobilg 3y ago
    Thanks for the feedback! Regarding the run query shortcut, if you open the page, there‘s a comment banner on the top of the editor that mentions it. It‘s not the first time that I hear/read that people overlooked it though. What would be a more intuitive way from your POV? I‘ll look into the horizontal scrolling/CSV header issues, thanks!

T3RMINATED 3y ago

[dead]