DBCode - Docs - Relationships

Discover How to Access Related Data Across Linked Tables

Open a Table

Begin by selecting a database connection from the DB Explorer.
Choose the desired database, then select a table by clicking it.

Navigate to and select a table with relationships

Identify Linked Relationships

To understand the relationships between tables, hover over a cell containing a Primary Key or Foreign Key. A small relationship icon will appear beside the cell, indicating that the column is linked to another table.

Hover over cells to see relationship indicators

Browsing Foreign Key Relationships

Foreign key relationships allow you to navigate data across multiple tables in both directions:

From Foreign Key to Primary Key: When you click on the relationship icon next to a foreign key, you can view the related records in the primary key table. For example, clicking on a CustomerID in an Orders table will show you all orders related to that specific customer in the Customers table.
From Primary Key to Foreign Key: Conversely, while in the related table (e.g., Customers), you can explore how many orders each customer has by following the foreign key back to the Orders table.

To see the linked data:

Hover over the relationship icon to view the message displaying the filtering query for the related table.
Click the relationship icon to instantly filter and display data from the related table, as shown below.

View filtered data from related tables with a single click

This two-way navigation of foreign key relationships enhances your ability to analyze data comprehensively, allowing for deeper insights and better data integrity.

Inferred Relationships

Some databases like MongoDB, DynamoDB, Cassandra, and others don’t have native foreign key support. DBCode can automatically detect relationships based on column naming conventions, enabling the same relationship browsing experience.

How It Works

Inferred relationships use pattern matching to detect columns that reference other tables. For example:

A column named user_id in an orders table likely references the id column in a users table
A column named customerId references id in a customers table

Configuring Inferred Relationships

You can configure inferred relationship patterns in two ways:

1. Connection Settings

Open your connection settings (right-click connection → Edit Connection)
Navigate to the Inferred Relationships section
Enable the patterns you want to use:
- Suffix patterns: Match columns ending with _id, Id, _fk, etc.
- Custom patterns: Define your own regex patterns for specific naming conventions
Set the Target column (default: id) - the column name to reference in target tables

2. Entity Relationship Diagram (ERD)

For visual configuration:

Open the ERD diagram for your database (right-click tables → Open Diagram)
Click the Inferred Relationships tool in the toolbar
Toggle patterns on/off and see relationships update in real-time
Click Save to persist your configuration to the connection

Predefined Patterns

DBCode includes several predefined patterns:

Pattern	Description	Example
`column_id` → `column.id`	Underscore suffix	`user_id` → `users.id`
`columnId` → `column.id`	CamelCase suffix	`userId` → `users.id`
`column_fk` → `column.id`	Foreign key suffix	`user_fk` → `users.id`
`fk_column` → `column.id`	Foreign key prefix	`fk_user` → `users.id`

Custom Patterns

For databases with unique naming conventions, you can create custom regex patterns:

Column Pattern: Regex to match the source column name (use capture group for table name)
Table Pattern: Template for the target table name (use $1 for captured group)
Target Column: The column to reference in the target table

Example: To match ref_users_key → users.pk:

Column Pattern: ref_(.+)_key
Table Pattern: $1
Target Column: pk

Explicit Mappings

For relationships that don’t follow any naming pattern, you can define explicit column-to-column mappings. This is useful when:

Column names don’t match any pattern (e.g., creator → users.id)
You want to override pattern-detected relationships

To add an explicit mapping:

In Connection Settings or ERD tool panel, find the Explicit Mappings section
Click Add Mapping
Enter the source and target in the format table.column or schema.table.column:
- From: The source column (e.g., orders.creator)
- To: The target column (e.g., users.id)

Examples:

From	To	Description
`orders.creator`	`users.id`	Non-standard column name
`logs.entity_ref`	`products._id`	Generic reference column
`audit.modified_by`	`auth.users.id`	Cross-schema relationship

MongoDB Example

For MongoDB collections with document references:

// orders collection
{
  _id: ObjectId("..."),
  user_id: ObjectId("..."),  // References users._id
  items: [...]
}

// users collection
{
  _id: ObjectId("..."),
  name: "John"
}

Configure an inferred relationship pattern:

Enable the column_id → column.id pattern
Set Target column to _id (MongoDB’s default primary key)

Now when viewing orders, you’ll see the relationship icon on user_id cells, allowing you to browse to the related user document.

Relationships