```cv
gallery
{"layout":"hscroll","images":[{"src":"https://firebasestorage.googleapis.com/v0/b/maitake-project.appspot.com/o/pages%2FKUJX9WOAJWgKCH1WphLqFMO9z7O2%2FPUDgYkrHBA9VjkAGmVU1%2F231f0fb0-b79c-416e-b287-2dfbfee0ed12.jpg?alt=media&token=91670d80-dd55-49fc-be02-9a484911ac96","id":"231f0fb0-b79c-416e-b287-2dfbfee0ed12","width":4000,"height":6000,"filename":"brigitta-baranyi-TZOYfErRQEE-unsplash.jpg","type":"image/jpeg","caption":"Photo by Brigitta Baranyi on Unsplash.","border":false}]}
```

Creating the books-api app and for any other application for that matter, during the initial stages we need to populate the database with either real or dummy data. This process is what we call seeding the database and there are several methods to seed a database and one of which will be shown in this article.

We're basing our entire setup from the books-api repo [here](https://github.com/riveraja/books-api/tree/main). So if you want to follow through with this article you can clone the repo and try it yourself.

First, let's understand the structures for tables `authors`, `publishers`, and `books`.

```
postgres=# \d authors
                                Table "public.authors"
    Column    |            Type             | Collation | Nullable |      Default
--------------+-----------------------------+-----------+----------+-------------------
 id           | uuid                        |           | not null | gen_random_uuid()
 name         | text                        |           | not null |
 profile_link | text                        |           |          |
 created_at   | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "authors_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "books" CONSTRAINT "fk_author_id" FOREIGN KEY (author_id) REFERENCES authors(id)

postgres=# \d publishers
                              Table "public.publishers"
   Column   |            Type             | Collation | Nullable |      Default
------------+-----------------------------+-----------+----------+-------------------
 id         | uuid                        |           | not null | gen_random_uuid()
 name       | text                        |           | not null |
 site_link  | text                        |           |          |
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "publishers_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "books" CONSTRAINT "fk_publisher_id" FOREIGN KEY (publisher_id) REFERENCES publishers(id)

postgres=# \d books
                        Table "public.books"
     Column     |  Type   | Collation | Nullable |      Default
----------------+---------+-----------+----------+-------------------
 id             | uuid    |           | not null | gen_random_uuid()
 author_id      | uuid    |           |          |
 publisher_id   | uuid    |           |          |
 title          | text    |           | not null |
 date_published | date    |           | not null |
 isbn           | text    |           |          |
 page_count     | integer |           |          |
 created_at     | date    |           |          | CURRENT_TIMESTAMP
 img_link       | text    |           |          |
Indexes:
    "books_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_author_id" FOREIGN KEY (author_id) REFERENCES authors(id)
    "fk_publisher_id" FOREIGN KEY (publisher_id) REFERENCES publishers(id)

postgres=#
```

And our books data is formatted in JSON as such:

```
const books = [
  {
    title: "Iron Flame",
    date_published: "2023-11-07",
    isbn: "978-1-64937-417-2",
    page_count: 623,
    publisher: "Red Tower Books (Entangled Publishing)",
    site_link: "https://www.entangledpublishing.com/about-us",
    author: "Rebecca Yarros",
    profile_link: "https://en.wikipedia.org/wiki/Rebecca_Yarros",
    img_link: "https://en.wikipedia.org/wiki/File:Iron_Flame_Cover_Art.jpg",
  },
  {
    title: "Spare",
    date_published: "2023-01-10",
    isbn: "978-0-59359-380-6",
    page_count: 416,
    publisher: "Penguin Random House",
    site_link: "https://en.wikipedia.org/wiki/Penguin_Random_House",
    author: "Prince Harry, The Duke of Essex",
    profile_link: "https://en.wikipedia.org/wiki/Prince_Harry,_Duke_of_Sussex",
    img_link: "https://en.wikipedia.org/wiki/File:Spare_cover.jpg",
  },
  {
    title: "Fourth Wing",
    date_published: "2023-04-05",
    isbn: "978-1-64937-404-2",
    page_count: 512,
    publisher: "Red Tower Books (Entangled Publishing)",
    site_link: "https://www.entangledpublishing.com/about-us",
    author: "Rebecca Yarros",
    profile_link: "https://en.wikipedia.org/wiki/Rebecca_Yarros",
    img_link: "https://en.wikipedia.org/wiki/File:Fourth_Wing_Cover_Art.jpeg",
  },
  {
    title: "The Woman In Me",
    date_published: "2023-10-23",
    isbn: "978-1-66800-904-8",
    page_count: 288,
    publisher: "Gallery Books",
    site_link: "https://en.wikipedia.org/wiki/Gallery_Publishing_Group",
    author: "Britney Spears",
    profile_link: "https://en.wikipedia.org/wiki/Britney_Spears",
    img_link:
      "https://en.wikipedia.org/wiki/File:Britney_Spears_-_The_Woman_in_Me.png",
  },
];
```

