Using Molnify's Database

Introduction

There are many reasons to save user data in a Molnify application. Dynamic product lists, usage logging, issue statuses, and sales data, are just a few example cases where saving to the database is key.

By connecting your application with the database, you can store, modify and retrieve data without updating your application.

Before you start

Before you start using Molnify's Database functionalities in your application, you must ensure that you have a static (non-changing) application ID. You have this if your application has the ID metadata set up. If you do not have the ID set up, you will lose access to all your table data whenever you update your application.

Once you have the ID metadata included in your application, then you are ready to unleash the power of Molnify's database!

The Data Manager

The Data Manager is used to configure your database connection, and you can access it from My Applications, or from your application's sidebar menu. The data manager is accessible only to the application's uploader and its superusers.

The Data Manager consists of two tools: the table management interface and the database query tester.

Table management

The table management interface lets you create and edit tables for your application. You can also use it to edit to the data stored in your tables.

Creating a table

To create a table, click on the "Create New Table" button. A new table will be automatically created and assigned to your application. The new table will be given a name of the form data_<your-application-id>_ followed by a number, and this name cannot be changed.

The actions that add or modify data to your tables require the columns _molnify_user_ip, _molnify_user_email, _molnify_application_id and _molnify_timestamp to exist in the table. So, they are included automatically whenever you create a new table.

Click on the name of a table which you have created to bring up its details in the right portion of the window.

Table data

Once you have opened up a table in the detailed view, you can inspect its data in a searchable view. Note that if you just created your database table, then it will not contain any data initially.

In addition to reading the data, you can also insert new rows or delete selected rows. You can select a row or several by clicking on the checkbox in the rightmost column on its row. Click on an entry in order to edit it - any changes will be saved to the database as soon as you deselect the entry.

Table statistics

The shown statistics are, unlike the other views on this page, not editable.

Each table has a counter associated with it, the Auto Increment. If your table has a column which is the primary key with AUTO_INCREMENT enabled, then the value of this counter is the next new value for that column.

Table columns

You can use this table for adding, editing and removing columns in the same way as you use the data table above it.

A table's columns defines the structure of its data. For example, if there is a column named variable, then each row of data may have a value for variable. Column names are ideally descriptive, and we recommend that you avoid names that do not describe their function. For example, we consider the name max_acceleration to be easier to work with than the abbreviated ma.

If a table's columns define its data, then a column's data type defines the data in the column. The data type is a label that instructs the database on how it shall interpret the 1s and 0s that constitute the data, how much memory it should reserve for the data, and more.

Common data types and their uses

Data type	Use case
INT UNSIGNED	Non-negative whole numbers
INT	Whole numbers that may be negative, recordId
BOOL	Values that are either TRUE/FALSE. The database converts BOOL columns to TINYINT, where 0 is FALSE and all other values are TRUE
FLOAT	Decimal numbers
TIMESTAMP	Date and time of an audit event, or other logging
VARCHAR	Text strings, up to 255 characters
TEXT	Text strings longer than a VARCHAR

The actions that insert data into the database require the columns _molnify_user_ip (VARCHAR), _molnify_user_email (VARCHAR), _molnify_application_id (VARCHAR) and _molnify_timestamp (TIMESTAMP). They are included in newly created tables by default.

You can set the default value of a column as well. Then, any row of data that is inserted into the table and does not have a value specified for that column will be assigned the default value. For example, you can use a BOOL column is_deleted with a default value of 0 to initialise data as "active", and then use an add row to database action to set is_deleted to 1 when the user wishes to remove an entry. This is a technique for soft-removing data, as no application can actually remove data from the database.

Table indexes

An index is an instruction for the database to prepare the stored data for lookup, and how. For example, if your application stores a list of cars, then an index on the column "brand" may make the query SELECT model FROM `data_<your-application-id>_0` WHERE brand = "VOLVO"; perform better, at the expense of very slightly slowing down insertion of data. For most tables, the performance benefit is too small, but it can be a critical optimization for tables with thousands of rows of data.

While it is technically not an index, you can define a "primary key" for your database table by creating an index with the name PRIMARY. A primary key is a constraint on the data, and all rows of the table must have a unique value for the primary key column(s). We recommend that you define a primary key for your table, as it has the following benefits:

• A primary key ensures that your data is sound, and internally consistent. For example, it can prevent you from accidentally having two price definitions for the same product ID
• A primary key allows you to update rows of data with the add row to database action

Query tester

Sometimes data does not load into your application as you expect, or you need to experiment with different queries to figure out how to retrieve the data your application needs. You can use the query tester to try out and validate your queries before including them in your Molnify application.

