4 ms·

I'm not a data scientist, but I've been interested in the idea of a "code notebook" ever since Jupyter hit it big. I write mostly in JS/TS for application logic

I'm not a data scientist, but I've been interested in the idea of a "code notebook" ever since Jupyter hit it big. I write mostly in JS/TS for application logic, so this looks like it could be really useful. Related, does anyone have any recommendations of a (Postgres) SQL "notebook"? I don't really need any visualizations, more just a markdown integrated doc that allows me to lay out the different queries I use to answer a question.

gradys 5y ago
Maybe just a Python notebook with a Postgres client library and some helper functions to keep the amount of Python in the main body to a minimum?
Hasnep 5y ago
Rmarkdown notebooks can contain SQL chunks, so you'd only need to use R to configure the connection. [1] [1] https://bookdown.org/yihui/rmarkdown/language-engines.html#sql https://bookdown.org/yihui/rmarkdown/language-engines.html#s...
- keeganj 5y ago
  I didn't know you could write SQL directly in Rmarkdown like this, very interesting. Thanks!
  - pbowyer 5y ago
    Same, when I've read the docs I've always got the impression that it was R only supported.
    - PuddleOfSausage 5y ago
      There are loads more supported via knitr. Scroll to the top of that linked page in this thread for the list.
RocketSyntax 5y ago
Lots of jupyter magic `%` commands for that already https://www.datacamp.com/community/tutorials/sql-interface-within-jupyterlab https://www.datacamp.com/community/tutorials/sql-interface-w...
simonw 5y ago
Weirdly my Django SQL Dashboard project may fit the bill a bit here: you can build up a "dashboard" (which is a tiny bit notebook-like if you squint at it the right way) with multiple SQL queries on it, and save that either as a bookmark or as a "saved dashboard" with a URL. https://django-sql-dashboard.datasette.io/ https://django-sql-dashboard.datasette.io/ In my own work I've been using it for the kind of things that I would normally use a Jupyter notebook for - gathering together research on problems I'm trying to solve.
- keeganj 5y ago
  Interesting take, I'm not deep in the python ecosystem, but this looks like it's lightweight enough to function as a refreshable notebook. Will give this a try, thanks!
javierluraschi 5y ago
For viz/DS/ML/AI with JS/TS is either observablehq or and IDE with custom extensions; this project looks relevant if you are already into observablehq. Shameless plug, we are building a few tools for JS to narrow down this gap as well: - https://hal9.ai https://hal9.ai (Drag&Drop / IDE) - https://marketplace.visualstudio.com/items?itemName=Hal9.hal9 https://marketplace.visualstudio.com/items?itemName=Hal9.hal... (VSCode extension) - https://observablehq.com/@javierluraschi/running-nodejs-in-observable https://observablehq.com/@javierluraschi/running-nodejs-in-o... (ObservableHQ extension) Would love to chat if you are interested in providing feedback, I'm in javier at hal9.ai. Cheers.
shapiromatron 5y ago
re: sql notebook, this came up a few months ago and worked great when I played around with it: https://blog.jupyter.org/an-sql-solution-for-jupyter-ef4a00a0d925 https://blog.jupyter.org/an-sql-solution-for-jupyter-ef4a00a.... It's just a different kernel you can install to an existing jupyter instance.
okennedy 5y ago
It's based on Spark rather than Postgresql directly, but I'm part of an effort to build a workflow system disguised as a notebook callled Vizier [1]. SQL is a first-class primitive in Vizier, and the notebook plays nice with postgres (you can load from and unload to postgres using Spark's native data loader). [1] https://vizierdb.info https://vizierdb.info
amcaskill 5y ago
I am working on a SQL-in-markdown reporting tool called evidence. It’s feels like a markdown doc that runs SQL. https://evidence.dev/ https://evidence.dev/
- keeganj 5y ago
  This is almost exactly what I was imagining. Just subscribed to updates, very interested to see what this becomes!
natrys 5y ago
Emacs and Org-mode has great integration with multiple SQL implementations including Postgres (via org-babel). Org-mode tables are pretty neat, and you can have query result directly populated into tables. Read this blogpost if you are interested: https://fluca1978.github.io/2021/01/18/PostgreSQLLiterateProgramming.html https://fluca1978.github.io/2021/01/18/PostgreSQLLiteratePro...
tlarkworthy 5y ago
https://observablehq.com/@observablehq/databases https://observablehq.com/@observablehq/databases
qbasic_forever 5y ago
I like the ipython-sql magic in Jupyter: https://github.com/catherinedevlin/ipython-sql https://github.com/catherinedevlin/ipython-sql Depending on what you're doing you might be able to get away entirely with just using it and some basic queries, i.e. no python glue code in the notebook at all. But worst case you might need a cell to open up the DB connection and make the magic aware of it, then you can execute clean and simple SQL queries in cells using the magic.
- thejosh 5y ago
  Yeah ipython-sql is great and works well, and can use an environment variable for the connection string.
sixdimensional 5y ago
Apache Zeppelin is one open source option - https://zeppelin.apache.org https://zeppelin.apache.org.
Siira 5y ago
org-babel should fit the bill.
robertlacok 5y ago
Deepnote has native Postgres cells :) you can mix them with Python too. Disclaimer - I work there :)

