Data

The Data tool lets you save and read information in a private database built into MESA.

The Data allows you to persist information between workflow runs; saving data from one workflow and accessing it in another.

Configure

You can create tables from within any Data step that allows you to create or update a row, for example, Data's "Create Record" action.

Specify a table name and begin adding columns to your table.

Give each column a name and type by choosing one of our supported column types, and select the data to store in that column by selecting variables or adding static text to the column's value field.

Choose one of our supported column types:

It is recommended that you label your columns with no spaces included.

Columns will be in the same order as they're created here.

Three additional columns will be automatically added: mesa_id, mesa_created_at, mesa_updated_at

Click the workflow's "Save" button to save your columns to the database.

You can run one of the queries in the Altering Tables section below if you need to adjust your columns.

Populating a table

You can select the data to store in the column's value field.

One method is selecting through variables:

Another option is to add your own text:

Viewing a table

Go to My account under your account name.

Then, click on the Data tab.

Select View Data to browse the data in the table or Database Options to Query or Alter the table:

Using data stored in a table

Retrieve Record action

To use stored data in a table, you can use the Retrieve Record action. This retrieves a single row. For example, you can retrieve one particular order.

Query Action

This action retrieves multiple rows. For example, you can use this step to grab all of a user's orders.

Example Queries

Only show records that contain a string

SELECT * FROM "customers" WHERE "email" ILIKE '%@getmesa.com%';

Only show records created after a certain UTC date and time, sorted newest first:

SELECT * FROM "customers" WHERE "mesa_created_at" > '2022-01-04T17:51:00.000Z' 
  ORDER BY "mesa_created_at" DESC;

Join two tables together:

SELECT * FROM "orders" 
  LEFT JOIN "customers" ON "orders"."customer_id" = "customers"."id";

More examples and information about SELECT queries.

Record Created or Record Updated trigger

This step starts a workflow when a new row is added to the table or a row has one of its values changed.

Going Further

Alter Tables

You can remove columns or change their type by running an ALTER query. Go to the Settings Page and click the ⚙ cog icon next to the table. The query box will be pre-populated with some commented-out SQL queries to help you get started.

Remove a column

To remove a column, uncomment the first line and the DROP COLUMN line, replace {{column_name}} with your column, and click Run Query. For example:

ALTER TABLE "line_items"
  DROP COLUMN "{{column_name}}"

Change a column type

To change the column type, uncomment the first line and the ALTER COLUMN line corresponding to the column you would like to change by removing the -- at the beginning of each line. Then change the column type to one of the options in the Column types listed below:

ALTER TABLE "line_items"
  ALTER COLUMN "order_id" TYPE decimal

Note that in some cases (especially if you have existing data), changing the column may not be as easy as running the query above. In these cases, removing the column and re-adding it from the workflow builder with the new desired column type may be easier.

More examples and information about ALTER queries.

Delete a table

To delete a table, use the following query:

DROP TABLE "Add table name here"

You will need to change the Add table name here text to the name of your table and keep the double quotations.

Column types

Data supports the following column types:

• Text varchar(255): Ideal for most text strings (keys, titles, names, etc). Maximum length: 255 characters.

• Long Text text: For free-form text and JSON blobs.

• Integer int8: For integers and numeric IDs. Supports numbers between -9223372036854775808 and 9223372036854775807.

• Number numeric: For numbers with decimal-point precision.

• Date date: For dates.

• Date and time timestamptz: For date and times. Includes timezone information.

Connect to your data

Use your connection details from the Settings page to connect directly to your Data tool's database. You can use these credentials for a desktop querying client or business intelligence tool or read and write data directly from your custom application.

• Business Intelligence Clients

  • Metabase: an open-source BI tool for exploring, visualizing, and sharing data via a user-friendly interface, with support for both simple queries and advanced SQL

  • Tableau: a powerful analytics platform for creating interactive visualizations and dashboards, enabling users to explore and share data insights with ease.

• SQL Clients

  • Beekeeper Studio: an open-source SQL editor and database manager offering a clean interface for querying, editing, and managing databases efficiently.

  • TablePlus: a modern database management tool with a sleek interface designed to simplify querying, editing, and managing multiple databases efficiently.

Technical Notes

• Data is built on top of PostgresSQL and supports its features. Learn more about connecting to PostgreSQL databases.

• The Record Created and Record Updated triggers utilize polling to detect changes.