Table SQL Pack - User Guide

Get starts with the Table SQL Pack for Coda

Explore

Get starts with the Table SQL Pack for Coda

Query language

This Pack is based on the

SQLite⁠

project, and uses it’s flavor of SQL. You can read more about the query language here:

⁠

Query Language Understood by SQLite⁠

⁠

When making queries against Coda tables, there are some additional information to keep in mind.

Backquotes and spaces

When a table and column name contains spaces you’ll need to wrap it in backquotes: ` (the thing above the tab key).

My Stuff

My Stuff

The Name

Something Cool

Apple

Banana

Carrot

There are no rows in this table

⁠

SELECT `The Name`

FROM `My Stuff`

WHERE `Something Cool`;

⁠

⁠Apple⁠ ⁠ ⁠Carrot⁠ ⁠

⁠

Column values

When translating Coda columns to SQL columns, some column types are changed slightly:

Percent - A number between 0 and 1. Ex: 50% will be 0.5.

Currency - A number, without the currency symbol. Ex: $15 will be 15.

Checkbox and Toggle - A number, either 0 (false) or 1 (true). Ex: true will be 1.

Lookup - The display value of the row, or the ID or the row if enabled. See

Joining tables⁠

for more information.

If a column is configured to allow selecting multiple options (Select list, Lookup) the SQL column will return the selected values separated by a space.

Cost

Tax

Ready

Countries

$15.60

Japan

There are no rows in this table

⁠

[ { "Cost": 15.6, "Tax": 0.08, "Ready": 1, "Countries": "US,UK,Japan" } ]

⁠

Binding values

When you need to use user-data in your query, rather than try to escape it use a prepared query and bind the values to it. All of the building blocks have a values parameter that takes an array of string values you can bind to. In your query you can refer to these values with an equals sign ? , optionally followed by an index.

Query

Starts with

Country

Result

SELECT `First Name`

FROM Employees

WHERE `First Name` LIKE ? || '%'

⁠

Margaret⁠

⁠

Michael⁠

⁠

SELECT `First Name`

FROM Employees

WHERE Country = ?

USA

⁠

Nancy⁠

⁠

Andrew⁠

⁠

Janet⁠

⁠

Margaret⁠

⁠

Laura⁠

⁠

SELECT `First Name`

FROM Employees

WHERE

Country = ?2 AND

`First Name` LIKE ?1 || '%'

USA

⁠

Margaret⁠

⁠

There are no rows in this table

⁠

No extra values

Make sure that you use every value that you bind to the query. Unused values will cause the query to fail.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.