8 ms·

Franchise – An Open-Source SQL Notebook

398 points by laser 9y ago

budhajeewa 9y ago
Rich man's phpMyAdmin?
- RussianCow 9y ago
  Why do you have to be rich? It's free!
  - swalsh 9y ago
    A man can be rich and have no money at all. For instance, a man might have Franchise instead of PHPMyAdmin.
agentultra 9y ago
Cool but it's hosted. I'd pay for a sql-notebook application that ran on OSX. I'm always working on speculative features or analysis but security, policy, and portability reasons I'd rather have a desktop app.
- homarp 9y ago
  run it locally: https://github.com/hvf/franchise https://github.com/hvf/franchise
neuland 9y ago
Like someone else said, this is hosted (but there's a way to run it yourself). But, I don't see any pricing. Is that something they plan on adding later. Or, how do they plan to keep it online over time?
- pgeorgi 9y ago
  "Your data never hits our servers" That seems to be a pure local web-app. Kind of a remix of the local aspect of Tiddly Wiki and DabbleDB's approach to data massaging.
bduerst 9y ago
Looks cool. Any plans for D3.js reporting integration? How would someone add their own reporting types?
- bijection 9y ago
  Take a look at https://github.com/HVF/franchise/blob/master/src/cell/visualizer.js https://github.com/HVF/franchise/blob/master/src/cell/visual... Basically, each visualization is a react widget with some static properties that define its icon / when to show it / etc.
bijection 9y ago
Co-creator here! We wanted * A simple, flexible SQL client * A notebook interface that lets you drag stuff around and view things side-by-side * Query CSVs, JSON, and XLSX documents fully in-browser with Emscripten + SQLite * Postgres, MySQL and BigQuery through a local connection bridge. Your data never touches a third party server. I’ll be here with @antimatter15 for the rest of the morning answering questions!
- dvfjsdhgfv 9y ago
  Thank you so much for your great work! This is exactly the kind of post I visit HN for!
- gglanzani 9y ago
  Hi, Any plan to support jdbc so that it’s possible to connect to things such is HiveServer2/SparkSql?
  - sandGorgon 9y ago
    same here - actually, today we use zeppelin, which is kind of a pain to setup on a cluster. I see that you are using Reactjs. If you can build something that can connect to spark and python, I will pay for this and so will lots of people. One of the big unsolved issues where we want to really throw money is have a notebook that my analysts can work on and then I can take straight to production and have it run multi-user. There are lots of startups attempting to do this on top of Jupyter, but I believe building this from scratch is the right way. I'm literally crying right now for a reactjs + d3js dashboard running on pyspark that multiple people can use simultaneously.
  - bijection 9y ago
    We're hoping to add support, ideally with the help of the open source community! You can follow along at https://github.com/HVF/franchise/issues/5 https://github.com/HVF/franchise/issues/5
- sixdimensional 9y ago
  This is really cool :) I nearly worked on such a project myself, out of frustration for simple solutions. I've seen similar things recently with SQL Notebook, Zeppelin, Jupyter, Flyspeed SQL query.. and more complex desktop apps like Tableau, QlikView, etc. I kept thinking to myself... why isn't there just a lighter weight query only tool. It helps people to learn, it's a handy utility, it helps you just use SQL as a utility for quick tasks using a UI - very nice. A few questions/comments: - Are you planning to put this in an Electron app? I think the more you can make it feel like an actual desktop app, the more likely you are to get more "regular" users. Just my opinion. - While I love the simplicity of making this sound like a cloud service, I think that the nature of it being provided through the user's own infrastructure should be emphasized even more. As far as I can tell (and I haven't looked extensively), there is nothing that is actually "cloud oriented" about your service. Is it because people make there own "cloud" out of your bridge adapter? For people who want these tools on-premise and under their own control, you have a solution.
snaky 9y ago
How is it different from SQL support in Jupyter notebook?
- lima 9y ago
  And superset[0]: https://github.com/apache/incubator-superset https://github.com/apache/incubator-superset
  - postila 9y ago
    and redash https://redash.io/ https://redash.io/ and many more
  - Mizza 9y ago
    I've used superset and honestly, it kinda sucked as a user. Maybe it will thrive now it's not under AirBnB's ownership, but I more than welcome competition in this space.
    - lima 9y ago
      Could you elaborate? Currently evaluating it for internal use.
- antimatter15 9y ago
  Co-creator here. For existing SQL tools there's currently basically two approaches: where either the full application is hosted, or where the full application is local. Jupyter and Zeppelin generally fall into the second category— you have to set up Docker (or the myriad of dependencies), and edit a bunch of configuration files to launch a local server before getting started. If someone sends you a notebook, and you just want to read its contents and see the interactive charts, you still have to go through this ordeal. On the other hand, there's Mode, Redash, and PopSQL, which are hosted solutions. Editing configuration files is replaced with a one-time setup and registration, but you have to entrust these startups with access to all your data. Franchise takes a hybrid approach— it's a hosted static web page that contains all of the display logic, so if you save a notebook and share it with a friend, you can open it and it just works. To connect to a database, you start a local bridge which tunnels data from Franchise directly to your database through your computer. We spent a lot of time thinking about the interface and trying to make it easy to use, but still powerful. We built a new notebook layout engine that allows you to run two queries and see the results side-by-side. Deleting a cell sends it to an archive, so you don't have to worry about losing data. Data visualizations are accessible with one click. And if you're just trying to query data on a CSV or JSON file, you can just drag and drop it onto the page.
geokon 9y ago
This looks really cool! I've been meaning to learn SQL for a long time, but I'm unfortunately in an unrelated field. Could anyone recommend some resources for learning SQL? Preferable targeted towards an experienced programmer
- zengid 9y ago
  Khan academy has a great in-browser environment for learning. Unfortunately the lessons are given in a tone that seems to be targeted towards middle-schoolers, so it can feel a bit irritating at times.
- felixge 9y ago
  Check out https://www.pgexercises.com/ https://www.pgexercises.com/ . If you like learning by doing, it's a great resource.
- robbles 9y ago
  https://sqlschool.modeanalytics.com/toc/ https://sqlschool.modeanalytics.com/toc/
- ams6110 9y ago
  "Experienced programmer" can be a hindrance to learning SQL, depending. You will need to set aside all your imperative, procedural habits. To use SQL effectively you need to think in terms of sets, unions, intersections, and declarative statements. If you find yourself thinking "row at a time" you might need to stop yourself. It's not always wrong, but a common mistake many programmers make when first learning SQL.
- siddboots 9y ago
  What's your unrelated field? In my experience there's a way to shoehorn SQL into any application if you really want to. Sometimes even when you really don't want to.
- hantusk 9y ago
  I thought this text adventure approach is a cool way of learning: http://wwwlgis.informatik.uni-kl.de/extra/game/ http://wwwlgis.informatik.uni-kl.de/extra/game/
reaperducer 9y ago
> Disconnected from SQLite (Error: Invalid LatLng object: (NaN, NaN)) Too bad, I was looking forward to that mapping demo. It might be just what I need. I'll try again in a few hours.
- bijection 9y ago
  What browser are you using? It's working for me on chrome and safari.
  - ptrott2017 9y ago
    Not OP - but you get that error in Microsoft Edge on windows 10. Demo works on Internet Explorer 11 on windows (but without map option using Crime in LA example) and in Edge all the demos appear to run, map and graphs get populated etc, then page looses the db connection.
    - bijection 9y ago
      Thanks! We're looking into it.
stuxnet79 9y ago
Tried running it locally and it's one of the most pleasant experiences I've had in recent memory. Extremely short number of steps and no hiccups. I already had something running on port 3000 and it even had a Y/N flag asking me if I wanted to run it on a different port ... I was like wow! Great job OP and I'll be sure to try this out as we are having lots of difficulties managing data at my workplace.
- stuxnet79 9y ago
  "Restore from the most recent autosave" ... wow! You guys really went the distance to make Franchise user friendly.
megamindbrian 9y ago
Can you integrate this with jupyter instead? Since there is already a nice UI made for it?
HFTGuru 9y ago
Are there plans for Teradata support in the near future?
- bijection 9y ago
  We're hoping to work with the community to add support for new databases. If you open an issue at https://github.com/hvf/franchise/issues https://github.com/hvf/franchise/issues we can track Teradata progress there.
ocdtrekkie 9y ago
I just pulled up the crime stats example data: How do you tell it that a given table can be plotted on a map? I don't have the file with me at the moment, but I do have an SQLite database with coords and datetimes I'd love to plot.
- bijection 9y ago
  Here's the test it currently runs: https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68998315d57c61f9f572/src/cell/visualizer.js#L166 https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68... Basically, it looks for geo-ish column names.
  - ocdtrekkie 9y ago
    That should work for me! Thanks!
seacatdog 9y ago
This is awesome, I really like the interactive components. Great work!
SwellJoe 9y ago
This is super impressive, and small, too. This is a lot less code than I would have imagined for such an ambitious project. The JS ecosystem has gotten really strong in recent years in terms of library support and how concisely complex tasks can be implemented.
manigandham 9y ago
It looks like this is using Blueprint JS for the UI framework: http://blueprintjs.com/ http://blueprintjs.com/
shaunol 9y ago
This looks really nice. Having a bit of trouble getting the bridge running on node v6.11 on Windows 10. The app mentions requiring the latest version of node but it's not clear whether you need the standard LTS (6.11) or the bleeding edge (8.5). Thanks! > npx franchise-client@0.2.2 franchise-client\server.js:16 ws.on('message', async message => { ^^^^^ SyntaxError: missing ) after argument list at createScript (vm.js:56:10) at Object.runInThisContext (vm.js:97:10) at Module._compile (module.js:542:28) at Object.Module._extensions..js (module.js:579:10) at Module.load (module.js:487:32) at tryModuleLoad (module.js:446:12) at Function.Module._load (module.js:438:3) at Module.runMain (module.js:604:10) at run (bootstrap_node.js:389:7) at startup (bootstrap_node.js:149:9)
- queicherius 9y ago
  Since it fails after an "async" keyword which is supported since node v7.6 I assume they mean bleeding edge.
jhgjklj 9y ago
An sqlite client in the browser ? Is that it.
chime 9y ago
Would love to use it. But I get this error in Chrome when I try to connect: > Disconnected from PostgreSQL (Error: Too many result rows to serialize: Try using a LIMIT statement.) This is on my console (my credentials replaced with ellipses): ~> npx franchise-client@0.2.2 npx: installed 140 in 12.533s franchise-client listening on port 14645 opened connection received: {"action":"get_postgres_credentials","id":1} received: {"action":"open","db":"postgres","credentials":{"id":1,"host":"localhost","user":"...","database":"...","port":"...","autofilled":true,"password":"..."},"id":2} received: {"action":"exec","sql":"SELECT table_schema, table_name, column_name\n FROM information_schema.columns \n WHERE table_schema not in ('pg_catalog', 'information_schema', 'pg_internal')","id":3} Error: Too many result rows to serialize: Try using a LIMIT statement. at Object.query (~/.npm/_npx/736/lib/node_modules/franchise-client/response.js:128:23) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) I ran the SELECT query in Valentina Studio and got 11000+ rows in 4s. Maybe raise the acceptable row count to 100,000?
e12e 9y ago
This looks great! Support for Google sheets, other than export/import? I was recently surprised at how horrible an experience it is to script Google sheets - in that case for "survey" (questionnaire form) data - either locally with python or (shudder) the js/script functionality of Google sheets...
drej 9y ago
added 1523 packages in 94.397s node_modules: 329 715 665 bytes (435,4 MB on disk) for 37 881 items I know it looks cool and animates and all that, but... really?!
cheeeerio 9y ago
How does it compare to https://airtable.com/ https://airtable.com/?
awake 9y ago
I think this is a really clean product but I have a couple of questions. 1. I'm dealing with a dataset with >10M rows. Is there any way to kill a query that is currently running? I noticed at least with postgres that a long running query would stop me from performing any other queries. 2. The hashtag notation is very nice but would it be possible to cache the results of the hashtag without having to run the same query over again. This would more closely resemble the behavior of jupyter notebook, where results in previous cells can be recalculated by running those cells, but can be quickly accessed without recalculation.
statsig 9y ago
This looks incredible! Congrats, guys! Does it support Google Authentication for sign in and data access restriction features for sensitive data?

