DEV Community: Daniil Bazhenov

Securing Your Data in PostgreSQL Using PG_TDE for Encryption – Beginner-Friendly Guide

Daniil Bazhenov — Thu, 04 Sep 2025 13:29:51 +0000

Let’s explore how to encrypt data in PostgreSQL — not just at the application level, but directly within the database engine itself. Transparent Data Encryption (TDE) allows you to protect sensitive information by encrypting it at the storage layer, without changing how your application interacts with the database.

In this walkthrough, we’ll use PG_TDE, an open-source extension that brings TDE capabilities to PostgreSQL. It encrypts table data, indexes, TOAST storage, temporary tables — and now even WAL (Write-Ahead Logging), meaning changes are protected from the moment they’re written to disk.

The key advantage of TDE is that it works transparently: no changes are needed in your application code or SQL queries. PostgreSQL continues to operate as usual, while encryption and decryption happen behind the scenes. This is especially valuable in scenarios where an attacker gains access to the underlying storage — without TDE, raw database files can be read directly; with TDE, they’re encrypted and unreadable without the proper keys.

We’ll set up PostgreSQL with PG_TDE in Docker, configure encryption keys, create encrypted tables, and inspect how the data is stored. While PG_TDE supports multiple key management options, we’ll start with a simple keyring file provider — ideal for local testing and experimentation.

Note: The keyring file is intended for development only. In production, you should use a secure external key store such as HashiCorp Vault or KMIP.

Installing PostgreSQL with PG_TDE via Docker

PG_TDE is part of the Percona Distribution for PostgreSQL — an enhanced PostgreSQL distribution that includes tools for monitoring, auditing, replication, and of course, Transparent Data Encryption. We’ll use the official Docker image, which already contains everything needed to run PG_TDE.

Let’s start by launching the container:

docker run --name pg-tde \
  -e POSTGRES_PASSWORD=secret \
  -e ENABLE_PG_TDE=1 \
  -p 5432:5432 \
  -d percona/percona-distribution-postgresql:17.5-3

Here:

POSTGRES_PASSWORD=secret — sets the superuser password
ENABLE_PG_TDE=1 — enables Transparent Data Encryption support
Port 5432 — standard for PostgreSQL

After launching, connect to PostgreSQL inside the container:

docker exec -it pg-tde psql

And activate the PG_TDE extension:

CREATE EXTENSION pg_tde;

Verify that PG_TDE is enabled and check its version using the SQL function:

SELECT pg_tde_version();

Here’s the result at the time of writing — I’m using PG_TDE 2.0

I also connected to PostgreSQL using pgAdmin, accessing via localhost, user postgres, and the password from POSTGRES_PASSWORD.

pgAdmin is very convenient for exploring the database via UI.

Now TDE is ready — we can create encrypted tables, manage keys, and verify how data is protected at the storage level.

Configuring Encryption Keys with PG_TDE

After installing and activating the pg_tde extension, the next step is to configure the keys that will be used for data encryption. In this example, we’re using a key provider based on a keyring file — a simple and fast way to start working with PG_TDE in development mode.

Important: The keyring file stores keys in unencrypted form and is intended for testing and development only. For production, it’s recommended to use an external key store such as HashiCorp Vault — we’ll cover that in the next post.

Add the key provider:

SELECT pg_tde_add_database_key_provider_file(
    'file-vault',
    '/tmp/pg_tde_local_keyring.per'
);

This command:

Registers a key provider named 'file-vault'
Specifies that keys will be stored in the file located at /tmp/pg_tde_local_keyring.per
The file path can be anything — you can use a different location or filename, as long as PostgreSQL has access to it. If the file doesn’t exist, it will be created when the first key is generated

It’s important not to lose this file — it contains the encryption keys. If needed, it can be placed in a local folder and mounted into the container via Docker, but for this task, using a path inside the container is perfectly fine.

Create the key:

SELECT pg_tde_create_key_using_database_key_provider(
    'test-db-key', 
    'file-vault'
);

We’re creating a key named test-db-key inside the specified key provider. This key will be used to encrypt tables.

Set the active key:

SELECT pg_tde_set_key_using_database_key_provider(
  'test-db-key',
  'file-vault'
);

This command sets test-db-key as the current active key, which will be used when creating new encrypted tables.

Let’s verify.

Retrieve key information using the command:

SELECT pg_tde_key_info();

Result:

postgres=# SELECT pg_tde_key_info();
                      pg_tde_key_info
------------------------------------------------------------
 (test-db-key,file-vault,1,"2025-09-04 07:16:04.420629+00")
(1 row)

Check the contents of the keyring file — just for experimentation.

In a separate terminal tab, connect to the container:

docker exec -it pg-tde bash

Use hexdump to read the file since it contains binary data:

hexdump -C /tmp/pg_tde_local_keyring.per | head

This will output the first bytes of the file in hex format, allowing you to confirm that the file is not empty and the key is indeed recorded.

If the file is empty or unchanged — the key may not have been created or written.

Creating Tables with Data

Now that the encryption key is set, let’s create two tables: one with encryption enabled, and one regular. This will help visually demonstrate how PG_TDE works and how data storage differs.

Create the encrypted table by specifying USING tde_heap:

CREATE TABLE secure_data (
  id SERIAL PRIMARY KEY,
  secret TEXT,
  created_at DATE
) USING tde_heap;

Add a few rows:

INSERT INTO secure_data (secret, created_at) VALUES
  ('The launch code is hidden in plain sight.', '2025-09-01'),
  ('Trust no one. The truth is encrypted.', '2025-09-02'),
  ('The treasure lies beneath the third stone by the old oak.', '2025-09-03');

Create a similar table without encryption:

CREATE TABLE plain_data (
  id SERIAL PRIMARY KEY,
  secret TEXT,
  created_at DATE
);

Insert the same data:

INSERT INTO plain_data (secret, created_at) VALUES
  ('The launch code is hidden in plain sight.', '2025-09-01'),
  ('Trust no one. The truth is encrypted.', '2025-09-02'),
  ('The treasure lies beneath the third stone by the old oak.', '2025-09-03');

Now both tables contain identical rows, but one stores the data in encrypted form.

Let’s check the encryption status using PG_TDE.

Use the built-in function pg_tde_is_encrypted, which returns true (t) if the table uses encryption, and false (f) if not:

postgres=# SELECT pg_tde_is_encrypted('secure_data');
 pg_tde_is_encrypted
---------------------
 t
(1 row)

postgres=# SELECT pg_tde_is_encrypted('plain_data');
 pg_tde_is_encrypted
---------------------
 f
(1 row)

Excellent, now we can move on to comparison: let’s see how the data looks inside PostgreSQL — and how it’s stored on disk. This will help confirm that PG_TDE truly protects content, even if someone gains direct access to the files.

Comparing Tables: Encrypted vs Unencrypted

At the SQL query level, both tables — secure_data (encrypted) and plain_data (unencrypted) — look absolutely identical. This is important: PG_TDE does not affect SQL interface behavior, and the application continues to work with the data as usual.

postgres=# SELECT * FROM secure_data;
 id |                          secret                           | created_at
----+-----------------------------------------------------------+------------
  1 | The launch code is hidden in plain sight.                 | 2025-09-01
  2 | Trust no one. The truth is encrypted.                     | 2025-09-02
  3 | The treasure lies beneath the third stone by the old oak. | 2025-09-03
(3 rows)

postgres=# SELECT * FROM plain_data;
 id |                          secret                           | created_at