Note that the query tester is used for testing queries, and not a mix of Excel functions and queries. For example, the following query would result in an error, as it is an Excel formula that attempts to read a value from a spreadsheet cell: ="SELECT value, recordId FROM `data_<your-application-id>_0` WHERE _molnify_user_email = '"&B2&"';". You will need to replace any cell references in your query with data before using it in the tester.

Common error messages

Error	Explanation
General troubleshooting tip for complex queries	Simplify your query, and verify that each individual component of the query works. Then, slowly assemble it back together, and see when it breaks. That is where your issue most likely is.
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ..."	The database does not know how to interpret the query. For example, a table name which includes dashes (-) must be enclosed in backticks (`), or there might be a comma (,) after the last item in the SELECT. But, the database does not give you more detail than on which line it failed. So you can split your query into multiple lines, to narrow down on the issue.
Unknown column 'column' in 'field list'	You are attempting to use a column that does not exist. Check that you have spelled the column name correctly, and that the column exists in the table.
User 'you@email.com' and application 'application-id' are not allowed to execute query:	For security reasons, you are only allowed to execute READ-ONLY queries, that only affect the columns in the tables which your application has access to. All of those tables are listed in the Table manager. Contact us if you are not permitted to execute a query which you believe you should be allowed to execute.

Autofill

Molnify's Autofill functionality is a powerful tool for using data from the database in your application. It allows you to specify a range of cells, and a query to retrieve data from the database. Molnify will then populate the cells with the data from the query, allowing you to use the data in Excel formulas and in outputs.

Query cookbook

Here follows some example queries to get you started. More complex queries than these are supported, as well.

Common operations when fetching data

Query	Description
SELECT * FROM `data_<your-application-id>_0`	Select all data in all rows from the table
SELECT `colour`, `value` FROM `data_<your-application-id>_0`	Select only specific columns from the table
SELECT * FROM `data_<your-application-id>_0` WHERE `colour` = 'red'	Select only rows from a table where a column matches a condition
SELECT * FROM `data_<your-application-id>_0` WHERE `colour` = 'red' AND `value` > 5	Select only rows from a table based on multiple conditions
SELECT * FROM `data_<your-application-id>_0` WHERE `colour` = 'red' ORDER BY `_molnify_timestamp` DESC	Order the result based on the values in a column
SELECT * FROM `data_<your-application-id>_0` WHERE `colour` = 'red' ORDER BY `_molnify_timestamp` DESC LIMIT 10	Fetch the 10 most recent rows of data that match the condition
SELECT COUNT(*) FROM `data_<your-application-id>_0` WHERE `value` < 5	Count the number of rows that match the condition
SELECT SUM(`value`) FROM `data_<your-application-id>_0` WHERE YEAR(`_molnify_timestamp`) = 2024	Count the number of rows that were created during a particular year
SELECT DISTINCT(`customer`) FROM `data_<your-application-id>_0`	List all different customers that have data in the table

Actions

You can use the following actions in order for your application to add new data or update the data in your database table.

Add record to database

A record is a snapshot of your application's variables. Unlike a scenario, which captures all inputs, a record captures all variables which have corresponding column names in the application's records table, and stores them in a new row.

You must use the RecordTableName metadata to specify the name of the table that will contain the records for your application.

The database table for storing records must also be set up as follows:

1. The columns _molnify_user_ip, _molnify_user_email, _molnify_application_id and _molnify_timestamp must exist in the table.
2. There must also be a column named recordId, of a data type that can store whole numbers, such as INT. This name is, like all column names in the database, case-sensitive.
3. The table must have a primary key (see indexes), and the primary key must consist of only the recordId column.
4. The recordId column must also be set to auto increment.

Example:

Once you have your database table set up, and metadata naming the table as your records table, you are ready to include the add record to database action in your application.

When a user activates this action, the current values of its variables are stored into a new row of your application's records table. But, if your application has a variable named recordId, and it has a value that is not 0 or empty when the user activates the action, then the existing record corresponding to that ID will instead be updated. After a record has been created, the recordId variable will be updated to the recordId of the newly created row. This way, any subsequent saved records by the same user will update the previously saved record - until the webpage is refreshed or the application is reset.

If your application also has a variable named recordId, and you load the application with it set using a URL parameter, then the data from the corresponding record will be loaded into the application.

The add record to database action does not offer many customization options, but it also does not require extensive configuration.

Option	Value	Description
type	addRecord	This must be set to instruct Molnify to create an add record to database action. This option is required
title	Any string	This sets the text displayed on the generated action button
name	Any string	This sets an internal name for the action, and is used to integrate the action with e.g. the multiple action and JavaScript functions
hidden	TRUE or FALSE	Set this to TRUE if you want to hide the generated action button, and instead activate the action with JavaScript, using e.g. custom buttons

