Modify a database

Writing back to a database allows users of an app to directly interact with the data. For example, if an app is collecting user-inputted data, or editing existing data, these changes will immediately be reflected in your database.

Database direct nodes connect directly to your database, so you'll be able to write SQL statements in your nodes to modify the database. This can include:

Creating new tables

Inserting new records

Updating existing records

Deleting records

The following examples are created in PostgreSQL data nodes.

Creating new tables

You can create new tables in your database using a CREATE TABLE statement.

Specify the name of the new table after the CREATE TABLE keywords.

Within the parentheses, specify a comma-separated list of table columns. Each column contains:

• Column name

• Data type that the column stores

• Length of the data

• Column constraint

Column constraints specify rules that the column must follow. For example, NOT NULL specifies that values in the column cannot be NULL. Other column constraints include UNIQUE, PRIMARY KEY, CHECK, and FOREIGN KEY CONSTRAINTS.

When you click , the node will run and a new table will be created in your database. The node only needs to be run once. Since this is a create table query, there will be nothing in the output preview table.

Query:

CREATE TABLE new_table (
	customer_id SERIAL PRIMARY KEY,
	first_name VARCHAR(255) NOT NULL,
	last_name VARCHAR(255)  NOT NULL,
 	email VARCHAR(255)  NOT NULL,
 	uploaded_time TIMESTAMP
);

This example creates a new table called new_table with 5 columns: customer_id, first_name, last_name, email, and uploaded time.

• customer_id: This column is a SERIAL datatype. It will automatically be assigned a sequential integer when a new record is added. This column is designed to work as the primary key of the table.

• first_name, last_name, email: These columns are strings with a length of 255 characters. They also cannot contain NULL values.

• uploaded_time: This column stores a timestamp showing when a new record is added.

Inserting new records

Data node setup

New records can be inserted into an existing table with an INSERT statement.

Specify the name of the table you want to insert records into after the INSERT INTO keywords. Then list the columns you'd like to add values to.

After the VALUES keyword, list the values to add. Ensure that the columns and the column values are in the same order.

To insert new records based on user input, those values should be parameterized. See Query for detail on adding parameters.

Query:

INSERT INTO new_table (first_name, last_name, email, uploaded_time)
VALUES ('{{first_name}}', '{{last_name}}', '{{email}}', current_timestamp);

This example will insert new records based on user inputs for the columns first_name, last_name, and email. It will also insert the current time into the uploaded_time column to indicate when the record was created. Since this is a Insert query, nothing will come out in the output preview table.

User input setup

User inputs are passed in through a Form element. See Use Custom Form Container to collect user inputs for more on how to build a form to collect data and how to set up the necessary interactions.

Once the form and form elements have been created in your interface, first set up an interaction on the form to set SQL parameters in your node.

Remember to create another interaction to submit the form.

When the button is clicked, the form will be submitted and run the statement in your node to insert a record with the values the user has inputted.

Updating existing records

Data node setup

An UPDATE statement changes the values of columns in all rows that satisfy a condition.

Then, in a data node, set up a statement to change the email for the customer_id of the row being edited.

Paramaterize the email and customer_id values so users can input an email for any customer.

Query:

UPDATE new_table
SET email = '{{email}}'
WHERE customer_id = {{customer_id}};

This example changes the email address for the given customer_id.

After the UPDATE keyword, choose the table you'd like to edit.

Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values. To modify multiple columns, separate them with a comma.

In the WHERE clause, identify which rows will be modified with the new value. In this case, customer_id is a primary key, so only one row will be modified at a time. However, you can change the condition to match multiple rows to bulk edit data.

User input setup

Let's say an app has a table to displaying data from new_table. Each row has a button that will open up a modal containing a form to change the customer's email.

See Create a table for table setup with a column of buttons.

See Modal for how to create and trigger the modal popup. Make sure the modal is dragged into the table element, so the modal is able to access table values.

See Use Custom Form Container to collect user inputs to learn how to create a form inside the modal to collect a new email address.

• Customer ID: In a text element, use ${$row.customer_id} to access the customer id for the clicked row. Since the modal is created inside of the table, it will be able to access table values.

• Email: Use an Input element and name the form item email. This will pass in the user input to the form.

Once the form and data node are ready, set up the interactions to submit the form and update the record based on the parameter values for email and customer_id.

For the interaction on the form to set SQL parameters for the data node, pass in ${$row.customer_id} and ${event.form.customer_id} as the action parameters. This will run the node with the parameter values and update the record.

Remember to add an action on the submit button to submit the form.

Deleting records

Data node setup

Use a DELETE statement to delete rows that satisfy the WHERE condition.

Similarly, the value can be parameterized.

Query:

DELETE FROM new_table
WHERE customer_id = {{customer_id}};

In this example, we delete a row from new_table for a given customer_id.

User input setup

Let's say an app contains a table displaying data from new_table and each row in the table has a delete button for that row.

See Create a table for table setup with a column of buttons.

When a user clicks on the delete button, the record will be deleted from the database.

On the button, add an interaction to set the SQL parameter of the data node to the customer_id of that row. Use ${$row.customer_id} to access the value.

When the button is clicked, the parameter value will be sent to the node and the node will run the DELETE statement to remove that record.