Here we will store the values for `author` and `profile_link` to the `authors` table, the `publisher` and `site_link` to the `publishers` table and the rest of the object's values to the `books` table.

### Dissecting the code

To be able to insert the values to the correct table we will need to iterate through each element in the JSON object.

```
for (const values of books) {
```

We then initialize `author` and `publishers` as empty JSON objects.

```
   let author = {};
   let publisher = {};
```

Next, we're defining the constant reference to the `author_info` object that takes the author's name and profile link.

```
    const author_info = {
      name: values.author,
      profile_link: values.profile_link,
    };
```

To avoid duplicate records, we will execute an `entities.author.find()` query against the database to fetch any existing author name matching `values.author`.

```
    const findAuthor = await entities.author.find({
      fields: ["id", "name"],
      where: { name: { like: values.author } },
      limit: 1,
    });
```

If a match is found the next lines of code will be skipped otherwise the data will be stored in the database.

```
    if (Object.entries(findAuthor).length === 0) {
      author = await entities.author.save({ input: author_info });

      console.log("Create author:", author);
    }
```

Similar process happens when evaluating the publisher's data finding a match and skipping the insert.

Next step is to store the book info to the database taking into consideration the data from `authors` and `publishers` tables.

```
    const book_info = {
      author_id: author.id != null ? author.id : findAuthor[0]["id"],
      publisher_id:
        publisher.id != null ? publisher.id : findPublisher[0]["id"],
      title: values.title,
      date_published: values.date_published,
      isbn: values.isbn,
      page_count: values.page_count,
      img_link: values.img_link,
    };

    const book = await entities.book.save({ input: book_info });
```

As I was writing this I noticed we will need to add a function to check for duplicates in the `isbn` value given that this key should be unique for each table. Adding a `UNIQUE` constraint in the PostgreSQL table should add consistency to the database as well.

Seeding the PostgreSQL data using NodeJS

```cv
gallery
{"layout":"hscroll","images":[{"src":"https://firebasestorage.googleapis.com/v0/b/maitake-project.appspot.com/o/pages%2FKUJX9WOAJWgKCH1WphLqFMO9z7O2%2Fplatformatic-books-api%2F2994e4ba-fd9b-437f-9aa8-96bee92f8f9c.jpg?alt=media&token=6539747b-71c5-4382-851b-f78352e2df91","id":"2994e4ba-fd9b-437f-9aa8-96bee92f8f9c","width":512,"height":512,"filename":"e828abd7-e445-4318-9d04-eca473782ae3.jpeg","type":"image/jpeg","caption":"","border":false}]}
```

