
SQL Notebook - mmsimanga
https://sqlnotebook.com/
======
electroly
Hello everyone! Author here. I didn't expect anyone to find this repo, much
less post it on Hacker News!

This project is inactive for two main reasons:

\- SQLite is not a great general-purpose SQL engine. Poor performance of joins
is a serious problem that I couldn't solve. The virtual table support is good
but not quite good enough; not enough parts of the query are pushed down into
the virtual table interface to permit efficient querying of remote tables.
Many "ALTER" features are not implemented in SQLite which is a tough sell for
experimental data manipulation.

\- T-SQL, the procedural language I chose to implement atop SQLite, is not a
great general-purpose programming language. Using C# in LINQpad is a more
pleasant experience for experimentally messing around with data. R Studio is a
good option if you need statistical functions.

I think several good solutions in this problem space exist. A local install of
SQL Server Express can be linked to remote servers, allowing you to join local
tables to remote ones. That setup serves nearly all of SQL Notebook's use
cases better than SQL Notebook does. LINQpad is also very convenient for a lot
of use cases.

I appreciate the interest! I may spin off the import/export functionality into
its own app someday, as I had a lot of plans in that area, but I think SQL
Notebook as it stands is a bit too flawed to develop fully.

~~~
coleifer
SQLite definitely presents some unique challenges, despite it generally being
the "simplest" relational database. I've written a web-based SQLite database
browser, which you can find here: [https://github.com/coleifer/sqlite-
web](https://github.com/coleifer/sqlite-web) \-- importantly, it supports all
the `ALTER TABLE` statements you'd expect (add column, drop column, rename
column, etc). This is done by renaming the table temporarily, creating a new
table with the desired changes, then copying the data into the new table.
Kinda clunky, but it works :)

~~~
kalleboo
Which is basically how MySQL ALTER TABLE works internally anyway...

------
bobochan
This looks very interesting.

I recently had to teach a series of workshops on SQL and I was trying to
figure out the best system to allow students to independently work with small
datasets without having to install any software. I found Alon Zakai's
absolutely fantastic version of SQLite in JavaScript here:

[https://github.com/kripken/sql.js](https://github.com/kripken/sql.js)

I coupled that library with a CodeMirror editor and got a working web based
environment very quickly.

~~~
frik
There is the WebSQL standard, it's works for the majority of web users:
[https://en.wikipedia.org/wiki/Web_SQL_Database](https://en.wikipedia.org/wiki/Web_SQL_Database)

Only Mozilla dev, a die-hard NoSQL fan, prevented adoption in Firefox (he
since left Mozilla) and Microsoft couldn't decide which of it's 10 SQL engines
(no joke, think about MS Access, AD, Exchange, Outlook, WinFS, etc all
had/have their own embedded SQL database incarnations) to use in Internet
Explorer. In the end Microsoft nowadays ships SQLite with Windows. But WebSQL
isn't supported by Firefox and IE/Edge - but the web users moved on, and like
99% of mobile devices have a webkit/blink based browser, and Chrome and Safari
have the largest user base on desktop too.

~~~
Groxx
It's not quite that simple. The WebSQL standard wasn't a "standard" at all, it
was just "browsers already expose whatever SQLite they have, lets write that
down!". No tests, no conformity, no way to tell if browser X supported SQLite
feature Y, nothing. Just raw access to whatever SQLite was baked in. There
were lots of inconsistencies.

Yeah, it was super simple and useful, but nobody wanted to do the work to make
sure interoperability was a thing, in part because SQLite has very complex
behavior and lots of quirks to adhere to.

------
lima
Jupyter/IPython + [https://github.com/catherinedevlin/ipython-
sql](https://github.com/catherinedevlin/ipython-sql) is a wonderful workflow
for interactive DB exploration.

~~~
dil8
This is awesome! Thanks for sharing.

------
nrjames
I generally use the Firefox SQLite Manager extension when I need to explore
SQLite databases. It serves its purpose pretty well, though it has some
annoyances and UI quirks. [https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manage...](https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manager/)

~~~
mythrwy
In a terminal, `sqlite3 mydb.` works pretty well also. I find this generally a
lot faster than GUI tools for quick peek or some queries (although for complex
relations and visualization a good GUI is the thing to have). Not a huge MS
fan but SQL Management Studio is pretty good (don't know about sqlite
support.. I bet it's possible though).

.tables (list tables).

.schema <table> (show table schema).

.help shows commands.

Or just enter some SQL to get results.

~~~
voltagex_
It'd be really cool to use SSMS for SQLite. It's _possible_ but not nice:
[https://superuser.com/questions/138787/is-it-possible-to-
ope...](https://superuser.com/questions/138787/is-it-possible-to-open-a-
sqlite-database-from-within-microsoft-sql-server-manage)

DataGrip from Jetbrains looks promising but the connect/disconnect/setup
process is really painful.

------
TeMPOraL
Ouch, that would be very useful to me had I known about it two months ago,
when I was exploring the database dump from my old Wordpress blog (I'm
finalizing the process of re-launching it as a static site). I managed though,
by combination of MySQL Workbench and Common Lisp REPL.

Anyway, bookmarking for the next time I'll need to play with relational data.

------
probdist
Looks pretty neat. Reminds me a bit of Linqpad,
[https://www.linqpad.net/](https://www.linqpad.net/) which I've also never
used.

~~~
macca321
LINQPad is amazing. I have a full licence and have persuaded a couple of
companies to buy site licences before.

It's would be great if there was some kind of full version trial so people
could try it with full intellisense and package manager support.

~~~
hjalle
Yeah, LINQPad is great! I use it for so many weird cases, everything from
rather complex scraping with phantomjs to trying out things like regexs. I've
saved small scripts that make it easier to get into new projects, such as
making lists of files that have been changed the most and people who have the
most commits etc.. Really recommend it for .NET devs.

------
grouseway
Neat.

-How about import from clipboard (useful for cut and paste from excel)

-It doesn't seem to recognize tab delimiters in a .txt file. Maybe the import window should have a delimiter selector?

-Does it have a crosstab/pivot tool? Most sql dialects are lacking here because they make you explicitly define crosstab columns which is a pain for exploration work.

------
yread
Hmm looks nice but last commit was 8 months ago
[https://github.com/electroly/sqlnotebook](https://github.com/electroly/sqlnotebook)

------
ckdarby
Can't exactly see the value this brings that Apache Zeppelin doesn't already
offer.

[https://zeppelin.apache.org/](https://zeppelin.apache.org/)

~~~
recursive
It's got a straight-forward installer that's 6MB. I was able to run it and use
it in seconds.

Zeppelin has source available, or a binary package with a spark interpreter,
or a binary package with "all interpreters". That one is 712MB. If I just want
to use it, I still don't understand what to actually do. I'm downloading the
big .tgz as we speak.

Following the installation guide,

A:\TEMP\zeppelin\zeppelin-0.7.1-bin-all>bin\zeppelin.cmd Log dir doesn't
exist, create A:\TEMP\zeppelin\zeppelin-0.7.1-bin-all\logs Pid dir doesn't
exist, create A:\TEMP\zeppelin\zeppelin-0.7.1-bin-all\run The system cannot
find the path specified.

A:\TEMP\zeppelin\zeppelin-0.7.1-bin-all>mkdir logs A subdirectory or file logs
already exists.

A:\TEMP\zeppelin\zeppelin-0.7.1-bin-all>bin\zeppelin.cmd The system cannot
find the path specified.

And now I'm completely stuck.

So the value in this compared to Zeppelin is that it's small, straight-
forward, and simple.

~~~
petercooper
Says more about how long I haven't used a MS OS than anything but I initially
had a 5 second "why are they installing it on their floppy drive?" moment
there :-)

~~~
temp246810
Ha - yes DOS commands hold a special place in my heart so to speak.

The very first "terminal" like thing I learned as a really young kid was how
to load a floppy disk, which usually just meant typing a:.

Memories.

~~~
petercooper
Ditto. For me A: was always a 3.5" drive with B: being, optionally, a 5.25"
drive (if installed). Not sure how universal that was though. I do like how C:
has mostly hung on though.

------
agentultra
I've always wanted a nice SQL-oriented "notebook" type of application.

I get something of this experience in Emacs via `org-mode`, `sql-mode`, and
`ob-sql-mode` minus the data-importing functionality... though with babel it's
probably doable in a code block using a script.

Bonus: org-mode lets you export to many formats which makes sharing results
quite easy.

------
stared
For having R in notebooks (similar to Jupyter Notebooks) I really recommend
[http://rmarkdown.rstudio.com/authoring_knitr_engines.html](http://rmarkdown.rstudio.com/authoring_knitr_engines.html).

As a side benefit, it is easy to ggplot results. :)

------
carlosgg
I will check it out. You can also use R notebooks to embed SQL code in
notebook format.

[https://blog.rstudio.org/2016/10/05/r-notebooks/](https://blog.rstudio.org/2016/10/05/r-notebooks/)
(scroll down to "Batteries included")

I was playing around a bit with it:

[https://carlosror.github.io/baseball_mysql/](https://carlosror.github.io/baseball_mysql/)

------
Dnguyen
In my daily work I often have the need to analyze excel and csv files from
clients. I use [http://harelba.github.io/q/](http://harelba.github.io/q/) and
it worked most of the time. But this one seems promising. Especially being
able to query data from a file and join with data from a database.

~~~
j_s
CSV tool recommendations |
[https://news.ycombinator.com/item?id=7175830](https://news.ycombinator.com/item?id=7175830)

------
daveorzach
Is there any Windows SQL software that can use system/machine ODBC data
sources? My company uses OpenLink's ODBC drivers to access our main database
(Progress OpenEdge.) I have no problem using Python, Pandas, and pyodb to
connect to the data base but it isn't the best environment to develop queries.

~~~
eterm
MySQL workbench (it is available as a free download but you have to dodge
quite a few "No I really mean I want the free community ed" screens) is quite
good for that.

[https://www.mysql.com/products/workbench/](https://www.mysql.com/products/workbench/)

There are versions for linux and windows and iirc it supports any ODBC
connection as well as out of the box SSL tunnelling for mysql/postgres/etc.

------
educar
Very nice, I have been using [https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manage...](https://addons.mozilla.org/en-
US/firefox/addon/sqlite-manager/) so far. Looks like this can replace it.

------
krylon
At work, I routinely have a copy of SQL Server Management Studio open for the
odd ad-hoc query I need to run against our ERP system's database.

This tool looks like it might be a useful replacement for this purpose,
especially if it can handle CSV data, as well.

------
bognition
Windows only is a shame, nearly all devs I know use OSX or linux.

~~~
egeozcan
[https://insights.stackoverflow.com/survey/2016#technology-
de...](https://insights.stackoverflow.com/survey/2016#technology-desktop-
operating-system)

~~~
k3nx
How does an SO survey change the view of the comment? If they use Linux and
know three other developers two that use a Mac and one on Windows isn't the
comment still true? From what they see more folks use non-Windows. Also, not
every developer in the world filled out the SO survey. The comment was
lamenting that it was Windows Only, I think that's still relevant.

~~~
gdulli
The comment can lament whatever it wants, but its anecdotal evidence is
clearly implying a premise that's proven false by actual data that shows a
majority of developers use Windows.

------
kencausey
Anyone else understand what they are referring to in the Getting Started
notebook about a 'CREATE menu'? I don't see it anywhere.

------
bendykstra
I'm curious why it is not possible to import data from an SQLite file.

------
iagovar
Would this app be nice for a beginner with DB's for data analysis?