----+-----------------------------------------------------------+------------
  1 | The launch code is hidden in plain sight.                 | 2025-09-01
  2 | Trust no one. The truth is encrypted.                     | 2025-09-02
  3 | The treasure lies beneath the third stone by the old oak. | 2025-09-03
(3 rows)

Display in the application using pgAdmin

Data from the plain_data table:

Data from the encrypted table secure_data:

The pgAdmin screenshots also show no differences: data is displayed in readable form, regardless of whether the table is encrypted. This confirms that PG_TDE operates at the storage level, without affecting data handling logic.

As you can see, the result is identical: encryption is transparent to the user and application.

Comparing Table File Contents

Now let’s verify that PG_TDE truly encrypts data at the storage level. To do this, we’ll locate the physical table files and compare their contents using hexdump.

In PostgreSQL, each table is physically stored as one or more files in the base directory, inside the folder corresponding to the database. The file path looks like $PGDATA/base/<database_oid>/<relfilenode>

database_oid — unique database identifier (OID), retrieved from pg_database
relfilenode — table file identifier, retrieved from pg_class

Step 1: Retrieve Identifiers

Get the OID of the current database:

postgres=# SELECT oid, datname FROM pg_database WHERE datname = current_database();
 oid | datname
-----+----------
   5 | postgres
(1 row)

Get the relfilenode of the tables:

postgres=# SELECT relname, relfilenode FROM pg_class WHERE relname IN ('secure_data', 'plain_data');
   relname   | relfilenode
-------------+-------------
 secure_data |       16433
 plain_data  |       16442
(2 rows)

Now, knowing oid = 5 and the relfilenode of the tables, we can locate the table files at:

data/db/base/5/16433 — encrypted table secure_data
data/db/base/5/16442 — unencrypted table plain_data

Step 2: Read Table Files

Open a terminal and connect to the PostgreSQL container for bash commands:

docker exec -it pg-tde bash

Read data from the unencrypted table (plain_data):

hexdump -C data/db/base/5/16442 | head -n 20

The output shows that the data is readable directly from the file:

Read data from the encrypted table (secure_data):

hexdump -C data/db/base/5/16433 | head -n 20

The output shows that the contents are fully encrypted:

No readable strings — PG_TDE reliably encrypts data at the file system level.

Conclusion

In this post, we installed PostgreSQL with Transparent Data Encryption (PG_TDE) by Percona, created keys, encrypted a table, and verified that the data is truly protected during physical storage. PG_TDE by Percona provides transparent encryption: the application continues to work with the data as usual, while security is enforced at the file system level.

If you're working with PostgreSQL and looking to strengthen your data protection, PG_TDE offers a great starting point. I invite you to experiment:

Create encrypted tables and perform a backup. Try restoring it on a different installation and see how key access is enforced.
Explore key rotation, switching the active key, or integrating alternative key providers.
Test how PG_TDE behaves in real-world scenarios: migration, failover, CI/CD pipelines

Thanks for reading — and if you experiment with PG_TDE, I’d love to hear what you discover.

Monitoring, troubleshooting, and query analytics for PostgreSQL on Kubernetes

Daniil Bazhenov — Sun, 30 Jun 2024 17:31:07 +0000

If you are learning about databases and Kubernetes or running or migrating PostgreSQL to Kubernetes, I would like to show you a great open-source tool for database monitoring and troubleshooting.

I will discuss a tool to help you better understand your database, its parameters, and its health. You can access a Query Analytics tool to help you find slow queries. In addition, you will have dashboards to monitor the Kubernetes cluster itself.

In the previous article, I discussed the pgAdmin and PostgreSQL cluster created using Percona Everest. Today, I installed Percona Monitoring and Management (PMM) in my cluster, made some test queries to the database using pgAdmin, and explored the dashboards.

PMM is a free, open-source database monitoring tool for MySQL, PostgreSQL, and MongoDB. PMM has configured Grafana dashboards to monitor various PostgreSQL metrics:

Connections, Tuples, Transactions
Checkpoints, Buffers, and WAL usage
Blocks, Conflicts and Locks
Disk Cache and Memory Size
CPU, RAM, Disk IO
Vacuum monitoring and more

You need to install it (I'll show it below)

PMM Server, which includes dashboards and collects metrics from your databases.
PMM Client for each of your databases that sends database metrics to PMM Server. You need to configure pg_stat_monitor or pg_stat_statements extensions for PostgreSQL. If you use Percona Operator for PostgreSQL or Percona Everest, PMM is already integrated and enabled in the settings.

Let's get to the installation.

Installing PMM in a Kubernetes cluster.

You can install the PMM server on any server or cluster; I use the same cluster where the database is installed.

The documentation offers many installation methods, such as using Docker, Podman, AWS, or HELM.

I used the installation with HELM and the instructions from the official documentation.

Create a separate namespace or use an existing one. I create a separate one



kubectl create namespace monitoring

I have installed HELM and the Percona repositories as per the documentation, and now install PMM using the commands:



helm repo add percona https://percona.github.io/percona-helm-charts/



helm install pmm -n monitoring \
--set service.type="ClusterIP" \
--set pmmResources.limits.memory="4Gi" \
--set pmmResources.limits.cpu="2" \
percona/pmm

I added parameters with resource limits for PMM since my test cluster has limited resources.

The installation is quick, and I have the next steps.

We need to get the administrator password created during installation. (I just took that command from the last step.)



kubectl get secret pmm-secret -n monitoring -o jsonpath='{.data.PMM_ADMIN_PASSWORD}' | base64 --decode

Let's do a port-forward for Pod with pmm to an available port on our laptop to open PMM in the browser. (I used 8081 because 8080 is used for Percona Everest, which manages the database.)



kubectl -n monitoring port-forward pmm-0 8081:80

Opened PMM in a browser and used the password to log in.

Connecting the database to the PMM server

Now that we have the PMM itself, we need to make our Postgres database pass metrics to it. I created the cluster using Percona Everest; however, you can connect any PostgreSQL cluster to PMM.

If you are not using Percona's Postgres, please refer to the documentation on installing the PMM Client and Postgres extensions (pg_stat_statements or pg_stat_monitor).
If you are using Percona Distribution for PostgreSQL, Percona Operator for PostgreSQL, or Percona Everest, then the necessary extensions are already installed. I will explain how to enable monitoring below.

Postgres database created using Percona Operator for PostgreSQL

The setup process is described in sufficient detail in the documentation, if briefly:

You need to create an API key in the PMM settings.
Specify the API key as the PMM_SERVER_KEY value in the deploy/secrets.yaml secrets file. Using the deploy/secrets.yaml file, create the Secrets object.
Update the pmm section in the deploy/cr.yaml file.



  pmm:
    enabled: true
    image: percona/pmm-client:2.42.0
    secret: cluster1-pmm-secret
    serverHost: monitoring-service

Apply the changes, and you will see the databases in PMM.

PostgreSQL cluster created using Percona Everest

That's my way.

We need to get the IP address of the PMM in the Kubernetes cluster.



kubectl get svc -n monitoring

Now, in the Percona Everest settings, let's add a new Monitoring Endpoint using the IP address, user, and password from PMM.

Let's edit the database and enable monitoring in the created endpoint.

Done; now we will see the metrics in PMM.

Testing how it works.

Open pgAdmin and make some complex queries.

I found a SQL query that generates random data in rows.



INSERT INTO demo.LIBRARY(id, name, short_description, author,
                              description,content, last_updated, created)
SELECT id, 'name', md5(random()::text), 'name2'
      ,md5(random()::text),md5(random()::text)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,1000) id;