In this tutorial we will explore how to build a books API using [Platformatic](https://platformatic.dev/feature/db/) and PostgreSQL as the database backend.

### **Initialize the developer environment**

First, let's initialize the developer environment from the terminal.

```
$ mkdir books-api
$ cd books-api/
$ npm init -y
$ npm i platformatic@latest fastify@latest
$ npx create-platformatic@latest
Need to install the following packages:
create-platformatic@1.31.1
Ok to proceed? (y)
 Hello Jericho Rivera, welcome to Platformatic 1.31.1!
? What kind of project do you want to create? Application
? Where would you like to create your project? .
? Which kind of project do you want to create? DB
? What is the name of the service? books-db
? What is the connection string? sqlite://./db.sqlite
? Do you want to create default migrations? no
? Do you want to create another service? no
? Do you want to use TypeScript? no
? What port do you want to use? 3042
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/package.json written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/platformatic.json written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/.env written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/.env.sample written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/.gitignore written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/README.md written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/package.json written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/platformatic.json written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/plugins/example.js written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/routes/root.js written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/test/helper.js written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/test/plugins/example.test.js written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/test/routes/root.test.js written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/.gitignore written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/README.md written!
[09:53:21] INFO: /Users/jerichorivera/Workspace/nodejs/books-api/services/books-db/global.d.ts written!
? Do you want to init the git repository? no
⠦ Installing dependencies...
[09:54:06] INFO: Project created successfully, executing post-install actions...
[09:54:06] INFO: You are all set! Run `npm start` to start your project.
```

### **Configure the application**

Open the folder in VS Code and edit the following lines inside tne `.env` file:

```
PLT_SERVER_HOSTNAME=127.0.0.1
PLT_BOOKS_DB_DATABASE_URL=postgres://user:password@127.0.0.1:5432/postgres
PLT_BOOKS_DB_APPLY_MIGRATIONS=false
```

Platformatic supports many database backends so you may edit the DB URL based on your needs.

Next, let's create the `migrations` folder:

```
$ mkdir services/books-db/migrations
```

### **Migrations**

Now we're ready to create our first migration.

```
$ npx platformatic db migrations create --config services/books-db/platformatic.json
```

This should create an `001.do.sql` and `001.undo.sql` files inside the `migrations` folder.

Edit the file based on your specifications. Standard SQL DDL commands are accepted.

Once you're ready you can execute the DDL with the `apply` directive.

```
$ npx platformatic db migrations apply --config services/books-db/platformatic.json
```

### **Start the application**

Let's start the application and open and access the client:

```
$ npm start

> start
> platformatic start

[10:19:14.775] INFO (books-db/95352): Server listening at http://127.0.0.1:3042
```

If everything worked up to this point you should have an OpenAPI endpoint and a GraphQL endpoint.

### **Seed the Database**

This procedure is as easy as creating a seed file and and executing it with:

```
$ npx platformatic db seed path/to/seed.js --config services/books-db/platformatic.json
```

You can find my sample seed file [here](https://github.com/riveraja/books-api/blob/main/services/books-db/seed/seed.js).

### **Protecting the API routes**

It is important that we protect our API routes to avoid abuse. Let's add an admin secret key and rules for entities.

Edit the file in `services/books-db/platformatic.json` to add the `authorization` object.

```
"authorization": {
    "adminSecret": "{PLT_AUTH_ADMIN_SECRET_KEY}",
    "rules": [
      {
        "entities": ["book", "author", "publisher"],
        "role": "platformatic-admin",
        "find": true,
        "save": true,
        "delete": false
      },
      {
        "entities": ["book", "author", "publisher"],
        "role": "anonymous",
        "find": true,
        "save": false,
        "delete": false
      }
    ]
  },
```

This will prevent `save` and `delete` actions from `anonymous` users and prevent `delete` from admin users while allowing `find` action to any user.

If you are still interested you can check my [books-api](https://github.com/riveraja/books-api) github repo.

Building Books API using Platformatic

```cv
gallery
{"layout":"hscroll","images":[{"src":"https://firebasestorage.googleapis.com/v0/b/maitake-project.appspot.com/o/pages%2FKUJX9WOAJWgKCH1WphLqFMO9z7O2%2Fterraform-cloudsql-deployment%2F425cdc23-c132-40f4-bbab-1394e528aa7d.jpg?alt=media&token=9b408e8c-3dce-4d39-adfd-a8e43f158c3f","id":"425cdc23-c132-40f4-bbab-1394e528aa7d","width":6016,"height":4016,"filename":"erhan-yildirim-Ixti1Yxk1XM-unsplash.jpg","type":"image/jpeg","caption":"Photo by Erhan YILDIRIM on Unsplash  ","border":false}]}
```

**Terraform** is an infrastructure automation tool to provision and manage resources in the cloud. I've been particularly interested and enjoyed using Terraform when provisioning cloud infrastructure.

In this post I will show how to codify provisioning of a MySQL with replicas in **GCP CloudSQL** using Terraform from your local system. Please note that I will not be using dynamic configuration in this blog post.

First, make sure you installed the tool itself. Installation *[instructions](https://developer.hashicorp.com/terraform/tutorials/aws-get-started/install-cli)* can be found in the official documentation page. It is also required to install the GCloud SDK, follow the instructions in the *[official documentation](https://cloud.google.com/sdk/docs/install)*. Moving forward, I'll assume you've already set up the gcloud sdk and terraform correctly in your system.

Create the `provider.yaml` file, copy the YAML code below and then run `terraform init` to initialize your setup.

```
terraform {
    backend "gcs" {
        bucket = "your-bucket-name"
        prefix = "your-bucket-prefix"
    }

    required_providers {
        google = {
            source = "hashicorp/google"
            version = ">= 5.2.0"
        }
    }
}

provider "google" {
    project = "your-project-id"
    region = "your-region-id"
}
```

Next, let's create the primary CloudSQL instance with a random suffix id. Create a `main.tf` file and paste the code below.

```
locals {
    resource = "cloudsql"
    env      = "production"
}

resource "google_sql_database_instance" "mysql_primary" {
    name                = "${local.resource}-${local.env}-source-${substr(sha256("${local.resource}-${local.env}-primary"), 2, 6)}"
    database_version    = "MYSQL_8_0_33"
    deletion_protection = false

    settings {
        tier                        = "db-custom-4-15360"
        activation_policy           = "ALWAYS"
        availability_type           = "REGIONAL"
        deletion_protection_enabled = false
        disk_autoresize             = true
        disk_size                   = 100
        disk_type                   = "PD_SSD"
        edition                     = "ENTERPRISE"

        backup_configuration {
            binary_log_enabled             = true
            enabled                        = true
            start_time                     = "04:00"
            transaction_log_retention_days = 3

            backup_retention_settings {
                retained_backups = 7
                retention_unit   = "COUNT"
            }
        }

        ip_configuration {
            ipv4_enabled = true
        }

        insights_config {
            query_insights_enabled = false
        }
    }
}
```

This resource block deploys a MySQL instance with 4 vCPUs and 16G RAM, at least 100G of persistent solid state drive auto-resizable running MySQL version 8.0.33 (as of this writing). Regional high availability is enabled with three days binlog retention and seven days of backup retention.

Let's create a super user since the default `root@%` user with no password will be deleted by Terraform on instance creation. Append the following code in the same `main.tf` file. Ideally we would want the password to be stored on GCP Secrets Manager to avoid secrets sprawl.

```
resource "google_sql_user" "admin_user" {
    name     = "your-admin-user"
    instance = google_sql_database_instance.mysql_primary.name
    host     = "cloudsqlproxy~%"
    password = "your-super-secret-password"
}
```

Ok! At this point we have the primary MySQL instance and the super user resource added.

Now let's add the replica instances. Append the following code in the `main.tf` file.

```
resource "google_sql_database_instance" "replica_1" {
    name                 = "${local.resource}-${local.env}-replica-${substr(sha256("${local.resource}-${local.env}-replica_1"), 2, 6)}"
    database_version     = "MYSQL_8_0_33"
    master_instance_name = google_sql_database_instance.mysql_primary.name
    deletion_protection  = false

    settings {
        tier                        = "db-custom-4-15360"
        activation_policy           = "ALWAYS"
        availability_type           = "ZONAL"
        deletion_protection_enabled = false
        disk_autoresize             = true
        disk_size                   = 100
        disk_type                   = "PD_SSD"
        edition                     = "ENTERPRISE"

        backup_configuration {
            binary_log_enabled             = true
            enabled                        = false
            transaction_log_retention_days = 3
        }

        ip_configuration {
            ipv4_enabled = true
        }

        insights_config {
            query_insights_enabled = false
        }
    }

    replica_configuration {
        master_heartbeat_period = 500
        connect_retry_interval  = 30
    }
}
```

Add as many replicas as you need with the same resource block just changing the first two lines from "replica-1" to "replica-N" where N is the sequence of the replica eg. `replica_2`, `replica_3`, `replica_4`.

```
resource "google_sql_database_instance" "replica_2" {
    name    = "${local.resource}-${local.env}-replica-${substr(sha256("${local.resource}-${local.env}-replica_2"), 2, 6)}"
    ...
}
```

The reason behind using separate `resource {}` blocks for each instance is to have full control over version upgrades instead of just upgrading the instances willy-nilly and cause an outage in production.

The next steps from here is to implement the code by running the following commands.

```
terraform plan -out tfplan
terraform apply tfplan
```

To cleanup, just run the following command and type `yes` when prompted.

```
terraform destroy
```

Thanks for reading!

Terraform for CloudSQL deployments

```cv
gallery
{"layout":"hscroll","images":[{"src":"https://firebasestorage.googleapis.com/v0/b/maitake-project.appspot.com/o/pages%2FKUJX9WOAJWgKCH1WphLqFMO9z7O2%2FX5UlZhEdhczx6oeOLat5%2F883caebf-5c56-4bcb-8657-7f8b8e95644b.jpg?alt=media&token=2e0aea8a-f3ef-441d-a777-468806ddaa7e","id":"883caebf-5c56-4bcb-8657-7f8b8e95644b","width":512,"height":512,"filename":"78f13075-e6fe-43d7-aed9-1a2539666750.jpeg","type":"image/jpeg","caption":"","border":false}]}
```

Recently, I have been working on ways to improve my testing environment and I stumbled upon Dev Containers. This article will provide a brief introduction on how to use Dev Containers in Visual Studio Code with FerretDB and PostgreSQL with Go.

### **What are Dev Containers?**

The Visual Studio Code Dev Containers is an extension that lets you use a container as a full-featured development environment. It seamlessly integrates with Docker to launch a single container or with Docker Compose to launch multiple containers.

### **What are the minimum requirements?**

You will need to install Docker Desktop, Visual Studio Code, and the Dev Containers extension.

### **The Setup**

Ensure that Docker is installed and running, please refer to the Docker documentation for this part.

Open Visual Studio Code and [install the Dev Containers](https://code.visualstudio.com/docs/devcontainers/containers#_installation) extension.

Create the root directory:

```
 mkdir goferret // you may choose any other name here
```

Create the ***.devcontainer*** folder and inside the new directory then create the ***devcontainer.json*** and ***docker-compose.yml*** files. Your folder structure would be similar to this:

```
tree -a goferret/
goferret/
├── .devcontainer
│   ├── devcontainer.json
│   └── docker-compose.yml
└── src

3 directories, 2 files
```

Add the following in the ***devcontainer.json*** file:

```json
{
    "name": "Go FerretDB",
    "dockerComposeFile": "docker-compose.yml",
    "service": "goferret",
    "workspaceFolder": "/workspace/src"
}
```

In the docker-compose.yml file, add the following lines:

```yaml
services:
  goferret:
    container_name: goferret
    image: mcr.microsoft.com/devcontainers/go:1.22-bullseye
    restart: on-failure
    volumes:
      - ../:/workspace:cached
    links:
      - ferretdb
    command: ["sleep", "infinity"]
  
  ferretdb:
    container_name: ferretdb
    image: ghcr.io/ferretdb/ferretdb
    restart: on-failure
    environment:
      - FERRETDB_POSTGRESQL_URL=postgres://goferret:goferret101@postgresqldb:5432/ferretdb
  
  postgresqldb:
    container_name: postgresqldb
    image: postgres
    restart: on-failure
    environment:
      - POSTGRES_USER=goferret
      - POSTGRES_PASSWORD=goferret101
      - POSTGRES_DB=ferretdb
    command: [ "postgres", "-c", "wal_level=logical" ]
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
```

At this point you can start using dev containers, open the Command Palette (ctrl-shift-p), and then type **Reopen in Container** at the prompt.

Initialize the app from the VSCode terminal with

```
go mod init goferret
```

Create the ***main.go*** file and add the sample code from the MongoDB official manual.

```go
// Connects to MongoDB and sets a Stable API version
package main

import (
	"context"
	"fmt"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

// Replace the placeholder with your Atlas connection string
const uri = "mongodb://goferret:goferret101@ferretdb:27017/ferretdb?authMechanism=PLAIN"

func main() {

	// Use the SetServerAPIOptions() method to set the Stable API version to 1
	serverAPI := options.ServerAPI(options.ServerAPIVersion1)
	opts := options.Client().ApplyURI(uri).SetServerAPIOptions(serverAPI)

	// Create a new client and connect to the server
	client, err := mongo.Connect(context.TODO(), opts)

	if err != nil {
		panic(err)
	}
	defer func() {
		if err = client.Disconnect(context.TODO()); err != nil {
			panic(err)
		}
	}()

	// Send a ping to confirm a successful connection
	var result bson.M
	if err := client.Database("admin").RunCommand(context.TODO(), bson.D{{Key: "ping", Value: 1}}).Decode(&result); err != nil {
		panic(err)
	}
	fmt.Println("Pinged your deployment. You successfully connected to MongoDB!")
}
```

From here you can either execute

```
go get .
go run .
```

to download the packages and execute the program.

```
vscode ➜ /workspace/src $ go run .
go: downloading github.com/klauspost/compress v1.13.6
Pinged your deployment. You successfully connected to MongoDB!
```

That's it, we completed setting up a Go + FerretDB + PostgreSQL development environment using Dev Containers and Docker with Visual Studio Code.

Developing with Golang + FerretDB + PostgreSQL using Dev Containers

```cv
gallery
{"layout":"hscroll","images":[{"src":"https://firebasestorage.googleapis.com/v0/b/maitake-project.appspot.com/o/pages%2FKUJX9WOAJWgKCH1WphLqFMO9z7O2%2FtSwWpNTQYFcOWpnBNah7%2F4bd55079-e463-409a-8312-24d05193253f.jpg?alt=media&token=a55fe450-d0fa-401a-9f9e-954c2ce2c533","id":"4bd55079-e463-409a-8312-24d05193253f","width":1024,"height":1024,"filename":"_19c859ef-94f0-4b0a-a16c-954b85b3e7ba.jpg","type":"image/jpeg","caption":"","border":false}]}
```

One of the most asked question in r/supabase is how to populate the profile table when a new user is created in Supabase. We're also creating a JWT token each time we insert a new row in the `profiles` table.

First let's create the `public.profiles` table as follows:

```
create table if not exists public.profiles (
   id uuid not null references auth.users on delete cascade,
   token text not null,
   primary key (id)
);

alter table public.profiles enable row level security;
```

We can then make use of the RLS policies listed in Supabase's documentation page.

```
create policy "Public profiles are viewable by everyone."
  on profiles for select
  using ( true );

create policy "Users can insert their own profile."
  on profiles for insert
  with check ( auth.uid() = id );

create policy "Users can update own profile."
  on profiles for update
  using ( auth.uid() = id );
```

Now the fun part!

We will store a JWT token in the `token` column and for this example we're using the `pgjwt` extension which is already enabled in Supabase. Our payload will be the `id` value, a private string used to sign the JWT, and the encryption method.

Since the payload needs to be in JSON format we will have to make use of  the `json_build_object()` wrapped with the `json_agg()` function.

```
select json_agg(
    json_build_object(
        'id', NEW.id
    )
)
```

We then use the `extensions.sign()` function from the pgjwt extension to create the token:

```
select extensions.sign(
  (
    select json_agg(
      json_build_object(
        'id', NEW.id
      )
    ),
    'secret',
    'HS256'
  )
)
```

We now have to complete the entire postgresql function and come up with this:

```
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $handle$
begin
  INSERT INTO public.profiles (id, token) VALUES (
    NEW.id,
    (select extensions.sign((select json_agg(json_build_object('id', NEW.id))), 'secret', 'HS256'))
  );
  return null;
end;
$handle$;
```

Let's finalize it by creating the `AFTER TRIGGER` here:

```
-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();
```

To verify the created token, we can use the online JWT decoder in [jwt.io](https://jwt.io/).

PostgreSQL new user profile function

Database Engineer

Deploys a 3-node mysql async replica set with 1 proxysql server as a read/write load balancer.

ProxySQL Docker setup

A simple books API using the following technologies:

* Platformatic

* PostgreSQL

* Fastify

* Supabase Auth (in progress)

Jericho Rivera

2024