Database Relationships

Xano makes it easy to visualize and select relationships of your database tables.

Basic Relationships

In Xano, a relationship between database tables is created using the Field type Table reference

Relationship data display A table reference is typically just an integer referencing the unique ID number from the table of the referenced record. However, oftentimes it's helpful to visualize what the reference represents beyond just the ID. With Xano, you can see the fields of your referenced record that are important to your use case.

In this example, we are viewing the deals table. Each deal has a relationship to a merchant record, as signified by the merchant_id field. Each field is displaying data from the merchant table including the name, description, and id of each merchant record.

Visualize and Auto-complete Database Relationships

Auto-complete relationships When entering a reference record, Xano presents you with a drop-down menu. The menu provides you with the recently added records from the table you are referencing. Additionally, there is a search bar where you can search for the id number or any of the displayed field values. You can quickly navigate using the keyboard arrows and enter key.

In this example, the current value is Firestone BBQ #1. There is an auto-generated list of the most recently added records from the merchant table. Additionally, we can search for any of the displayed fields and the record id #.

Choosing which data to display By default, Xano will automatically display the first text field and the id field of the referenced table record. However, you can easily customize which data you'd like to be displayed in your table reference.

In the parent table (or the table being referenced), click the menu icon in the top right (three dots), then select Auto-Complete. In this example, the parent table or table being referenced would be the merchant table.

In this example, select Auto-Complete in the merchant table to customize which data is to be displayed in other tables referencing the merchant table.

After selecting Auto-Complete, the settings will open up. Click customize in order to customize the display of the referenced data.

Once the Auto-Complete settings open, click Customize to customize the layout of the display.

You can add multiple columns, reorder the columns displayed, and remove columns from the display.

In this example, the displayed columns are name, description, and id - in that order.

Many-to-Many relationships

There are a few ways to handle Many-to-Many Database relationships within Xano.

▶️ This is also covered in Part 2 of our Database series

An example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students.

The traditional SQL to set this up has been to use a JOIN or pivot table which you can absolutely still do in Xano:

However, Xano allows you to do it another way Xano uses PostgreSQL, which is essentially a hybrid SQL/NoSQL database. You can simply create a list (array) of classes in the student table that allows you to not only achieve the same thing but have data locality (being able to see everything in one place).

Here's a demonstration of how you would add a class list directly inside the student table:

Add a field from the student table

Select a table reference

Select class (or the item that will have the many relationship)

Select the column header for class_id and select Change Type from the drop-down menu.

Change 'Structure' from Single to List.

Double-click into the field to be able to add to the list of classes.

See how the enrollments are displayed directly on the student

FAQ: When should I put things in an object or list vs a separate table

There is no hard and fast rule on this, but we like to say if you anticipate your object list growing over 100, it's probably time to start thinking about separating it into a new table. You can still keep everything in an object, but it becomes a little unwieldy to manage especially because when you update objects, you're updating the whole thing (so potentially 100+ records) and if you make a mistake on how you update the data on the front-end, it could lead to some trouble.