And made several million rows by changing the value of generate_series(1,1000)

I've also done various SELECT queries.

After that, I went to look at the dashboards, which immediately showed that I had a problem. I got a list of slow queries and a spike in the graph. I created a test table without indexes, and queries were already processing slowly on many rows. I did this purposely to see the result in the monitoring tool.

I also found a dashboard that shows the cluster resource utilization for each Pod, such as CPU and RAM.

Conclusion

PMM has various dashboards for monitoring PostgreSQL. I won't show you all of them, but I recommend installing and monitoring your database, especially if you are not using database monitoring tools.

Running pgAdmin to Manage a PostgreSQL Cluster on Kubernetes

Daniil Bazhenov — Wed, 26 Jun 2024 18:14:16 +0000

Let’s say you need to do something in PostgreSQL in Kubernetes, and it is inconvenient to work with the database in the terminal, or you need to become more familiar with PostgreSQL or SQL commands. In that case, this article comes in handy.

I explain how to run pgAdmin in a Kubernetes cluster to manage PostgreSQL databases deployed in that cluster. This is useful if your PostgreSQL cluster does not have external access and is only available inside a Kubernetes cluster.

pgAdmin is a popular open source tool for managing Postgres databases. It is convenient for creating databases, schemas, tables, viewing data, executing SQL queries, and much more in a user-friendly web interface.

Running pgAdmin

We need one file that contains deployment and service resources to run pgAdmin in k8s. Let's call it pgadmin.yaml.

pgadmin.yaml



apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgadmin
  template:
    metadata:
      labels:
        app: pgadmin
    spec:
      containers:
        - name: pgadmin
          image: dpage/pgadmin4
          ports:
            - containerPort: 80
          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: admin@example.com
            - name: PGADMIN_DEFAULT_PASSWORD
              value: admin
---
apiVersion: v1
kind: Service
metadata:
  name: pgadmin-service
spec:
  selector:
    app: pgadmin
  ports:
    - protocol: TCP
      port: 80
      targetPort: 80

Note that the middle of the file contains the login and password for logging into the pgAdmin panel.



          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: admin@example.com
            - name: PGADMIN_DEFAULT_PASSWORD
              value: admin

We need to deploy pgAdmin using this file by running the command.

kubectl apply -f pgadmin.yaml -n <namespace>

Let's print out the list of pods in our cluster to get the name pgadmin pod.

kubectl get pods -n <namespace>

Now, we just need to run port forwarding for the pgAdmin pod on a free port that will open in the browser. I use port 5050.

kubectl port-forward pgadmin-deployment-***-*** 5050:80 -n <namespace>

I can open localhost:5050 in a browser now.

After that, you only need to add a connection to Postgres by clicking Add New Server.

I used the access data that Percona Everest provided me for the Postgres cluster created using it.

Congratulations! We can manage Postgres using the pgAdmin interface or SQL queries. Viewing databases, schemas, and tables and writing SQL queries has become very convenient.

Remove pgAdmin

Once you are done with pgAdmin and PostgreSQL, all you need to do is to

Stop port-forwarding by simply pressing CTRL + C in the terminal.
Remove pgAdmin Deployment and Service by running the command.

About the PostgreSQL cluster on Kubernetes used in the article

In this post, I used the Postgres cluster with three nodes created on Google Kubernetes Engine (GKE) using Percona Everest.

Percona Everest is an open source platform that allows you to provision and manage database clusters. I recently wrote about Percona Everest in A New Way to Provision Databases on Kubernetes. You do not need to worry about PostgreSQL cluster installation and configuration, backups, recovery, or scaling; you can do it in the interface. It enables multi-database and multi-cluster configurations and can be deployed on any Kubernetes infrastructure in the cloud or on-premises.

Documentation: Create Kubernetes cluster on Google Kubernetes Engine (GKE)

You can use any other cloud or create a local Kubernetes cluster using minikube, k3d or kind.

A New Way to Provision Databases on Kubernetes

Daniil Bazhenov — Wed, 13 Mar 2024 07:59:13 +0000

Need to provide database self-service to internal teams? Can't spare the resources to build and maintain your own private DBaaS solution? Sick of cloud DBaaS providers locking you in?

Percona Everest is a cloud-native database platform to deploy and manage enterprise-grade PostgreSQL, MongoDB and MySQL database clusters.

With Percona Everest, you can:

Enable DBA teams to regain control over data and database configuration
Empower developers to deploy code faster and self-service provision highly performant, production-ready database clusters
Free your entire organization from vendor lock-in and restrictive subscriptions

Benefits of Percona Everest

Truly open source, Percona Everest offers the benefits of automated database provisioning and management without the need to build or maintain an in-house database management platform.

No data lock-in or restrictive contracts
Reduced total cost of ownership (TCO)
Complete data sovereignty to meet any compliance requirement
Fully customizable database configurations
Right-sized database deployments
Frictionless developer self-service
Backing of highly skilled database management experts

Key features of Percona Everest

Database management via a single pane of glass
Percona Everest enables you to manage complex database environments through a single pane of glass or API.

Create, configure, deploy, update, and upgrade with zero downtime
Backup, restore, restart, suspend/resume, or delete database clusters

Complete customization
Customize load balancing settings, settings for network exposure, and resource settings like node count, instance type, CPU, memory, storage, etc.

Standardized database deployments
Create ready-to-use database clusters of enterprise-grade MySQL, PostgreSQL, and MongoDB, with the ability to customize to support a variety of topologies or deployments.

Percona Everest leverages Percona Operators to deploy Cloud-Native Percona Distributions.

Built-in observability tools
Benefit from built-in open source infrastructure monitoring to keep control of (and optimize) resource usage, health, and performance of database clusters.

How Percona Everest is designed

Percona Everest has two primary components

Percona Everest application with the UI

On the frontend, there is a Percona Everest UI which is developed using the Vite framework, React library and TypeScript language.
On the backend, requests from the frontend app are processed by Percona Everest Backend. It is an API developed in Golang using the Echo framework that sends requests to the Kubernetes API. You can also use the API directly for your needs.

Percona Everest CLI (everestctl)
You can install Percona Everest operators and components to your Kubernetes Cluster with the help of a console tool Percona Everest CLI (everestctl). Then, Percona Everest will create and manage database clusters. Everestctl is developed in Golang, and it is a built-in executable file.

Percona Everest does not supply a Kubernetes cluster; you’ll need to use your own for the deployment.

Percona Everest is currently in Beta

We invite you to become an early adopter and contribute to progress!

Your feedback is crucial to enhancing the software, and we highly value and rely on your input.

How to develop serverless PHP application with PostgreSQL database with Vercel and Neon.tech for free

Daniil Bazhenov — Mon, 20 Nov 2023 15:25:39 +0000

In this post, I will describe my experience with building a serverless PHP application for free. I also used PostgreSQL, PHP Composer, Vercel, and Neon.tech. This information might be helpful for students, novice developers, and even experienced developers trying to develop a serverless application.

Why I think this topic is important. Usually, to develop a PHP application, you need some kind of server; you need to set up a web server, connect a domain, set up an SSL certificate, and configure and maintain all of that. Serverless saves a lot of time and energy.

I investigated methods to develop and deploy a PHP application and PostgreSQL database for free.

I had requirements:

It is free, and no credit card is required.
Sufficient resources for the application, several gigabytes of data, and thousands of application runs per month.
HTTPS and the ability to connect my own domain.
Easy start for an untrained user (me).
PostgreSQL, PHP, and composer support

So, I found a pretty popular platform called Vercel, which could deploy PHP applications, too. I'd be glad if you could suggest a similar alternative in the comments.

With the database, there was more choice; as a database, I wanted to use PostgreSQL or compatible with it, and as a result, I found several services providing it for free:

CockroachDB - 10 GiB storage
Xata.io - 15 GB storage
Aiven.io - 5 GB storage
Neon.tech - 3 GiB storage per branch & 10 branches.

I only chose for a short time, deciding to try all services, and the first would be Neon.tech. I also found Neon.tech in the Vercel marketplace app list, which put me in the mood for my first experiment.

So, I will tell you how to make an application, deploy it to Vercel, and connect Neon.tech PostgreSQL.

Preparation

I signed up for the Vercel service at the Hobby plan, did my first project and installed the Vercel CLI.

I registered at Neon.tech and created my first free database.

Application development and deployment

Vercel community provides two great resources on how to make a PHP application with tons of examples:

We need to create a vercel.json file in the project folder. I just made an empty folder for the project.



{
  "functions": {
    "api/*.php": {
      "runtime": "vercel-php@0.6.0"
    }
  },
  "routes": [
    { "src": "/(.*)",  "dest": "/api/index.php" }
  ]
}

Create a simple php file index.php for the test in a new folder named api



<?php
phpinfo();

Make the application deployment with the command.

vercel --prod

The first time you will need to log in and do a dev deployment.



# Log in
vercel login

# Let's fly
vercel

That's it, a few seconds, and you can open a deployment domain or a production domain.

Let's connect the database

On the dashboard of our database in Neon we can get the connection data, this looks like a string:



psql 'postgresql://daniil.bazhenov:************@ep-shrill-disk-******.eu-central-1.aws.neon.tech/php-neon?sslmode=require'

I suggest immediately adding the access data as environment variables in the vercel.json file.

I added a section env, but you can also add variables directly in the code or vercel project page.



{
  "functions": {
    "api/*.php": {
      "runtime": "vercel-php@0.6.0"
    }
  },
  "routes": [
    { "src": "/(.*)",  "dest": "/api/index.php" }
  ],
  "env": {
    "PG_HOST": "ep-shrill-disk-******.eu-central-1.aws.neon.tech",
    "PG_PORT": "5432",
    "PG_DB": "php-neon",
    "PG_USER": "daniil.bazhenov",
    "PG_PASSWORD": "**********",
    "PG_ENDPOINT": "ep-shrill-disk-******"
  }
}

Now, let's modify the application to connect to PostgreSQL using our data from environment variables.

Let's open the php file api.index.php and modify it as follows:

Get the environment variables
Make a connection string for pg_connect
Check the connection

The example is courtesy of ChatGPT; you can use any other method.



<?php

$host = $_ENV['PG_HOST'];
$port = $_ENV['PG_PORT'];
$db = $_ENV['PG_DB'];
$user = $_ENV['PG_USER'];
$password = $_ENV['PG_PASSWORD'];
$endpoint = $_ENV['PG_ENDPOINT'];

$connection_string = "host=" . $host . " port=" . $port . " dbname=" . $db . " user=" . $user . " password=" . $password . " options='endpoint=" . $endpoint . "' sslmode=require";

$dbconn = pg_connect($connection_string);

if (!$dbconn) {
    die("Connection failed: " . pg_last_error());
}
echo "Connected successfully";

Let's run the deployment to production.

vercel --prod

We'll see the result in the console.



➜  start vercel --prod
Vercel CLI 32.5.0
🔍  Inspect: https://vercel.com/dbazhenovs-projects/start/224sUDqro9hi5gfne8oL9F5FaPkM [4s]
✅  Production: https://start-bzq80bkt8-dbazhenovs-projects.vercel.app [4s]
➜  start

And we can open the main domain in the browser to see the connection.

Let's check the Composer

Composer is a PHP package manager and it will allow us to use many out-of-the-box packages and features such as HTTP requests, logging, and templates and database management. I use it to develop large, complex applications.

For testing, I'll connect Guzzle, make a GET request to the GitHub API, and connect dd to print the result of the request in a readable form for development and debugging.

So, my application can request external APIs, save the results to a PostgreSQL database, and do other things like return some results to the user.

You must create a composer.json file in the project directory and add the necessary packages.



{
    "require": {
        "guzzlehttp/guzzle": "^7.0",
        "larapack/dd": "1.*"
    }
}

At the beginning of the index.php file let's add the composer packages connection, make a test query with Guzzle and print the result with dd()



<?php

require_once __DIR__ . '/../vendor/autoload.php';

$client = new \GuzzleHttp\Client();

$response = $client->request('GET', 'https://api.github.com/repos/guzzle/guzzle');

$result = json_decode($response->getBody(), true); 

dd($result);

As usual, we do the deployment
vercel --prod
and in less than a minute we see the result in production.

Conclusion

This is a fantastic technology.

You can make a website or backend API without worrying about server, hosting, web server configuration, and domains.

You can use the database for free as well, and you have several options for the database.

What to do next:

Develop the application and add features.
Develop the database, add tables schema, and make queries to insert and select data.

How Database Indexes Affect MongoDB and Application Performance

Daniil Bazhenov — Thu, 04 May 2023 09:50:46 +0000

I will show with real examples and graphs how indexes can affect the performance of the database and your application.

This article was inspired by a lot of feedback from my friends saying that MongoDB is slow or crashing. Indeed, when I started working with MongoDB, I noticed very quickly that it starts working slowly, although there were few documents in the database. MySQL was faster in the same situation during development with no load. I just didn't add any indexes.

As an experiment, I will run an application that will make lots of queries to different collections in the database. In the experiment, I will show that it can run fast and slow and even crash entirely because of our mistakes, just like any tool. At the beginning of the experiment, all queries are executed using indexes. Then I'll remove the index for a while, show how it affects it. And I'll create it again to restore the performance.

About the application and the database

The application is developed in PHP 8 and uses the default MongoDB driver for database queries.

The application is deployed on a t2.micro (Free Tier) instance in AWS, with 1 vCPU and 1 GiB of Memory.

The application makes queries to several collections in a loop:

Pages - 3.2 million documents
Users - 130k documents
Docs - 100k documents

Percona Server for MongoDB is used as the database. MongoDB ReplicaSet consists of three nodes also deployed in AWS, each node on a separate t2.micro instance.

I use the free, open-source tool Percona Monitoring and Management (PMM) for monitoring and graphing.

These limited resources are available to everyone for free, and you will see how much you can get out of them.

Let's start the experiment

I ran the application to do the load on the database. All queries in this experiment were performed to the Primary node to simplify the demonstration. And also in the final section of the article I will make an experiment with the load on all three nodes.

The loop performed several FindOne queries

Get a document from a collection sorted by timestamp
Get an random document by id.

Queries were run against two different collections (with 3 million and 100k documents)

Example code

   $last_user = $app['db']->users->findOne([],
   [
       'sort' => [
           'timestamp' => -1
       ]
   ]);

   $user_id = rand(1, $last_user['user_id']);

   $user_data = $app['db']->users->findOne(
   [
       'user_id' => $user_id
   ]);