Add row to database

You can use the add row to database action for a configurable method of storing and updating database data. The action allows you to define which values should be stored in which columns and allows for a primary key consisting of several columns when updating data.

The columns _molnify_user_ip, _molnify_user_email, _molnify_application_id and _molnify_timestamp are required to exist in the table. Values for these columns are created automatically by the action.

Option	Value	Description
type	insertRow	This must be set to instruct Molnify to create an add row to database action. This option is required
tableName	data_<your-application-id>	This must be set to instruct Molnify which table to insert the data into. This option is required
.col	Any value	Replace "col" in the option name with the name of the database column to insert data into. A dot (.) must prefix the column name. The action requires at least one ".col" option
k.keycol	Any value	If a database column is part of the primary key, replace "keycol" in the option name with the name of the column to insert data into. A "k" and a dot (k.) must prefix the column name.
title	Any string	This sets the text displayed on the generated action button
name	Any string	This sets an internal name for the action, and is used to integrate the action with e.g. the multiple action and JavaScript functions
hidden	TRUE or FALSE	Set this to TRUE if you want to hide the generated action button, and instead activate the action with JavaScript, using e.g. custom buttons

Download Custom Data

The download custom data action can be used to allow the users of your application to download the result of a query. The downloaded file can be in either .csv or .json format.

Download Database Table

An entire database table can be downloaded through the download database table action. Unlike the download custom data action, this action does not allow choosing file format, delimiter, or query.

This action is equivalent to the download custom data action with the query SELECT * FROM data_<your-application-id>_0.

Table access and application ID

The list of tables that your database may use is determined by its ID. As such, if you need to change your application's ID, you should either delete all your database tables for the application or contact us for help to migrate your data to your new application ID.

Example: a logging tool

This example will walk you through how to build a logging tool in Molnify.

We pretend that we have a set of locations, each of which should be inspected regularly. An inspection should determine if a location is in "Good" condition, "Poor" condition (but serviceable), or if action is needed.

First things first: we set up our metadata with an ID for the application, a name, a description, a link to this guide, and a template for some quick styling. We place this in the columns A-B in our sheet. In general, we recommend that you structure your workbook so that you can find your metadata easily.

Now that our application has a static ID and some basic metadata, we can add some inputs to it.

First off, we need a list of names of our inspectors, and a list of warehouse locations. We place them in columns K and L, giving us some space to their left for inputs, outputs and actions. For brevity, we choose to have 5 inspectors and 10 locations, but the application could easily be extended to include more.

The states "Good", "Poor" and "Action needed" go into column M.

Now, we assign these to inputs. We create a named range "Names" for the names, a named range "Warehouses" for the warehouse locations, and a named range "States" for the states. By enabling data validation for the inputs, and typing "dropdown" in their UI cell, we instruct Molnify that the inputs should be rendered as dropdown lists with options defined by the data validation ranges.

We can now upload the app, and we have some working dropdowns. But, there is no way to save a log yet.

Setting up the database

In order to save the logs, we need to set up a database table. We do so in the Data manager.

In the Data manager, we click on the button to create a new table. It will be automatically assigned the name "data_<my-app-id>_0". After the table is created, we click on its name to open the configuration for our new table.

To store our log data, we create the following columns in the "Columns" table: recordId, name, warehouse, status. We cannot set recordId to auto increment unless we define it as the primary key first, so we do that by creating an index with the name PRIMARY. After we add the primary key, we set recordId to be auto-incremented, so new recordIds will be generated for new logs.

And that is all the database setup we will need. Now, we will use the add record to database action to add the ability to save logs to the application.

Saving logs

We add our newly created table as the application's records table in the metadata, and we create the action.

For Molnify to know which inputs to save in which database columns, we assign variable names to the inputs matching the column names. E.g. the input for name should have the ui-string variable=name;dropdown.

We also hide the default action button for the action. The default action button is visible on all input tabs, and we are limited in how we style it. So, we create a custom action button using an HTML Panel and place it among inputs.

The button element uses the onClick attribute to call the hidden action when clicked on. The classes btn btn-sm btn-success buttonMargin molnifyActionButton are for styling.

Now that we're able to save logs, it is time to visualise the results. We want to display two tables. The first table should show the ten most recently made logs. The second table will show an overview of our locations, their last reported state, who made that report, and when the report happened.

Table 1: most recent logs

