Use SQL with Stencila
Installation (Node.js context)
The Stencila Node.js package provides the
NodeContext with the
SQLiteContext for executing SQL (Structured Query Language) code from within Stencila documents.
This package comes bundled with Stencila Desktop so it does not need to be installed separately. But, for completeness, and because you may want to use it from another client application, we include these installation instructions here.
You may need to install
npmfirst. On Windows, these instructions may be useful.
Open a terminal session (on Windows, go to "Start" and then in "Search for programmes and files" type
cmd) and type in:
npm install stencila/node -g
Although this package is available on NPM, we currently recommend installing directly from the
masterbranch on Github as per the above instructions
Then register the package's manifest so that it can be found by other Stencila packages,
Stencila keeps track of the code dependecies between the code cells. It supports reactive programming which means that the code and the respective outputs will get updated, when you change a piece of code they depend on. It is a similar principle as behind the behavior of spreadsheet applications. You can refer to specific outputs from any code cell in any part of your Stencila document. Stencila does all this using its execution engine.
What inputs and outputs of a code cell are vary slightly between programming languages that you use in Stencila.
For cells written using SQL, the inputs are determined by parsing the
FROM clause of the SQL statement.
For example, the following cell has a single input
people, the value of the cell is the result of
the evaluation of SQL statement and the output is empty (
SELECT * FROM people ORDER BY height DESC
Parsing of inputs from more complicated SQL statements involving joins or sub-queries is currently not supported.
The the standard syntax of SQL means that the above cell has empty output in Stencila. If you want to capture the value of the evaluation of the cell
as the cell output, you need to explicitly use the assignment operator before the
For example, the following cell has an output
brown_hair and an input
brown_hair = SELECT * FROM people WHERE hair_color == 'Brown'
Stencila provides you with ability to use multiple programming languages to write interactive code within
one document, working on the same data. In other words, you can manipulate the same data switching between different programming
languages. This capability is achieved through
data interchange feature.
When you pass data between cells Stencila temporarily converts it into its internal schema representation.
Currently, only the
table data type can be exchanged with SQL. All data values are stored as temporary tables.