When I ran one process, I got about 1,000 queries per second and 30% of the CPU load of the database instance.

Then I started the second similar process. I got about 1.8k queries per second and just over 50% CPU load.

I was shocked by these results because these are very big numbers. For example, if you develop a website, you can get hundreds of requests per second (RPS) from online users on these resources and store millions of rows (documents) in a database with a disk of less than 10GB.

Queries to the database were very fast. About 2-100 ms to a collection with 100k documents and 3-300 ms to a table with 3 million documents. Not enough resources for 3 million documents, but it kept running at a speed acceptable to a live user.

Let's delete the index in the collection with 100k documents

I opened MongoDB Compass and removed the index on the timestamp field. One of the queries was sorting the collection by this field.

Performance has degraded dramatically:

The number of queries has dropped from 1.8k to 120 per second.
The load on the CPU has increased to 90%, to the limit.
The query time went from 100ms to 20+ seconds on average.

Yes, the app continued to work. If you don't have many users while the app is in development, you probably won't even notice it. But the app was very bad.

Always create indexes for all fields that are used in search and sorting.

Let's create the index

I just created an index that I deleted earlier.

Performance, CPU utilization, and execution time were immediately restored.

Now delete the index in the collection of 3 million documents

Performance has dropped to less than one operation per second.

The Primary node died after about 30 seconds. Then I lost the connection to the second Secondary node, after which the PHP application ended with an error.
It took me about 10 minutes to recover the instances, and I didn't repeat the experiment.

It is gratifying that simple Reboot instances through AWS control panel automatically started all database nodes; they connected to the monitoring and continued to work.

Conclusion one

Always add indexes.

To learn more about them, I recommend taking the excellent MongoDB performance course from MongoDB:
M201: MongoDB Performance

Let's try to use all the ReplicaSet nodes

In the experiment, I only queried the Primary node, but I have a ReplicaSet with three nodes.

I set Read Preference for read operations from Secondary Nodes.

I just used the parameter when initializing the database client in my application

 'readPreference' => 'secondaryPreferred'

As a result, the initialization looks like this

$app['db_client'] = new \MongoDB\Client(MONGODB_URLS,
   [
       'username' => MONGODB_USER,
       'password' => MONGODB_PASSWORD,
       'replicaSet' => 'MongoDB-RS',
       'authSource' => 'admin',
       'readPreference' => 'secondaryPreferred'
   ],
);

As a result, all write operations are automatically executed in Primary, and read operations in Secondary nodes.

So, without increasing the number of resources, using t2.micro instances I got:

1k ops/sec read operations on each node;
over 500 ops/s of writes and updates (insert, update, delete).

The CPUs of each instance were less than 50% loaded, and I just didn't need more performance for my application.

I repeated the experiment with deleting the index in the collection where the write was done. I had another Docs collection with 100k+ documents. Then I restored the index.

And also got a lot of performance degradation.

Then I loaded the Primary node a little bit more.

Conclusion two

Think about performance, experiment, and keep an eye on monitoring.

It's not complicated or time-consuming, and various free tools are available now.

How to add authentication to HTTP requests and work with Rate limits using GitHub REST API example.

Daniil Bazhenov — Thu, 13 Apr 2023 17:24:44 +0000

I suggest that we dive deeper into HTTP requests to external APIs using the example of the GitHub REST API and a simple PHP script.

In past posts, we learned how to do a lot of requests in a loop and ran into limitations on the GitHub API side.

"message": "API rate limit exceeded for *.*.*.*. 
(But here's the good news: Authenticated requests get a higher rate limit. Check out the documentation for more details.)", "documentation_url":"https://docs.github.com/rest/overview/resources-in-the-rest-api#rate-limiting"

The API tells us the cause of the problem, but this is not always the case. More often we will have to guess on our own. It explicitly states that we need to read about rate limits.

After reading the documentation, I realize that:

We made requests as unauthenticated users. We did not use login/pass or token.
The API has rate limits and they may be different for different requests and authentication methods.
The current limits can be seen in the HTTP headers.

GitHub limits non-authenticated users to 60 requests per hour, and we did more than 10 in a few seconds. But if we are an authenticated user, we can do 5,000 or even 15,000 requests per hour.

So we have the following plan:

Add authentication to API requests.
Add a function to read headers containing information about limits, so as not to exceed the limits.

Let's look at the documentation and the available authentication methods

I explored the available authentication methods for the GitHub REST API on the Authenticating to the REST API page

I found that we need to get a special token and add it to the headers.

So we've looked at the limits and we have a plan:

Make a token.
Add the token to the headers.
Have fun!

Let's add authentication with a personal access token

You need to find the personal token creation page in your GitHub profile settings and create it, this page.

Save Token! :)

Stop our application if it is running.

docker-compose down

We need to pass our Token to the application, we use the environment variables in the docker-compose.yml file to do this.

Let's add a new environment variable GITHUB_TOKEN to the php-fpm service, because you will need it in your PHP code. Token is private information not to be shared with anyone.

docker-compose.yml

  php-fpm:
    image: php8.2-fpm-mongo
    volumes:
      - ./app:/var/www/html
    environment:
      DB_USERNAME: root
      DB_PASSWORD: secret
      DB_HOST: mongodb
      GITHUB_TOKEN: my_secret_token

We can run our application with the command

docker-compose up -d

Now our TOKEN will be available in PHP to use anywhere. We need to replace our simple Guzzle initialization with a new one using Token. Let's do this right after initializing the database in app/init.php.

app/init.php

if (isset($local_conf['GITHUB_TOKEN'])) {

    define('GITHUB_TOKEN', $local_conf['GITHUB_TOKEN']);

    $app['http'] = new \GuzzleHttp\Client(
        ['headers' => 
            [
               'Authorization' => 'Bearer ' . GITHUB_TOKEN
            ]
        ]
    );

} else {
    $app['http'] = new \GuzzleHttp\Client();  
}

You can notice that I made two modes. If we have Token in the environment variables we will do authentication, if not we will do requests without authentication.

Let's make a test request.

Great! Last time we were able to make only 9 requests and hit the limits. Now we have 30 requests and we have 770 repositories in the database.

HTTP headers and development of logic for dealing with limits

The Rate limit headers section in the documentation tells us about the headers that contain information about limits.

If we can read these headers, we can design a function to not exceed these limits.

Open the function fn_github_api_request which executes HTTP requests and add a function to read the headers and print them.

app/func/github.php

        $response = $app['http']->request($method, $url , [
            'query' => $params
        ]);              

        $headers = $response->getHeaders();

        dd($headers);

As a result, we will see the headers and their contents.
We will also see how many requests we have left until the next limit update.

Next, let's work the logic out:

After each request, we will save the value of X-RateLimit-Remaining and X-RateLimit-Reset
Before each request, we will check if we have information about the limits from the last request, this value, and if it is 0. Then we will wait for a number of seconds before resetting.

I added two new functions, setting limit information and checking limit information.
app/func/github.php

function fn_github_api_request_limits_set(&$app, $response)
{
    $headers = $response->getHeaders();

    $app['github_http']['limits']['remaining'] = (int) $headers['X-RateLimit-Remaining'][0];
    $app['github_http']['limits']['reset'] = (int) $headers['X-RateLimit-Reset'][0];

}

function fn_github_api_request_limits_check($app)
{

    if (isset($app['github_http']['limits'])) {

        $remaining = $app['github_http']['limits']['remaining'];

        if ($remaining == 0) {
            $reset = $app['github_http']['limits']['reset'] - time();

            fn_print_progress($app, 'Github API X-RateLimits will be reset in ' . $reset . ' sec.', true);
            sleep($reset+1);
        }
    }

}

