# Databases

Databases allow you to create custom structured databases to store and organize your information. Each table can have multiple columns of different types, with built-in validation, permissions, and integration with your IoT devices.

Databases are integrated with Blynk.Console and mobile applications.

### Supported Column Types

| Column Type          | Description                           | Best Used For                                      |
| -------------------- | ------------------------------------- | -------------------------------------------------- |
| **Text**             | Store words, sentences, or paragraphs | Names, descriptions, notes, addresses              |
| **Integer**          | Whole numbers only                    | Counts, quantities, IDs, ages                      |
| **Double**           | Numbers with decimals                 | Measurements, prices, percentages, sensor readings |
| **Date**             | Calendar dates                        | Deadlines, birthdays, creation dates, schedules    |
| **Enum**             | Predefined list of options            | Status values, categories, priority levels         |
| **Device Reference** | Link to your IoT devices              | Associate records with specific devices            |
| **Table Reference**  | Link to other data tables             | Connect related information across tables          |
| **Formula**          | Calculated values                     | Automatic calculations based on other columns      |
| **List**             | Multiple device references            | When one record relates to several devices         |
| **Aggregation**      | Summary calculations                  | Totals, averages, counts from related data         |

### System-Managed Columns

{% hint style="info" %}
These columns are automatically maintained by the system and cannot be manually edited.
{% endhint %}

| Column Type      | What It Stores                     |
| ---------------- | ---------------------------------- |
| **Created At**   | When the record was first created  |
| **Modified At**  | When the record was last updated   |
| **Created By**   | Who created the record             |
| **Organization** | Which organization owns the record |

### Column Configuration

#### Text Columns

Configure text-based data with validation and formatting options.

| Setting                | Description                  | Example                                      |
| ---------------------- | ---------------------------- | -------------------------------------------- |
| **Minimum Characters** | Shortest allowed text length | Require at least 3 characters for names      |
| **Maximum Characters** | Longest allowed text length  | Limit descriptions to 500 characters         |
| **Default Value**      | Text automatically filled in | "New Item" or "{timestamp}" for current date |
| **Placeholder**        | Helpful hint text            | "Enter customer name here..."                |
| **Required**           | Must be filled in            | ✓ Make name field mandatory                  |

{% hint style="info" %}
Use `{timestamp}` in default values to automatically insert the current date and time.
{% endhint %}

#### Number Columns (Integer & Double)

Configure numeric data with precision and unit support.

| Setting            | Description                    | Example                             |
| ------------------ | ------------------------------ | ----------------------------------- |
| **Minimum Value**  | Smallest allowed number        | Don't allow negative quantities     |
| **Maximum Value**  | Largest allowed number         | Limit temperature readings to 100°C |
| **Default Value**  | Number automatically filled in | Start quantity at 0                 |
| **Unit**           | Measurement unit display       | °C, kg, meters, $                   |
| **Decimal Format** | How many decimal places        | Show 2 decimals for prices          |
| **Required**       | Must be filled in              | ✓ Require price entry               |

#### Date Columns

Configure date fields with range restrictions.

| Setting         | Description           | Example                          |
| --------------- | --------------------- | -------------------------------- |
| **Start Limit** | Earliest allowed date | No dates before January 1, 2020  |
| **End Limit**   | Latest allowed date   | No future dates beyond next year |
| **Required**    | Must be filled in     | ✓ Require delivery date          |

#### Enum (Dropdown) Columns

Create predefined lists of options for consistent data entry.

| Setting               | Description                 | Example                             |
| --------------------- | --------------------------- | ----------------------------------- |
| **Options List**      | Available choices           | Low/Medium/High or Red/Yellow/Green |
| **Default Selection** | Automatically chosen option | Default to "Medium" priority        |
| **Required**          | Must select an option       | ✓ Require status selection          |

#### Device Reference Columns

Link records to your IoT devices for better data organization.

| Setting               | Description                       | Example                                  |
| --------------------- | --------------------------------- | ---------------------------------------- |
| **Device Template**   | Filter to specific device types   | Only show temperature sensors            |
| **Deletion Behavior** | What happens if device is deleted | Remove reference or delete entire record |
| **Required**          | Must select a device              | ✓ Require device assignment              |

{% hint style="warning" %}
When a referenced device is deleted, you can choose to either remove the reference (set to null) or delete the entire record. Choose carefully based on your data integrity needs.
{% endhint %}

#### Table Reference Columns

Create relationships between different data tables.

| Setting               | Description                              | Example                               |
| --------------------- | ---------------------------------------- | ------------------------------------- |
| **Target Table**      | Which table to link to                   | Link to "Customers" table             |
| **Deletion Behavior** | What happens if linked record is deleted | Remove link or delete this record too |
| **Required**          | Must select a linked record              | ✓ Require customer selection          |

