
Franchise – An Open-Source SQL Notebook - laser
https://franchise.cloud/
======
bijection
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!

~~~
sixdimensional
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.

~~~
chrisweekly
I fell in love w/ a fantastic little CLI util called 'lnav' which has an
embedded sqlite db and lets you do all kinds of querying (eg write SQL queries
against a few million rows of server logs in a custom log format, or use regex
to transform files to/from arbitrary formats...).
[http://lnav.org](http://lnav.org)

It's sort of a mini ETL powertool.

------
stuxnet79
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
"Restore from the most recent autosave" ... wow! You guys really went the
distance to make Franchise user friendly.

------
awake
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.

------
snaky
How is it different from SQL support in Jupyter notebook?

~~~
antimatter15
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.

~~~
arikfr
Redash creator here.

One correction about Redash: Redash is open source and you can host it
yourself too, in case you have an issue trusting a 3rd party with your data.

The proxy approach is something I thought about several times, but I am aiming
at providing a collaborative tool, where it's easy to share the results and
work with others. Depending on a proxy running on a user machine breaks this.

But having said that, I'm sure there are many whose needs Franchise will fit
very well. So kudos on launching a great tool! :)

~~~
spapas82
Hello, happy redash self hosted user here :)

I'd like to confirm, we are hosting redash 2.0 (recently updated from 1.0) to
our own server and use it as a query portal and collaboration tool to a number
of databases (including mysql, postgres and oracle). Query sharing is really
important there are users that don't know SQL but know how to run "peter-21"
or "jim-32" with redash.

Everything is smooth till now thank you for the great product!

------
SwellJoe
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.

------
chime
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?

------
shaunol
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
Since it fails after an "async" keyword which is supported since node v7.6 I
assume they mean bleeding edge.

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

~~~
bijection
Take a look at
[https://github.com/HVF/franchise/blob/master/src/cell/visual...](https://github.com/HVF/franchise/blob/master/src/cell/visualizer.js)

Basically, each visualization is a react widget with some static properties
that define its icon / when to show it / etc.

------
geokon
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

~~~
felixge
Check out [https://www.pgexercises.com/](https://www.pgexercises.com/) . If
you like learning by doing, it's a great resource.

------
manigandham
It looks like this is using Blueprint JS for the UI framework:
[http://blueprintjs.com/](http://blueprintjs.com/)

------
e12e
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...

------
ocdtrekkie
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
Here's the test it currently runs:
[https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68...](https://github.com/HVF/franchise/blob/b9b1b8414bbc930f7e1a68998315d57c61f9f572/src/cell/visualizer.js#L166)

Basically, it looks for geo-ish column names.

~~~
ocdtrekkie
That should work for me! Thanks!

------
agentultra
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
run it locally:
[https://github.com/hvf/franchise](https://github.com/hvf/franchise)

------
neuland
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
"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.

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

------
reaperducer
> 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
What browser are you using? It's working for me on chrome and safari.

~~~
ptrott2017
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
Thanks! We're looking into it.

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

------
drej
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
How does it compare to [https://airtable.com/](https://airtable.com/)?

------
HFTGuru
Are there plans for Teradata support in the near future?

~~~
bijection
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.

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

------
jhgjklj
An sqlite client in the browser ? Is that it.

------
budhajeewa
Rich man's phpMyAdmin?

~~~
RussianCow
Why do you have to be rich? It's free!

~~~
swalsh
A man can be rich and have no money at all. For instance, a man might have
Franchise instead of PHPMyAdmin.