I also added the useful function fn_print_progress, I added it to the file app/func/common.php, for general useful functions.

This function prints the result of the application in a convenient form.

Now I can see the limits and the time in microseconds and seconds.

After playing around a bit with the code with and without Token, with the if ($remaining < 2) setting, I checked that everything works fine for the current stage and the sleep function works as needed.

Bonus functions

In the process of development before the current one, I added some useful features. I will not publish them here as it takes a lot of code. You can find them in the repository

These are just useful functions to display information while the script is running and when it's finished.

You may notice that running a script that has run dozens of requests and saved hundreds of repositories to the database consumes only 2-3 MB of RAM.

This is important when I run long-running scripts that can make thousands of queries, if I save the data in an array, it can get bigger and the script will crash because of lack of allocated memory.

We now have a feature that will let you know the amount of memory consumed at any time.

Conclusion

We have learned how to do authentication and perform an unlimited number of requests, taking into account the limits.

We've added some useful features and more logic.

Now, we can get more data from the API and even do it in multiple threads by running the scripts several times as separate processes from the console. We'll learn how to do this next time.

The code for the current state of the project is available in the repository.

Thank you!

The Story of One Mistake: How a Database Monitoring Tool Can Help a Developer

Daniil Bazhenov — Fri, 07 Apr 2023 16:40:33 +0000

I will tell you the real story of using database monitoring tools when developing an application. I will show you an example of how I managed to detect and fix a problem in the application.

About the app and the process

I am developing a PHP application using MongoDB as a database. The application is lightweight, and most load falls on the database. I have implemented functions at the application level to adjust the number of queries, as the application can quickly load the database to 100%.

For development, I use several small dev instances in AWS, use Percona Server for MongoDB with three nodes as a database, and have PMM installed to monitor the databases.

As a result, my development process consists of the following steps:

I developed a new feature and ran it on the dev server for testing.
I check the profiling on the PHP side, and there is no memory leak, and I am happy with the speed.
I check the database monitoring to ensure everything works fine.
I debug the feature, setting the number and types of queries in the function to balance the number of queries and the load on the database, if necessary.

How I added new functionality to the app

So I started the application and got ready to run the new feature. The feature was getting information from open sources, processing it, and saving it to the database. The second part of the functionality went through all the saved documents and did some additional processing.

At this point, the application already had a lot of features that loaded the CPU of the Primary Node by 25-40%, and everything was running stably. I decided to have a performance reserve, as I planned to add new features.

I checked several dashboards, and there were no anomalies or changes. PMM has many dashboards and charts, and I will only show a few, just some.

I saved the changes with the new feature and pushed it to the dev server to make it work. Then I checked that the function started without errors, and the result was visible in the database. I use MongoDB Compass to check the result of a database entry.

Something has gone differently than planned.

I waited a few minutes and rechecked the dashboard. At first glance, the main screen was fine. However, I was alarmed by the speed of processing. The number of operations has mostly stayed the same.

I scrolled down through the various charts on the dashboard and saw an anomaly.

The latency increased, and the app loaded the instance to 100% CPU.

I have made a test run on the application side and checked the profiler there, too. The app worked poorly, and queries were slow.

Finding the cause of the problem

I knew the reason was the new feature and immediately rolled back the last changes.

I had a rough idea of where the problem might be, made a few changes, and started again.

I did it several times, but the result was the same (the CPU was loaded at 100%).

I selected a period with a load and used the Query Analytics function built into the monitoring.
Query Analytics shows a list of queries sorted by load or execution speed. Some of the queries to the Pages collection gave 90% load, and the Query Time was more than 3 minutes.

In Query Analytics, you can find slow queries, see their details, and then debug them in the application.

Fixing the problem

I made a few changes that fixed the problem.

The first problem was the indexes. I create indexes from within the application using the command.

$app['db']->CollectionName->createIndex(['index_key' => 1]);

Since the application uses many different collections and queries with conditions on various fields and with or without sorting, I have a lot of indexes.

I made a typo in this case, and the index was not created correctly.

After the indexes were created correctly, I needed quick runs to debug the number of queries to adjust the CPU load to around 50%.

You can see the final chart after debugging and fixing the problem.

About the monitoring tool

I used the free, open-source database monitoring Percona Monitoring and Management (PMM). This is a professional tool based on Grafana.

It is enough to install locally or on a server PMM Server and PMM Client on each database instance. Documentation

It also takes 60-360 minutes to get used to it, as it has many features and dashboards.

Conclusion

Don't forget to add indexes and make sure they work.

I am a simple developer who can make mistakes and do different experiments. Installing the monitoring was one of the experiments, and previously I just focused on the speed of the PHP script. From time to time, I have looked at the monitoring dashboard in the AWS control panel, but it gives less information, only about the instance itself, without being able to investigate in detail.

If you use MySQL, PostgreSQL or MongoDB, try installing PMM and see how your database works. Installing PMM for development purposes took a limited amount of resources and was straightforward. It benefited me because I discovered and fixed the problem before it went into production.

A small clarification, the story is real from my development practice, but for the article I took the graphs of the final debugging, so that the graphs show the right sequence and fit into the image available for explanation and demonstration. Just in reality I went to drink coffee several times and thought for a long time, what was reflected in the graphs of monitoring :)

How To Optimize the Code Structure of a Simple PHP Application as Your Project Grows

Daniil Bazhenov — Wed, 29 Mar 2023 18:07:34 +0000

Let's discuss the structure of our simple PHP application, folders, files, and functions and transform the project to grow it.

You and I have developed a small PHP application that makes an HTTP request to the GitHub API and stores the result in the database.
Step 1 - How to Develop a Simple Web Application Using Docker-compose, Nginx, PHP 8, and MongoDB 6.
Step 2 - How to Make HTTP Requests to API in PHP App Using GitHub API Example and Write to Percona Server for MongoDB.

Our code already performs different functions:

Reading environment variables.
Connecting to the database.
Running API queries.
Looping and writing to the database.

The code is already hard to fit on the screen, and it's time to think about dividing the code into files, folders, and functions.

Frameworks are usually responsible for separating code into folders and files. But we don't use frameworks, so we'll do it ourselves. At this stage, we will not make the structure too complicated. Our task is to learn how to change it so that we can change it at any time in the future.

I prefer to change the structure of files and folders as the project grows, grouping files according to meaning and logic. In this article, we'll make the first change, and we'll do them more times in the future.

Let's start optimizing our application.

Initializing and configuring the application

Usually, PHP scripts are executed sequentially starting with index.php, as in our case.

At the beginning of index.php, we connect the composer libraries, read environment variables, create a database connection, and create an object for HTTP requests. This will be required for each script. I propose to put this in a separate init.php file.

Create an app/init.php file and move the initialization to it. We simply move the code from the index.php file, leaving only the logic of the script.

app/init.php



<?php

// Enabling Composer Packages
require __DIR__ . '/vendor/autoload.php';

// Get environment variables
$local_conf = getenv();
define('DB_USERNAME', $local_conf['DB_USERNAME']);
define('DB_PASSWORD', $local_conf['DB_PASSWORD']);
define('DB_HOST', $local_conf['DB_HOST']);

// Connect to MongoDB
$db_client = new \MongoDB\Client('mongodb://'. DB_USERNAME .':' . DB_PASSWORD . '@'. DB_HOST . ':27017/');

