# Writing device data to a database
{% note warning %}
Yandex IoT Core is no longer available to new users.
Current users can create resources until November 1, 2026. Afterwards, the service will go read-only and cease to operate on December 1, 2026. For more information on the timing and procedure, see [Service shutdown](../sunset.md).
{% endnote %}
In this tutorial, you will learn how to write device data to a database. In our example, we will use a [Yandex Managed Service for PostgreSQL cluster](../../managed-postgresql/concepts/index.md). To connect your device to Yandex IoT Core, you will need an MQTT broker. All the following steps are performed in the [management console](https://console.yandex.cloud).
To start writing information from your device to the database:
1. [Get your cloud ready](#before-you-begin).
1. [Create a service account](#create-sa).
1. [Create the required Yandex IoT Core](#resources) resources.
1. [Create a registry](#registry).
1. [Create a device](#device).
1. [Connect your device to the MQTT broker](#connect).
1. [Prepare the database](#db).
1. [Create a cluster](#cluster).
1. [Connect to the cluster](#connect-to-cluster).
1. [Create a table](#table).
1. [Create a function for processing data](#func).
1. [Create a trigger for Yandex IoT Core](#trigger).
If you no longer need the resources you created, [delete them](#clear-out).
## Get your cloud ready {#before-you-begin}
Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.
If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).
[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).
### Required paid resources {#paid-resources}
* Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for PostgreSQL pricing](../../managed-postgresql/pricing.md)).
* Yandex IoT Core registry: Number of sent messages (see [Yandex IoT Core pricing](../pricing.md)).
* Yandex Cloud Functions functions: number of function calls, idle time of provisioned instances, and computing resources allocated for the function (see [Yandex Cloud Functions pricing](../../functions/pricing.md)).
## Create a service account {#create-sa}
{% list tabs group=instructions %}
- Management console {#console}
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a service account.
1. Navigate to **Identity and Access Management**.
1. Click **Create service account**.
1. Name the service account: `my-db-function-service-account`.
1. Click  **Add role** and select the `functions.functionInvoker` and `editor` [roles](../../iam/concepts/access-control/roles.md).
1. Click **Create**.
{% endlist %}
## Create the required Yandex IoT Core resources {#resources}
{% note info %}
This tutorial assumes [username and password-based authorization](../concepts/authorization.md#log-pass), so you do not need to add any certificate to your registry and device. In your projects, you can use [authorization using certificates](../concepts/authorization.md#certs).
{% endnote %}
### Create a registry {#registry}
{% list tabs group=instructions %}
- Management console {#console}
1. In [the management console](https://console.yandex.cloud), select the folder where you want to create a registry.
1. Navigate to **IoT Core**.
1. Click **Create registry**.
1. In the **Name** field, enter `my-registry`.
1. Enter a password.
* The password must be at least 14 characters long.
* The password must contain uppercase and lowercase letters and numbers.
{% note warning %}
Save your password as you will not be able to read it from Yandex IoT Core.
{% endnote %}
1. Click **Create**.
{% endlist %}
### Create a device {#device}
{% list tabs group=instructions %}
- Management console {#console}
Once a registry is created, you will see its settings.
1. Navigate to the **Devices** tab.
1. Click **Add device**.
1. In the **Name** field, enter `my-device`.
1. Enter a password.
* The password must be at least 14 characters long.
* The password must contain uppercase and lowercase letters and numbers.
{% note warning %}
Save your password as you will not be able to read it from Yandex IoT Core.
{% endnote %}
1. Click **Create**.
{% endlist %}
## Connect your device to the MQTT broker {#connect}
To connect to the MQTT server, use the following parameters:
- [A certificate from the certificate authority](https://storage.yandexcloud.net/mqtt/rootCA.crt).
- **Server address**: `mqtt.cloud.yandex.net`.
- **Server port**: `8883`.
- **Protocol**: `TLSv1.2`.
## Prepare the database {#db}
### Create a cluster {#cluster}
{% list tabs group=instructions %}
- Management console {#console}
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create your database cluster.
1. Navigate to **Managed Service for PostgreSQL**.
1. Click **Create cluster**.
1. In the **Cluster name** field, enter `my-pg-database`.
1. In the **Environment** field, select `PRODUCTION`.
1. In the **Version** field, select `17`.
1. Under **Host class**:
* Specify the `Intel Cascade Lake` platform.
* Select the `burstable` type.
* Specify the `b2.medium` class.
{% note warning %}
The `b2.medium` class was selected for testing only. In real projects, we do not recommend using hosts with a guaranteed vCPU share less than 100%.
{% endnote %}
1. Under **Storage size**:
* Select `network-ssd`.
* Set the size to 10 GB.
{% note info %}
Select the type and size of the disk to fit your purpose. The above values are used for testing.
{% endnote %}
1. Under **Database**, specify the DB attributes:
* Database name. It must be unique within the folder and contain only Latin letters, numbers, and underscores.
* DB owner username. It may only contain Latin letters, numbers, and underscores.
* User password. It must be from 8 to 128 characters long.
For the database created with the cluster, the character set and collate settings are specified as `LC_CTYPE=C` and `LC_COLLATE=C`. You cannot change these settings after the database is created, but you can [create a new database](../../managed-postgresql/operations/databases.md#add-db) with the appropriate settings.
1. Under **Hosts**, click  and enable the **Public access** option.
1. Under **Advanced settings**, enable **Access from the management console**.
1. Click **Create cluster**.
{% endlist %}
For more information about creating a cluster, see [How to create a PostgreSQL cluster](../../managed-postgresql/operations/cluster-create.md#create-cluster).
Afterwards, the cluster settings can be [updated](../../managed-postgresql/operations/update.md).
### Connect to the cluster {#connect-to-cluster}
After creating the cluster, you will be automatically redirected to the **Clusters** page.
Wait for the cluster status to change to `Alive`.
{% list tabs group=instructions %}
- Management console {#console}
1. Select the `my-pg-database` cluster.
1. Navigate to the **SQL** tab.
1. In the **Database username** field, specify the name of the user who owns the DB created in the previous step.
1. In the **Password** field, enter the password specified when creating the cluster.
1. Click **Connect**.
{% endlist %}
### Create a table {#table}
As a sample data source, the scenario uses an air sensor that measures the following parameters:
* Humidity
* Carbon dioxide (CO2) level
* Pressure
* Temperature
The sensor outputs the result in JSON format. Here is an example:
```json
{
"DeviceId":"0e3ce1d0-1504-4325-972f-55c9********",
"TimeStamp":"2020-05-21T22:53:16Z",
"Values":[
{"Type":"Float","Name":"Humidity","Value":"25.281837"},
{"Type":"Float","Name":"CarbonDioxide","Value":"67.96608"},
{"Type":"Float","Name":"Pressure","Value":"110.7021"},
{"Type":"Float","Name":"Temperature","Value":"127.708824"}
]
}
```
Write the received information to a DB table using a function.
Once you [connect to the cluster](#connect-to-cluster), create a table. Follow these steps:
{% list tabs group=instructions %}
- Management console {#console}
1. In the edit window, enter the following request:
{% note warning %}
The query below is given as an example. If your device sends different information, change the columns in your new table.
{% endnote %}
```sql
CREATE TABLE iot_events (
event_id varchar(24) not null,
device_id varchar(50) not null,
event_datetime timestamptz not null,
humidity float8 null,
carbon_dioxide float8 null,
pressure float8 null,
temperature float8 null
)
```
1. Click **Execute**.
Wait until a message confirms that the request is complete.
{% endlist %}
## Create a function for processing data {#func}
The function will receive messages from the MQTT broker and write data to the table created in the previous step.
{% list tabs group=instructions %}
- Management console {#console}
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a function.
1. Navigate to **Cloud Functions**.
1. Click **Create function**.
1. Enter a function name.
1. Click **Create**.
{% endlist %}
### Create a function version {#func-version}
After creating the function, you will be automatically redirected to the **Editor** page.
{% list tabs group=instructions %}
- Management console {#console}
1. Under **Function code**:
* In the **Runtime environment** field, select `python37`.
* In the **Code source** field, keep the default value, **Code editor**.
1. Create a file named `myfunction.py`.
1. In the file editing area, add the function code from [GitHub](https://github.com/yandex-cloud-examples/yc-iot-postgresql/blob/main/myfunction.py).
{% note info %}
The query used to write data to the DB is generated in the `makeInsertStatement` method. If you need to remove or add parameters, make changes to this method.
{% endnote %}
1. In the **Entry point** field, specify `myfunction.msgHandler`.
1. Specify the following version parameters:
* **Timeout**: `10`
* **Memory**: `128 MB`
1. In the **Service account** field, select `my-db-function-service-account`.
1. Add these environment variables:
* `VERBOSE_LOG`: Parameter displaying detailed information about the function. Type in `True`.
* `DB_HOSTNAME`: Name of the PostgreSQL database host to connect to.
* `DB_PORT`: Port for the connection.
* `DB_NAME`: Name of the database to connect to.
* `DB_USER`: Username for the connection.
* `DB_PASSWORD`: Password you entered when [creating your cluster](#cluster).
To define the values of connection parameters:
1. In the [management console](https://console.yandex.cloud), select the folder where you created the cluster.
1. Navigate to **Managed Service for PostgreSQL**.
1. Select the `my-pg-database` cluster.
1. In the line with the database you need, click  and select **Connect**.
1. On the **PowerShell** tab, find a sample connection string.
1. Move the values of the `host`, `port`, `dbname`, and `user` variables to the appropriate **Value** field for the function environment variables.
1. Click **Save changes**.
{% endlist %}
## Create a trigger for Yandex IoT Core {#trigger}
The trigger will accept copies of messages from the device topic and pass them to the function for processing.
{% list tabs group=instructions %}
- Management console {#console}
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a trigger.
1. Navigate to **Cloud Functions**.
1. In the left-hand panel, select  **Triggers**.
1. Click **Create trigger**.
1. Under **Basic settings**:
* In the **Name** field, enter a name for the trigger.
* In the **Description** field, enter a description for the trigger.
* In the **Type** field, select **IoT Core (device)**.
1. Under **IoT Core message settings**:
* In the **Registry** field, enter `my-registry`.
* In the **Device** field, enter `my-device`.
* In the **MQTT topic** field, specify the topic that the device sends data to:
```text
$devices//events
```
Where `` is the ID of your device.
The trigger will fire as soon as new data appears in the specified topic.
1. Under **Function settings**:
* Select the data processing function that you created earlier.
* In the **Function version tag** field, specify `$latest`.
* In the **Service account** field, specify `my-db-function-service-account`.
1. Leave the other fields empty or fill them out at your discretion.
1. Click **Create trigger**.
{% endlist %}
## How to delete the resources you created {#clear-out}
To shut down the infrastructure and stop paying for the resources you created:
1. [Delete](../operations/device/device-delete.md) the device.
1. [Delete](../operations/registry/registry-delete.md) the registry.
1. [Delete](../../managed-postgresql/operations/cluster-delete.md) the PostgreSQL cluster.
1. [Delete the trigger](../../functions/operations/trigger/trigger-delete.md).
1. [Delete](../../functions/operations/function/function-delete.md) the function.