gradys 5y ago

Maybe just a Python notebook with a Postgres client library and some helper functions to keep the amount of Python in the main body to a minimum?

Hasnep 5y ago

Rmarkdown notebooks can contain SQL chunks, so you'd only need to use R to configure the connection. [1] [1] https://bookdown.org/yihui/rmarkdown/language-engines.html#sql https://bookdown.org/yihui/rmarkdown/language-engines.html#s...

keeganj 5y ago
I didn't know you could write SQL directly in Rmarkdown like this, very interesting. Thanks!
- pbowyer 5y ago
  Same, when I've read the docs I've always got the impression that it was R only supported.
  - PuddleOfSausage 5y ago
    There are loads more supported via knitr. Scroll to the top of that linked page in this thread for the list.

RocketSyntax 5y ago

Lots of jupyter magic `%` commands for that already https://www.datacamp.com/community/tutorials/sql-interface-within-jupyterlab https://www.datacamp.com/community/tutorials/sql-interface-w...

simonw 5y ago

Weirdly my Django SQL Dashboard project may fit the bill a bit here: you can build up a "dashboard" (which is a tiny bit notebook-like if you squint at it the right way) with multiple SQL queries on it, and save that either as a bookmark or as a "saved dashboard" with a URL. https://django-sql-dashboard.datasette.io/ https://django-sql-dashboard.datasette.io/ In my own work I've been using it for the kind of things that I would normally use a Jupyter notebook for - gathering together research on problems I'm trying to solve.

keeganj 5y ago
Interesting take, I'm not deep in the python ecosystem, but this looks like it's lightweight enough to function as a refreshable notebook. Will give this a try, thanks!

javierluraschi 5y ago

For viz/DS/ML/AI with JS/TS is either observablehq or and IDE with custom extensions; this project looks relevant if you are already into observablehq. Shameless plug, we are building a few tools for JS to narrow down this gap as well: - https://hal9.ai https://hal9.ai (Drag&Drop / IDE) - https://marketplace.visualstudio.com/items?itemName=Hal9.hal9 https://marketplace.visualstudio.com/items?itemName=Hal9.hal... (VSCode extension) - https://observablehq.com/@javierluraschi/running-nodejs-in-observable https://observablehq.com/@javierluraschi/running-nodejs-in-o... (ObservableHQ extension) Would love to chat if you are interested in providing feedback, I'm in javier at hal9.ai. Cheers.

shapiromatron 5y ago

re: sql notebook, this came up a few months ago and worked great when I played around with it: https://blog.jupyter.org/an-sql-solution-for-jupyter-ef4a00a0d925 https://blog.jupyter.org/an-sql-solution-for-jupyter-ef4a00a.... It's just a different kernel you can install to an existing jupyter instance.

okennedy 5y ago

It's based on Spark rather than Postgresql directly, but I'm part of an effort to build a workflow system disguised as a notebook callled Vizier [1]. SQL is a first-class primitive in Vizier, and the notebook plays nice with postgres (you can load from and unload to postgres using Spark's native data loader). [1] https://vizierdb.info https://vizierdb.info

amcaskill 5y ago

I am working on a SQL-in-markdown reporting tool called evidence. It’s feels like a markdown doc that runs SQL. https://evidence.dev/ https://evidence.dev/

keeganj 5y ago
This is almost exactly what I was imagining. Just subscribed to updates, very interested to see what this becomes!

natrys 5y ago

Emacs and Org-mode has great integration with multiple SQL implementations including Postgres (via org-babel). Org-mode tables are pretty neat, and you can have query result directly populated into tables. Read this blogpost if you are interested: https://fluca1978.github.io/2021/01/18/PostgreSQLLiterateProgramming.html https://fluca1978.github.io/2021/01/18/PostgreSQLLiteratePro...

tlarkworthy 5y ago

https://observablehq.com/@observablehq/databases https://observablehq.com/@observablehq/databases

qbasic_forever 5y ago

I like the ipython-sql magic in Jupyter: https://github.com/catherinedevlin/ipython-sql https://github.com/catherinedevlin/ipython-sql Depending on what you're doing you might be able to get away entirely with just using it and some basic queries, i.e. no python glue code in the notebook at all. But worst case you might need a cell to open up the DB connection and make the magic aware of it, then you can execute clean and simple SQL queries in cells using the magic.

thejosh 5y ago
Yeah ipython-sql is great and works well, and can use an environment variable for the connection string.

sixdimensional 5y ago

Apache Zeppelin is one open source option - https://zeppelin.apache.org https://zeppelin.apache.org.

Siira 5y ago

org-babel should fit the bill.

robertlacok 5y ago

Deepnote has native Postgres cells :) you can mix them with Python too. Disclaimer - I work there :)