$app['db'] = $db_client->selectDatabase('tutorial');

$app['http'] = new \GuzzleHttp\Client();

You may also notice that I created an array or $app object, and added HTTP and db as array elements. That way I can use $app anywhere to pass to functions and have HTTP queries and database handling there.

And in the index.php file itself, we simply include our init.php

_app/index.php



<?php

require __DIR__ . '/init.php';

dd($app);

And print db and http with dd() to make sure they work and are available in index.php.

Leave the rest of the code in index.php unchanged for now and run localhost in the browser.

Let's get to the functions

Functions are needed to group code that is executed multiple times.

Functions can be initialized either in the executable PHP file itself, next to the code, or in a separate file that includes, such as composer libraries. You can pass parameters, variables or arrays into functions and functions can return some result.

For example, we make a GET HTTP request to the GitHub API at a certain URL. We will probably need to make another type of request to another URL, but it will still be an HTTP request using guzzle.

I assume in advance that I will have many different functions: general, for GitHub, for the database. So let's create a func folder in the app folder.

And in the app/func folder, create a github.php file. Create our first function there that will request the GitHub API.

We'll pack the code responsible for the HTTP request into a function.

app/func/github.php



<?php

function fn_github_api_request($app, $url, $method, $params = []) 
{

    try {
        $response = $app['http']->request($method, $url , [
            'query' => $params
        ]);              

        $result = $response->getBody();

        $result = json_decode($result, true);

    } catch (GuzzleHttp\Exception\ClientException $e) {
        $response = $e->getResponse();
        $responseBodyAsString = $response->getBody()->getContents();
        echo $responseBodyAsString;
    }  

    if (empty($result)) {
        $result = false;
    } 

    return $result;
}

A little clarification, we will modify this file in the future, the first function is not the best. You should understand that you will need to change frequently, changing parameters and variables within functions.

Now go back to the index.php, and connect our first function file.

app/index.php



<?php

// Enabling Composer Packages
require __DIR__ . '/init.php';
require __DIR__ . '/func/github.php';

$url = 'https://api.github.com/search/repositories';

$params = [
    'q' => 'topic:mongodb',
    'sort' => 'help-wanted-issues'
];

// New function
$repositories = fn_github_api_request($app, $url, 'GET', $params);

if (!empty($repositories['items'])) {
    foreach($repositories['items'] as $key => $repository) {

        $updateResult = $app['db']->repositories->updateOne(
            [
                'id' => $repository['id'] // query 
            ],
            ['$set' => $repository],
            ['upsert' => true]
        );

    }
}

dd($repositories);

Run localhost and see the same result. Our request was executed, and we got the list of repositories.

The structure of our code in the app folder will look like this.
app/



.
├── func
│   └── github.php
├── vendor
├── composer.json
├── index.php
└── init.php

Loops and pagination

All this time we made only one API request and got the same number of repositories.

I propose to run our repository retrieval function in a loop and get 1000 repositories.

We will use the for loop



for ($i =1 ; $i <= 30; $i++) {
    // Do something 
}

Where i will be the page number for request to api.

app/index.php



<?php

// Enabling Composer Packages
require __DIR__ . '/init.php';
require __DIR__ . '/func/github.php';

$url = 'https://api.github.com/search/repositories';

$params = [
    'q' => 'topic:mongodb',
    'sort' => 'help-wanted-issues'
];

for ($i = 1; $i <= 30; $i++) {

    $params['page'] = $i;

    $repositories = fn_github_api_request($app, $url, 'GET', $params);

    fn_github_save_repositories($app, $repositories);

    echo "Page: " . $i . " ";
}

I converted the index.php file so that:

The loop will add the page parameter corresponding to i and execute a request to the api.
I also made a function fn_github_save_repositories in which I placed the code responsible for saving to the database.
I added the output of the page sequence number.

When I ran localhost in the browser and looped, I saw that 9 requests were executed, and then I got an error



Page: 1 Page: 2 Page: 3 Page: 4 Page: 5 Page: 6 Page: 7 Page: 8 Page: 
9 Page: 10 {"message":"API rate limit exceeded for *.*.*.*.
(But here's the good news: Authenticated requests get a higher rate limit. 
Check out the documentation for more details.)",
"documentation_url":"https://docs.github.com/rest/overview/resources-in-the-rest-api#rate-limiting"}
Page: 11 {"message":"API rate limit exceeded for *.*.*.*. 
(But here's the good news: Authenticated requests get a higher rate limit. 
Check out the documentation for more details.)",
"documentation_url":"https://docs.github.com/rest/overview/resources-in-the-rest-api#rate-limiting"}

We made requests to the API as unauthorized users and got used to the limits. We need to add authorization. We'll fix that in the next article, as adding authorization will require a few big steps.

Check our database with MongoDB Compass

I ended up with 293 repository documents in the database.

Conclusion

As a result of this modification, we learned how to split one script into several scripts and create functions. We did a lot of queries and increased our database. True, we now know that API has limits.

You can check and run the source code in the repository.

In the next post, we will continue to modify and improve our application. We will add new functions and features. I'm sure we'll end up with a very useful app, and you'll learn how to develop it.

Ask me questions, I'll be happy to answer them.

Why and how to use Docker-compose for application development

Daniil Bazhenov — Fri, 24 Mar 2023 16:08:18 +0000

I often see examples using docker in manuals and documentation for services and products.

Docker containers are generally convenient, but we must run them manually with different parameters if we use several containers.

A simple example

If we need to run PHP + Nginx + MongoDB app, we need to run 3 long commands, and if we need to change them, it will not be convenient.

Docker-compose comes in, which allows us to describe in a single file which containers we need to run and with which parameters. As I explained in my article on preparing the environment: How to Develop a Simple Web Application Using Docker-compose, Nginx, PHP 8, and MongoDB 6

So, what we'll need:

Install docker and docker-compose.
Prepare docker-compose.yml
Run it and stop it when it is no longer needed.

You can use this locally or run it on a server.

Useful Commands

These commands are executed quite often during development.

Docker-compose runs several containers with the right environment variables and parameters described in the file docker-compose.yaml

To start them up, we use the command:

docker-compose up -d

We stop it with

docker-compose down

The commands must be run from the directory where docker-compose.yml is located.

The docker-compose ps allows you to see what is currently running.

The command to connect to one of the containers, if necessary

docker exec -it [container-name] bash

[container-name] is available in the list of running containers

Launch result in console

Docker-compose features that I use

Environment variables

It is very convenient and helpful not to keep secrets in the source code.

Environment variables can be connected as a file or as parameters, or you can make shared environment variables for all containers. This is much more convenient than working with environment variables with a simple docker command.

For example, API or databases access data or environment startup parameters.

Simple use of environment variables in docker-compose.yml

    environment:
      DB_USERNAME: root
      DB_PASSWORD: secret
      DB_HOST: localhost

I am connecting environment variables as a file.

File docker-compose.yaml

  php-fpm:
    image: php8-mongo
    env_file: 
      - .env

File .env

MONGODB_USER: dba
MONGODB_PASSWORD: secret
MONGODB_HOST: localhost

And that's it, and the variables can be read in any programming language application. In addition, you can use this on any hosting or server, and DevOps will be happy.

Volumes and file system

I do not use volumes from docker for the files, but I use the volumes parameter to connect and link the local directories and the filesystem within the container.

