Connect your database

In this tutorial you connect a Postgres database, such as one on Neon or Supabase, write a SQL query with a parameter, and feed that parameter from a search box so the list filters as the visitor types. You need a Postgres database you can reach from the internet and a user with read access.

Prepare a table

1. Create sample data

   If you do not have a table to show yet, open the SQL editor of your database provider and run:

   sql

   create table products (id serial primary key, name text not null, price numeric(8, 2) not null);
   insert into products (name, price) values
     ('Canvas tote', 24.00), ('Ceramic mug', 14.50), ('Linen apron', 32.00),
     ('Notebook', 9.90), ('Oak coaster set', 18.00), ('Wool blanket', 89.00);
   

2. Find the connection details

   In your provider's dashboard, open the connection details of the database (in Neon and Supabase, press Connect on the project). Copy the host, port, database name, user and password. They are the parts of a connection string like postgresql://user:password@host:5432/database.

Connect the database

1. Add a PostgreSQL integration

   Open the Data workspace from the bottom of the left sidebar. Next to Integrations, press + and choose PostgreSQL. Name it Shop database.

2. Fill in the connection

   In the Connection card, enter the Host (for example ep-cool-name.us-east-2.aws.neon.tech), the Port (usually 5432), the Database and the User. Keep Ssl on require, or pick verify-full to also check the server certificate.

3. Add the password

   In the Secrets card, enter the Password of the database user. It is encrypted on the server and never shown again.

4. Create and test

   Press Create integration, then Run test in the Test connection card. Connected means Malla reached the database and ran a simple query. An error shows the message and a code, such as a wrong password or a host that does not answer.

Query the table

1. Look at the tables

   Next to Queries, press + and rename the query to products. In Source, set Integration to Shop database and Operation to List tables, then press Run in the Result card. You see every table the user can read, with its columns.

2. Write the SQL

   Set Operation to Query. In Sql, write:

   sql

   select id, name, price from products
   where name ilike '%' || $1 || '%'
   order by name
   

   $1 is a placeholder. Its value comes from Params, never from the SQL text, so a visitor cannot inject SQL through it.

3. Give the parameter a value

   Under Params, press Add item and type "mug", with the quotes: each item takes a JSON value, so text needs quotes and numbers do not. Max rows returns 200 rows when you leave it empty, and at most 1000.

4. Return the rows

   The query returns an object with rows, rowCount, columns and truncated. In When it runs, set Response path to rows so the query returns only the list. Press Run: the result is one product, the ceramic mug.

Add a live search

1. Create a search variable

   Next to Page variables, press + and choose String. Rename it to search and leave the default value empty.

2. Feed the parameter from the variable

   Select the products query. Next to the "mug" item under Params, press Use an expression (the ƒ button) and replace the value with vars.search.

   In When it runs, pick Client. The query now runs in the browser on load and again whenever vars.search changes. Debounce (ms) waits until the visitor stops typing; the default is 250 ms. The browser asks Malla to run the query, so the password never leaves the server.

3. Add the search box

   Back on the canvas, press + in the Layers tab and choose Input. In the Field section, set Placeholder to Search products and set Value to search. Typing in the box now writes to the variable.

4. List the products

   Insert a Collection list under the search box. In Collection list, set Source to Expression and Items to queries.products.data. Add two texts inside Item and bind them to item.name and item.price in Typography. Set Empty text to No products match your search.

1. Try the search

   Press ⌘↵ to preview the page and type oa in the search box. After a short pause the list shows only the coaster set. Type xyz to see the empty text. Press ⌘↵ again to go back to editing.

2. Publish

   Press Publish and open the staging page. The search works the same way for your visitors.

What you learned

• A PostgreSQL integration stores the host, database and user, and keeps the password encrypted on the server.
• $1, $2 placeholders take their values from Params, and a parameter can read a variable.
• A client query refetches when the variables it reads change, which turns an input into a live search.

Read the guides behind this tutorial: