Data by MESA

The Data by MESA app lets you store data directly in MESA. You can trigger workflows when the data is updated and view the data from the Settings page. You can even connect directly to your integrate with business intelligence services.

🗒 Note: Are you interested in analyzing your data directly in MESA with custom advanced queries and charts? Contact us to sign up for the beta of our new Insights feature.

• Configuration
• Browse the Data
  • Example Queries
• Altering Tables
  • Remove a column
  • Change a column type
  • Delete a table
  • Column types
• Connect to your data

Configuration

To create a new table, add a Data Create Record step to your workflow.  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 in the column's Value field. It is recommended to label your columns with no spaces included. 

Once you save your workflow, to avoid affecting workflows that may use the same table, you will no longer be able to delete the column or adjust your column's Name or Type from the user interface. If you need to adjust your columns, you can run one of the queries in the Altering Tables section below.

Browse the Data

Go to the Settings page under your account name. 

Then, click on the Table name or 🔍 icon to query and browse the data in the table:

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.

Alter Tables

You can remove columns or change their type by running an ALTER query. Go to the Settings Page and click on the ⚙ cog icon next to the table in question. 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}} to 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 list 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, it may be easier to remove the column and re-add it from the workflow builder with the new desired column type.

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 by MESA 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 by Mesa database. You can use these credentials to a desktop querying client, business intelligence tool, or read and write data directly from your custom application.

• Metabase: Business Intelligence tool. Analyze your data, generate charts and gather insights all without writing a single query. Just add your Database by MESA connection info and you're off and running. Free and open source to self-host with a free trial available for the hosted version.
• Beekeeper Studio: SQL Editor and Database Manager. Useful for running queries, exporting data to CSV spreadsheets, and reading and writing data directly from your desktop. Free and open source.
• Learn more on connecting to PostgreSQL databases.