I use a local folder on my computer to develop and modify code, which automatically connects to the container for code execution.

  php-fpm:
    image: php8.2-fpm-mongo
    volumes:
      - ./app:/var/www/html

Also, when running the database container, I prefer to use the main server's or laptop's file system. When the database container starts, I can immediately see the data files and explore, make a backup, or transfer.

  mongodb:
    image: "percona/percona-server-mongodb:6.0.4"
    volumes:
      - ./data:/data/db

This is handy during development and experimentation.

Although you could use the built-in volumes by docker.

Changing application configurations in a container

Often applications require configuration modifications, usually done by editing a parameter file or config file inside a container.

For example, changing PHP, MongoDB, or Nginx parameters requires editing those software files:

/usr/local/etc/php-fpm.d/zz-docker.conf
/etc/mongod.conf
/etc/nginx/conf.d/default.conf

You can connect to the container and edit these files internally, but the next time you run it, the settings will be reset again.

In this case, I copy the config file from the container to my computer in the project folder and connect it to the inside of the container via docker-compose.yml

This way, I can edit the parameters in the file, which will be used inside the container when running.

Example for Nginx

    volumes:
      - ./config/nginx/default.conf:/etc/nginx/conf.d/default.conf
      - ./config/nginx/nginx.conf:/etc/nginx/nginx.conf

As a result, my project has the following file structure.

Conclusion

I love it, recommend it, and use it.

Quick Tips to Learn Monitoring With Grafana

Daniil Bazhenov — Wed, 22 Mar 2023 16:49:09 +0000

I recommend you try this, get a free cloud-based Grafana at the official site. The free resources are enough to get acquainted with monitoring and observability.

Grafana is a trendy open-source monitoring software.

If you are learning different technologies, this will be helpful.

You will learn and try monitoring software.
You can connect your apps and servers to the Grafana cloud and use it for free at the start.

Also, I took some courses today at University Grafana, and I find them very useful because they are quick (about 10 minutes per course) and teach.

I went through 5 blocks of practice in just one hour on the Playlist: Intro to visualizing data with dashboards and panels.

I recommend spending this hour today learning about software monitoring.

How to Make HTTP Requests to API in PHP App Using GitHub API Example and Write to Percona Server for MongoDB

Daniil Bazhenov — Tue, 21 Mar 2023 12:02:01 +0000

We learn how to work with HTTP requests in PHP and make API requests using the GitHub API as an example. We'll get the data from the API and save it to the database.

In the previous article, we developed a simple application that connects to the MongoDB database (Percona Server for MongoDB) and writes documents in a loop. We only used Composer packages to work with MongoDB. We have set up the Docker-compose environment and have the app/ directory where the application code is located. We can edit the code and check the result in the browser without restarting the Docker containers.

We plan to make HTTP requests to GitHub API. I prefer using the popular PHP HTTP client library guzzlehttp/guzzle.

Here we go!

1. Preparation

Open the project folder in the console or git clone the repository, and open the folder.

Start Docker-compose

docker-compose up -d

This will start the containers, and you can run localhost in the browser.

2. Connect PHP libraries using Composer

Composer is a package manager for PHP. You can find out-of-the-box libraries and functions for almost any task. I prefer to use something other than Frameworks for small projects, but just plug-in packages.

Open the app/composer.json file in the code editor.

Add two new packages, guzzle, and dd.

{
    "require": {
        "guzzlehttp/guzzle": "^7.0", // Guzzle for HTTP
        "larapack/dd": "1.*", // dd for debug
        "mongodb/mongodb": "^1.6",
        "ext-mongodb": "^1.6"
    }
}

Connect to the container with php-fpm

docker exec -it [php-fpm-container-name] bash

Update the packages with the command.

composer update

That's it, we have the packages installed, more about Composer is here.

3. Let's choose an API and read the rules of use.

I chose the GitHub API as an example because it is available with and without authorization and has good documentation.

First, we will get a list of repositories. We will use the repository search API for all repositories containing the topic mongodb.

I opened the REST API documentation and found the method, parameters, and examples I needed.

4. Let's make the first request to the API.

Open the index.php file in the app folder and add the following code somewhere at the beginning, after the inclusion of vendor/autoload.php

$http = new \GuzzleHttp\Client();

$url = 'https://api.github.com/search/repositories';

$params = [
    'q' => 'topic:mongodb'
];

try {
    $response = $http->request('GET', $url , [
        'query' => $params
    ]);              

    $result = $response->getBody();

    $result = json_decode($result, true);

} catch (GuzzleHttp\Exception\ClientException $e) {
    $response = $e->getResponse();
    $responseBodyAsString = $response->getBody()->getContents();
    echo $responseBodyAsString;
}  

dd($result);

To summarize, here is what we did:

Initiated the HTTP client Guzzle.
Created a variable with a URL from GitHub API.
Created array with query parameters, in our case q search string.
Executed request in Try-Catch construct; if a request has errors, an exception will be thrown, and we can read the error.
Received response results from API into variable $result.
Converted the response from JSON to an array.
Printed out the array in a convenient form using dd($result).

Run localhost in the browser and see the result.

5. Exploring the result

dd() is a function that helps print any variable, array, or object. Just list them separated by commas.

You have printed the response from the API and see the array:

total_count - says that there are 72945 repositories for our query. This looks like an excellent dataset for our experiments with analytics and the database
items - contains an array of 30 other arrays with repository data.

I suggest modifying our API query to get a slightly different result. We'll add sorting to the query.

Change the $params array in the index.php file.

Let's add sorting by the count of help-wanted-issues.

app/index.php

$params = [
    'q' => 'topic:mongodb+language:php',
    'sort' => 'help-wanted-issues'
];

Run localhost in your browser, and you will see a different set of items in the API response.

6. Let's save the data to the database

Instead of dd(), we add a foreach loop over items and print one repository data.

app/index.php

if (!empty($result['items'])) {
    foreach($result['items'] as $key => $repository) {

        dd($repository);

    }
}

Great, we see the data. This will be our document in the collection of repositories.

Replace dd($repository) with an insert query to the MongoDB database.

Remember to move the client db initialization, database connection, and read environment variables.

app/index.php

if (!empty($result['items'])) {
    foreach($result['items'] as $key => $repository) {

        $updateResult = $db->repositories->updateOne(
            [
                'id' => $repository['id'] // query 
            ],
            ['$set' => $repository],
            ['upsert' => true]
        );

    }
}

dd($result);

Run localhost in your browser. I usually always type dd() at the end, since it stops the script and displays something like dd('finish').

You may notice that making a updateOne request to the repositories collection, with the parameter upsert. This means that if such a document with an id ('id' => $repository['id']) already exists in the database, it will be updated. If not, it will be inserted. This allows me to avoid duplicate data with multiple test queries.

7. Connecting to a database using MongoDB Compass.

We must see that we did everything correctly and the data appeared in the database.

Besides, we can play with them.

Open our new collection and check it out. We can also create an index by the id key in the Indexes tab. If you did not make the index with a PHP query.

Download and install MongoDB Compass here

Conclusion

The code of the application at the current stage can be obtained from the repository.

We did a great job and figured out how to make requests to the API, as you can make any HTTP requests.

In my next posts, we'll figure out how to add authorization and work with API limits. The point is that GitHub lets unauthorized users make a few requests. We aim to get a lot of valuable data from the API and will do that soon.

We will also devote some time to the structure of the files and folders of our application. It's already getting big, and we must make it more convenient.