398 points by laser 9y ago

budhajeewa 9y ago

Rich man's phpMyAdmin?

RussianCow 9y ago
Why do you have to be rich? It's free!
- swalsh 9y ago
  A man can be rich and have no money at all. For instance, a man might have Franchise instead of PHPMyAdmin.

agentultra 9y ago

Cool but it's hosted. I'd pay for a sql-notebook application that ran on OSX. I'm always working on speculative features or analysis but security, policy, and portability reasons I'd rather have a desktop app.

homarp 9y ago
run it locally: https://github.com/hvf/franchise https://github.com/hvf/franchise

neuland 9y ago

Like someone else said, this is hosted (but there's a way to run it yourself). But, I don't see any pricing. Is that something they plan on adding later. Or, how do they plan to keep it online over time?

pgeorgi 9y ago
"Your data never hits our servers" That seems to be a pure local web-app. Kind of a remix of the local aspect of Tiddly Wiki and DabbleDB's approach to data massaging.

bduerst 9y ago

Looks cool. Any plans for D3.js reporting integration? How would someone add their own reporting types?

bijection 9y ago
Take a look at https://github.com/HVF/franchise/blob/master/src/cell/visualizer.js https://github.com/HVF/franchise/blob/master/src/cell/visual... Basically, each visualization is a react widget with some static properties that define its icon / when to show it / etc.

bijection 9y ago

Co-creator here! We wanted * A simple, flexible SQL client * A notebook interface that lets you drag stuff around and view things side-by-side * Query CSVs, JSON, and XLSX documents fully in-browser with Emscripten + SQLite * Postgres, MySQL and BigQuery through a local connection bridge. Your data never touches a third party server. I’ll be here with @antimatter15 for the rest of the morning answering questions!

dvfjsdhgfv 9y ago
Thank you so much for your great work! This is exactly the kind of post I visit HN for!
gglanzani 9y ago
Hi, Any plan to support jdbc so that it’s possible to connect to things such is HiveServer2/SparkSql?
- sandGorgon 9y ago
  same here - actually, today we use zeppelin, which is kind of a pain to setup on a cluster. I see that you are using Reactjs. If you can build something that can connect to spark and python, I will pay for this and so will lots of people. One of the big unsolved issues where we want to really throw money is have a notebook that my analysts can work on and then I can take straight to production and have it run multi-user. There are lots of startups attempting to do this on top of Jupyter, but I believe building this from scratch is the right way. I'm literally crying right now for a reactjs + d3js dashboard running on pyspark that multiple people can use simultaneously.
- bijection 9y ago
  We're hoping to add support, ideally with the help of the open source community! You can follow along at https://github.com/HVF/franchise/issues/5 https://github.com/HVF/franchise/issues/5
sixdimensional 9y ago
This is really cool :) I nearly worked on such a project myself, out of frustration for simple solutions. I've seen similar things recently with SQL Notebook, Zeppelin, Jupyter, Flyspeed SQL query.. and more complex desktop apps like Tableau, QlikView, etc. I kept thinking to myself... why isn't there just a lighter weight query only tool. It helps people to learn, it's a handy utility, it helps you just use SQL as a utility for quick tasks using a UI - very nice. A few questions/comments: - Are you planning to put this in an Electron app? I think the more you can make it feel like an actual desktop app, the more likely you are to get more "regular" users. Just my opinion. - While I love the simplicity of making this sound like a cloud service, I think that the nature of it being provided through the user's own infrastructure should be emphasized even more. As far as I can tell (and I haven't looked extensively), there is nothing that is actually "cloud oriented" about your service. Is it because people make there own "cloud" out of your bridge adapter? For people who want these tools on-premise and under their own control, you have a solution.

snaky 9y ago

How is it different from SQL support in Jupyter notebook?

lima 9y ago
And superset[0]: https://github.com/apache/incubator-superset https://github.com/apache/incubator-superset
- postila 9y ago
  and redash https://redash.io/ https://redash.io/ and many more
- Mizza 9y ago
  I've used superset and honestly, it kinda sucked as a user. Maybe it will thrive now it's not under AirBnB's ownership, but I more than welcome competition in this space.
  - lima 9y ago
    Could you elaborate? Currently evaluating it for internal use.
antimatter15 9y ago
Co-creator here. For existing SQL tools there's currently basically two approaches: where either the full application is hosted, or where the full application is local. Jupyter and Zeppelin generally fall into the second category— you have to set up Docker (or the myriad of dependencies), and edit a bunch of configuration files to launch a local server before getting started. If someone sends you a notebook, and you just want to read its contents and see the interactive charts, you still have to go through this ordeal. On the other hand, there's Mode, Redash, and PopSQL, which are hosted solutions. Editing configuration files is replaced with a one-time setup and registration, but you have to entrust these startups with access to all your data. Franchise takes a hybrid approach— it's a hosted static web page that contains all of the display logic, so if you save a notebook and share it with a friend, you can open it and it just works. To connect to a database, you start a local bridge which tunnels data from Franchise directly to your database through your computer. We spent a lot of time thinking about the interface and trying to make it easy to use, but still powerful. We built a new notebook layout engine that allows you to run two queries and see the results side-by-side. Deleting a cell sends it to an archive, so you don't have to worry about losing data. Data visualizations are accessible with one click. And if you're just trying to query data on a CSV or JSON file, you can just drag and drop it onto the page.

geokon 9y ago

This looks really cool! I've been meaning to learn SQL for a long time, but I'm unfortunately in an unrelated field. Could anyone recommend some resources for learning SQL? Preferable targeted towards an experienced programmer

zengid 9y ago
Khan academy has a great in-browser environment for learning. Unfortunately the lessons are given in a tone that seems to be targeted towards middle-schoolers, so it can feel a bit irritating at times.
felixge 9y ago
Check out https://www.pgexercises.com/ https://www.pgexercises.com/ . If you like learning by doing, it's a great resource.
robbles 9y ago
https://sqlschool.modeanalytics.com/toc/ https://sqlschool.modeanalytics.com/toc/
ams6110 9y ago
"Experienced programmer" can be a hindrance to learning SQL, depending. You will need to set aside all your imperative, procedural habits. To use SQL effectively you need to think in terms of sets, unions, intersections, and declarative statements. If you find yourself thinking "row at a time" you might need to stop yourself. It's not always wrong, but a common mistake many programmers make when first learning SQL.
siddboots 9y ago
What's your unrelated field? In my experience there's a way to shoehorn SQL into any application if you really want to. Sometimes even when you really don't want to.
hantusk 9y ago
I thought this text adventure approach is a cool way of learning: http://wwwlgis.informatik.uni-kl.de/extra/game/ http://wwwlgis.informatik.uni-kl.de/extra/game/

reaperducer 9y ago

> Disconnected from SQLite (Error: Invalid LatLng object: (NaN, NaN)) Too bad, I was looking forward to that mapping demo. It might be just what I need. I'll try again in a few hours.

bijection 9y ago
What browser are you using? It's working for me on chrome and safari.
- ptrott2017 9y ago
  Not OP - but you get that error in Microsoft Edge on windows 10. Demo works on Internet Explorer 11 on windows (but without map option using Crime in LA example) and in Edge all the demos appear to run, map and graphs get populated etc, then page looses the db connection.
  - bijection 9y ago
    Thanks! We're looking into it.

stuxnet79 9y ago

Tried running it locally and it's one of the most pleasant experiences I've had in recent memory. Extremely short number of steps and no hiccups. I already had something running on port 3000 and it even had a Y/N flag asking me if I wanted to run it on a different port ... I was like wow! Great job OP and I'll be sure to try this out as we are having lots of difficulties managing data at my workplace.

stuxnet79 9y ago
"Restore from the most recent autosave" ... wow! You guys really went the distance to make Franchise user friendly.

megamindbrian 9y ago

Can you integrate this with jupyter instead? Since there is already a nice UI made for it?

HFTGuru 9y ago

Are there plans for Teradata support in the near future?

bijection 9y ago
We're hoping to work with the community to add support for new databases. If you open an issue at https://github.com/hvf/franchise/issues https://github.com/hvf/franchise/issues we can track Teradata progress there.

ocdtrekkie 9y ago

I just pulled up the crime stats example data: How do you tell it that a given table can be plotted on a map? I don't have the file with me at the moment, but I do have an SQLite database with coords and datetimes I'd love to plot.

bijection 9y ago
Here's the test it currently runs: https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68998315d57c61f9f572/src/cell/visualizer.js#L166 https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68... Basically, it looks for geo-ish column names.
- ocdtrekkie 9y ago
  That should work for me! Thanks!

seacatdog 9y ago

This is awesome, I really like the interactive components. Great work!

SwellJoe 9y ago

This is super impressive, and small, too. This is a lot less code than I would have imagined for such an ambitious project. The JS ecosystem has gotten really strong in recent years in terms of library support and how concisely complex tasks can be implemented.

manigandham 9y ago

It looks like this is using Blueprint JS for the UI framework: http://blueprintjs.com/ http://blueprintjs.com/

shaunol 9y ago

This looks really nice. Having a bit of trouble getting the bridge running on node v6.11 on Windows 10. The app mentions requiring the latest version of node but it's not clear whether you need the standard LTS (6.11) or the bleeding edge (8.5). Thanks! > npx franchise-client@0.2.2 franchise-client\server.js:16 ws.on('message', async message => { ^^^^^ SyntaxError: missing ) after argument list at createScript (vm.js:56:10) at Object.runInThisContext (vm.js:97:10) at Module._compile (module.js:542:28) at Object.Module._extensions..js (module.js:579:10) at Module.load (module.js:487:32) at tryModuleLoad (module.js:446:12) at Function.Module._load (module.js:438:3) at Module.runMain (module.js:604:10) at run (bootstrap_node.js:389:7) at startup (bootstrap_node.js:149:9)

queicherius 9y ago
Since it fails after an "async" keyword which is supported since node v7.6 I assume they mean bleeding edge.

jhgjklj 9y ago

An sqlite client in the browser ? Is that it.

chime 9y ago

Would love to use it. But I get this error in Chrome when I try to connect: > Disconnected from PostgreSQL (Error: Too many result rows to serialize: Try using a LIMIT statement.) This is on my console (my credentials replaced with ellipses): ~> npx franchise-client@0.2.2 npx: installed 140 in 12.533s franchise-client listening on port 14645 opened connection received: {"action":"get_postgres_credentials","id":1} received: {"action":"open","db":"postgres","credentials":{"id":1,"host":"localhost","user":"...","database":"...","port":"...","autofilled":true,"password":"..."},"id":2} received: {"action":"exec","sql":"SELECT table_schema, table_name, column_name\n FROM information_schema.columns \n WHERE table_schema not in ('pg_catalog', 'information_schema', 'pg_internal')","id":3} Error: Too many result rows to serialize: Try using a LIMIT statement. at Object.query (~/.npm/_npx/736/lib/node_modules/franchise-client/response.js:128:23) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) I ran the SELECT query in Valentina Studio and got 11000+ rows in 4s. Maybe raise the acceptable row count to 100,000?

e12e 9y ago

This looks great! Support for Google sheets, other than export/import? I was recently surprised at how horrible an experience it is to script Google sheets - in that case for "survey" (questionnaire form) data - either locally with python or (shudder) the js/script functionality of Google sheets...

drej 9y ago

added 1523 packages in 94.397s node_modules: 329 715 665 bytes (435,4 MB on disk) for 37 881 items I know it looks cool and animates and all that, but... really?!

cheeeerio 9y ago

How does it compare to https://airtable.com/ https://airtable.com/?

awake 9y ago

I think this is a really clean product but I have a couple of questions. 1. I'm dealing with a dataset with >10M rows. Is there any way to kill a query that is currently running? I noticed at least with postgres that a long running query would stop me from performing any other queries. 2. The hashtag notation is very nice but would it be possible to cache the results of the hashtag without having to run the same query over again. This would more closely resemble the behavior of jupyter notebook, where results in previous cells can be recalculated by running those cells, but can be quickly accessed without recalculation.

statsig 9y ago

This looks incredible! Congrats, guys! Does it support Google Authentication for sign in and data access restriction features for sensitive data?