# 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 <a href="#configuring" id="configuring"></a>

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.

<figure><img src="https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FZju6jTDIZqMJCnHxkmi4%2Fimage.png?alt=media&#x26;token=48eb539b-5303-46a8-a261-e0415e167871" alt=""><figcaption></figcaption></figure>

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](https://docs.getmesa.com/workflow-builder/fields/variables) or adding static text to the column's value field.

Choose one of our supported column types:

<table data-full-width="true"><thead><tr><th>Type</th><th>SQL Type</th><th>When to use</th><th>Example</th></tr></thead><tbody><tr><td>Text</td><td>varchar(255)</td><td>Ideal for most text strings (keys, titles, names, etc). Maximum length: 255 characters.</td><td>Jannette Parks</td></tr><tr><td>Long Text</td><td>text</td><td>For free-form text and JSON blobs.</td><td>Jannette Parks likes to go to the park and play on the swings, the merry-go-round, and the super-duper play structure. She's an adventurer and she likes the thrill. She would stay at the park all day long, every day in fact, if she had the chance, and play with her yeti friend, Yedric.</td></tr><tr><td>Integer</td><td>int8</td><td>For integers and numeric IDs. Supports numbers between -9223372036854775808 and 9223372036854775807.</td><td>13</td></tr><tr><td>Number</td><td>numeric</td><td>For numbers with decimal-point precision.</td><td>13.99</td></tr><tr><td>Date</td><td>date</td><td>For dates</td><td>2024-11-30</td></tr><tr><td>Date and time</td><td>timestamptz</td><td>For date and times, with timezone information.</td><td>2024-11-30 13:24:00-07</td></tr><tr><td>Boolean</td><td>bool</td><td>Values that are either true or false</td><td>True</td></tr></tbody></table>

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.

{% hint style="info" %}
Once you save your workflow, to avoid affecting workflows that may use the same table, you can no longer delete the column or adjust your column's Name or Type from the user interface.
{% endhint %}

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

### Populating a table <a href="#browsing" id="browsing"></a>

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

One method is selecting through [variables](https://docs.getmesa.com/workflow-builder/fields/variables):

<figure><img src="https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FZk8RcSQNhDPRdRbzFPGZ%2Fdata_as_variable.png?alt=media&#x26;token=e594ec34-452e-47d2-b072-917437c23bed" alt=""><figcaption></figcaption></figure>

Another option is to add your own text:

<figure><img src="https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2F1c3NAqLq3ecJSiu598eO%2Fimage.png?alt=media&#x26;token=1fddce30-83de-4257-a1f5-9847c59a79e8" alt=""><figcaption></figcaption></figure>

### Viewing a table <a href="#browsing" id="browsing"></a>

Go to My account under your account name.

![](https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FVAhr5y7ACzzgpoXMMXPw%2FAS-DATA-MW.png?alt=media\&token=73c8efcc-2b1d-4fb5-b249-a6ba52d2bc84)

Then, click on the Data tab.

<figure><img src="https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FcgPNRatcJ7y8ZeBLgIgb%2FScreenshot%202024-05-10%20at%2011.56.52%E2%80%AFPM.png?alt=media&#x26;token=ae782c82-a3c3-400a-9f9f-e7cdb39896da" alt=""><figcaption></figcaption></figure>

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

![](https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FJ6Fhvyjp2MvOwllGvevc%2FAS-DATA-VIEWDATA.png?alt=media\&token=f3ac36bc-2c1f-469e-8420-68a732232934)

![](https://3425906282-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H6u1HQc3Iew7ATmmiCi%2Fuploads%2FYpdmgoroJ9vemsZE4geq%2FAS-DATA-DBASEOPTIONS.png?alt=media\&token=1aabc626-3735-4dd3-bbe6-7a6df9ff7d7f)

### Using data stored in a table <a href="#example-queries" id="example-queries"></a>

#### 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 <a href="#example-queries" id="example-queries"></a>

Only show records that contain a string

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

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

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

Join two tables together:

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

[More examples and information about SELECT queries](https://neon.com/postgresql/postgresql-tutorial/postgresql-select).

#### 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 <a href="#altering" id="altering"></a>

### Alter Tables <a href="#altering" id="altering"></a>

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 <a href="#removing" id="removing"></a>

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:

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

#### Change a column type <a href="#changing-column-type" id="changing-column-type"></a>

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:

```sql
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](https://neon.com/postgresql/postgresql-tutorial/postgresql-alter-table).

#### Delete a table <a href="#deleting" id="deleting"></a>

To delete a table, use the following query:

```sql
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 <a href="#column-types" id="column-types"></a>

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 <a href="#using-credentials" id="using-credentials"></a>

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

* Business Intelligence Clients
  * [Metabase](https://www.metabase.com/): 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](https://www.tableau.com): a powerful analytics platform for creating interactive visualizations and dashboards, enabling users to explore and share data insights with ease.
* SQL Clients
  * [Beekeeper Studio](https://www.beekeeperstudio.io/): an open-source SQL editor and database manager offering a clean interface for querying, editing, and managing databases efficiently.
  * [TablePlus](https://tableplus.com/): 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](https://www.postgresql.org/docs/current/ecpg-sql-connect.html).
* The Record Created and Record Updated triggers utilize [polling](https://docs.getmesa.com/workflow-builder/triggers#polling) to detect changes.