Guides

Tables & Queries

How to access in-memory and persistent data tables.

Suggest Edits

Database Access

To connect to and run queries on your persistent data store, you'll need to define a connection using the Storage event, like so:

=Storage("database")

To query, simply point a Query event to this storage, and the storage event will return the results.

If the result is rows, you'll receive a list of rows. If the result is anything else, you'll receive a message, such as 11 rows effected.

📘

Postgres-compatibility & SQL nuances

Summit uses CockroachDB for its persistent data storage due to its incredible durability, and SQLite to filter in-memory tables due to its ephemeral speed. While the vast majority of SQL syntax is supported by both, there can be tiny differences, and your Query syntax should take this into account. If you're asking GPT to write your SQL, just tell them Cockroach Labs or SQLite sent you. ;-)

Table

A lot of business processes involve the usage of data stored in a remotely-accessible CSV file. These files are often "published" spreadsheets (using a service like Google Sheets or Microsoft Excel) or even a read-only database view or "clip."

When you want to pull this data into SEL, you can use Table to fetch and structure the data, and Query to run ad hoc queries on it.

To pull a remote CSV file accessible by URL into a Summit, you can use:

=Table('https://docs.google.com/...'<csv_url>)

This will spin up a small database inside your model and store the table uniquely inside of it. In other words, you are welcome to use this event in multiple places, and the data will be stored in different tables:

=Table('https://data.heroku.com/clips/...'<data_url>)

In the SQL of downstream Query events, you will refer to this table as simple $table. For example: SELECT count(*) FROM $table.

You may also wish to pass Table a list of dictionaries, such as the output of a Parser event. You can do this by leaving the Table argument empty:

=Table()

In either case, you may provide an optional argument for the name of the table:

=Table("contacts")

Or

=Table("https://sheets.google.com/...", "contacts")

This will load the input data (or the data from the remote CSV) into a table in your model called contacts.

This can be useful for writing a query in an event that is not directly downstream of the table.

📘

In-Memory SQL is SQLite

Summit uses in-memory SQLite databases for Table, so all of your Query syntax should observe what's included in the SQLite dialect when working with Table events.

Learn more about SQLite's syntax and function library.

Query

Once you have a data file stored inside a Table or Storage, you'll most likely want to run a query on it. In SEL, we can do this by passing SQL to an event like so:

=Query("SELECT min(price) FROM $table WHERE status = 'ACTIVE'")

An example using Table, Query and Response Data.

#!/usesummit/sel/0.1a

"csv_table": =Table("https://docs.google.com/spreadsheets/d/e/2PACX-1vRg7CfZbL3V_h4fNzgl8KqTO7JKSuJ1NqJ03C3i4P6eK12jSDbOGPvTBPhzJMoQmkVEBLYUl2r5v5Sf/pub?gid=0&single=true&output=csv")

"csv_rows": =Query("SELECT * FROM $table LIMIT 2")

"my_csv_response": =Response("my_csv_data")

"csv_table" -> "csv_rows" -> "my_csv_response"

Multiline SQL

You can also write multi-line SQL queries by wrapping your query in """:

#!/usesummit/sel/0.1a

"select_images": =Query("""
	SELECT * FROM media_store
""")

"db": =Storage("database")

"rows": =Response("output")

"select_images" -> "db" -> "rows"

Updated 2 months ago