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:
The following examples are created in PostgreSQL data nodes.
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.
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.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.
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 Data Nodes for more on 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.User inputs are passed in through a Form element. See Create a form 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.
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.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 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 Create a form 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.
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.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.
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.
Last modified 2mo ago