First, we prepare a named range in the Excel file to read the most recent logs into. We will display the 10 most recent logs, so the range should be 10 rows. For each of the rows, we want to display when the log was made, who made it, for which warehouse, and which state was logged - that is a total of 4 columns. We name the cells O2:R11 "Recent_logs", and colour them grey - the grey has no meaning in Molnify but helps us remember that we have defined a named range there. We also write the column headers "timestamp", "name", warehouse", and "status" above the named range, to help us remember what data will be filled into which column.

Then, we create a table in the cells below the named range, N13:S23. In each cell of the table, we read the value of the corresponding cell in the named range, or leave the cell blank if there is no read value. If we do not include the check for empty values, then there could be rows displaying 0s if the result of the database query does not fill all rows.

So, we have set up the table to display data, but we need to supply the range Recent_logs with information from the database.

To do so, we must write a query. The syntax for this is the MySQL database query language. In other words, queries are generally structured to begin with a SELECT which specifies which columns to fetch, and a FROM which determines the database table to fetch from. The FROM can be followed by additional clauses, such as WHERE which filters the output, ORDER BY which determines how the output is sorted, and LIMIT N which only returns the first N rows. In this query, we will use the ORDER BY clause. Note that we do not need to use LIMIT N, since the named range is already limited in the number of rows.

SELECT
	`_molnify_timestamp`,
	name,
	warehouse,
	status
FROM
	`data_database-example-logging_0`
ORDER BY `_molnify_timestamp` DESC
                    

Now, the only thing that is left to do is to add the query as an autofill metadata.

Table 2: warehouse statuses

With a view of the most recent logs in place, it is time to show the results of the inspections.

Similarly to when we created the first table, we start by preparing the Excel file to receive the data for the second table. Since we have 10 warehouses, we create a named range that is 10 rows and 4 columns. We let this named range be called Warehouse_data, and define it to be the cells V2:Y11. We write the column headers "warehouse", "status", "name", "date" in V1:Y1 above the range. We also colour the range grey, and colour them grey, so we remember that the cells will be populated with data by Molnify.

Next, we create the Molnify table in the cells below the named range, U13:Z23. We refer each cell in the table to the corresponding cell in the named range above it.

Now that we have set up the table, and range to receive the data, it is time to construct the query which tells the database which data to find.

We will need to select the warehouse, status, name, and date of the single most recent report for each warehouse. We need to break down this query.

To select the most recent report for each warehouse, we use the function MAX(_molnify_timestamp), and GROUP BY warehouse to aggregate by warehouse. This sub-query becomes:

                SELECT
			warehouse,
			MAX(_molnify_timestamp) AS max_timestamp
		FROM
			`data_database-example-logging_0`
		GROUP BY
			warehouse
                    

It is unlikely that two reports are logged at the exact same second, but we should not ignore the possibility. Since the recordId is the primary key, we are guaranteed that it will be unique, so it is a perfect tie-breaker. So, for each of the warehouses present in the result of the earlier query, we want to determine the biggest recordId. We perform this operation with a JOIN and a GROUP BY.

        SELECT
		MAX(a.recordId) AS recordId,
		a.warehouse
	FROM
		`data_database-example-logging_0` AS a
	JOIN (
		SELECT
			warehouse,
			MAX(_molnify_timestamp) AS max_timestamp
		FROM
			`data_database-example-logging_0`
		GROUP BY
			warehouse
   	) AS b
   	ON
		a.warehouse = b.warehouse
		AND
                a.`_molnify_timestamp` = b.max_timestamp
	GROUP BY
		a.warehouse
                    

Now that we have the recordId of the most recent log for each warehouse location, we can use a second JOIN to find the status, name, and log date for the rows that match the recordIds. The final query becomes:

SELECT
	t.warehouse,
	t.status,
	t.name,
	DATE(t._molnify_timestamp)
FROM
	`data_database-example-logging_0` AS t
JOIN (
	SELECT
		MAX(a.recordId) AS recordId,
		a.warehouse
	FROM
		`data_database-example-logging_0` AS a
	JOIN (
		SELECT
			warehouse,
			MAX(_molnify_timestamp) AS max_timestamp
		FROM
			`data_database-example-logging_0`
		GROUP BY
			warehouse
   	) AS b
   	ON
		a.warehouse = b.warehouse
		AND
	        a.`_molnify_timestamp` = b.max_timestamp
	GROUP BY
		a.warehouse
) AS s
ON
	t.recordId = s.recordId
                    

Now, the only thing that is left to do is to add the query as an autofill metadata.

You can download the final Excel file for the logging tool, but remember that the application will not work unless you change the ID and set up the database tables for the new ID.

The demo app can be tried out below, or at https://app.molnify.com/app/database-example-logging.