#### Formula Columns

Create calculated fields that automatically compute values based on other columns.

| Setting                | Description             | Example                                     |
| ---------------------- | ----------------------- | ------------------------------------------- |
| **Formula Expression** | Calculation to perform  | `price * quantity` or `(temp_f - 32) * 5/9` |
| **Unit**               | Display unit for result | $, %, °C                                    |
| **Decimal Format**     | Result precision        | Show 2 decimal places                       |

{% hint style="info" %}
Formula columns are read-only and automatically recalculate when referenced columns change.
{% endhint %}

#### List Columns

Store multiple references in a single field.

<table><thead><tr><th>Setting</th><th width="246">Description</th><th>Example</th></tr></thead><tbody><tr><td><strong>Maximum Items</strong></td><td>How many items allowed</td><td>Allow up to 5 devices per record</td></tr><tr><td><strong>Item Type</strong></td><td>What can be added to list</td><td>Only device references currently supported</td></tr></tbody></table>

#### Aggregation Columns

Create summary calculations across related data.

| Setting              | Description            | Example                       |
| -------------------- | ---------------------- | ----------------------------- |
| **Source Table**     | Where to get data from | "Orders" table                |
| **Linking Column**   | How tables connect     | Match customer ID             |
| **Value Column**     | What to calculate      | Sum the "amount" column       |
| **Calculation Type** | Type of summary        | Sum, Average, Count, Min, Max |
| **Unit**             | Display unit           | $ for monetary totals         |

**Available Aggregation Types**

* **Sum**: Add up all values
* **Average**: Calculate the mean value
* **Count**: Count the number of records
* **Minimum**: Find the smallest value
* **Maximum**: Find the largest value

### Table Configuration

#### General Settings

Configure the basic properties of your data table.

| Setting            | Description                                            |
| ------------------ | ------------------------------------------------------ |
| **Table Name**     | Internal system name (no spaces or special characters) |
| **Display Name**   | Friendly name shown to users in the interface          |
| **Singular Name**  | What to call one record ("Customer" not "Customers")   |
| **Display Column** | Which column to show as the main identifier            |
| **Hide on Mobile** | Whether to show this table on mobile devices           |
| **Icon**           | Visual icon URL for the table                          |

#### Data Sharing Settings

Control how data is shared within your organization structure.

| Setting                          | Description                              |
| -------------------------------- | ---------------------------------------- |
| **Share with Sub-Organizations** | Allow child organizations to see records |
| **Allow Row Creation**           | Let users add new records to the table   |

#### Access Control

Configure who can view and edit data at different organizational levels.

**Permission Levels**

| Permission Level           | Description                                            |
| -------------------------- | ------------------------------------------------------ |
| **Root Organization View** | Roles that can see records in the main organization    |
| **Root Organization Edit** | Roles that can modify records in the main organization |
| **Sub Organization View**  | Roles that can see records in child organizations      |
| **Sub Organization Edit**  | Roles that can modify records in child organizations   |

{% hint style="warning" %}
Be careful when setting permissions. Overly restrictive permissions can prevent users from accessing needed data, while overly permissive permissions can compromise data security.
{% endhint %}

### Best Practices

#### Getting Started

{% hint style="info" %}
**Start Simple**: Begin with basic Text and Number columns, then add advanced features like formulas and aggregations as your needs grow.
{% endhint %}

#### Naming Conventions

* **Use Meaningful Names**: Choose clear, descriptive column names that explain what the data represents
* **Be Consistent**: Use the same naming style throughout your tables
* **Avoid Special Characters**: Stick to letters, numbers, and underscores for system names

#### Data Validation

* **Set Appropriate Limits**: Use min/max values and character limits to prevent data entry errors
* **Use Required Fields Wisely**: Only mark fields as required if they're truly essential
* **Provide Good Defaults**: Set sensible default values to speed up data entry

#### Relationships and References

* **Plan Relationships**: Think about how your tables connect before creating references
* **Choose Deletion Behavior Carefully**: Consider what should happen when referenced data is deleted
* **Use Device References**: Link records to devices to take advantage of IoT integration

#### Security and Access

* **Test Permissions**: Verify that the right people can access and edit your data
* **Review Regularly**: Periodically review access permissions as your team changes
* **Use Role-Based Access**: Assign permissions to roles rather than individual users

#### Performance Considerations

* **Limit Formula Complexity**: Keep formula expressions simple for better performance
* **Be Mindful of Aggregations**: Complex aggregations on large datasets may impact performance
* **Consider Mobile Users**: Use the "Hide on Mobile" setting for complex tables not suitable for small screens

{% hint style="success" %}
For additional support, consult your account manager
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.blynk.io/en/add-ons/databases.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.