PostgreSQL and Strapi setup

It is often confusing how to connect a Strapi instance to a Postgres database, but in this article I will demystify it with examples and images.

Goals

In this article, we will learn how to connect Strapi to PostgreSQL. By default, Strapi uses the SQLite for content storage, but Strapi is not only limited to using SQLite as the database. It can be configured to use other databases like MySQL, MariaDB, PostgreSQL, etc.

We will also learn how to:

• Create collections on Strapi,
• Setup a Postgres database in our machine,
• How to add API endpoints manually on Strapi, and
• Create a Bank admin app in React to consume the Strapi API endpoints.

Introduction to Strapi and PostgreSQL

Using PostgreSQL with Strapi is effective for managing and scaling content-driven applications. PostgreSQL is an open-source relational database known for its reliability and features, making it a good choice for supporting Strapi's headless CMS. To understand how PostgreSQL compares to other relational databases, consider this PostgreSQL comparison.

Benefits of Using PostgreSQL with Strapi

Connecting Strapi to PostgreSQL provides several advantages over the default SQLite database. PostgreSQL enhances scalability and performance, essential for larger projects requiring efficient data handling. Its support for complex queries and a wide range of data types enables more sophisticated data management within Strapi.

By using PostgreSQL, you can build stronger applications with Strapi. Setting up PostgreSQL with Strapi allows for better data integrity, advanced transactional support, and scalability as your application grows. Additionally, you can enhance productivity with Strapi by leveraging these features for efficient development. Using PostgreSQL with Strapi is ideal for projects that expect increased complexity and data volume.

Setting Up Your Development Environment

To get started with Strapi and PostgreSQL, you'll need to prepare your development environment.

Check System Requirements

Ensure your system is equipped with the following components:

• Node.js: Version 10.x or higher
• npm: Version 6.x or higher
• PostgreSQL: Installed and operational
• Database Client: Optional, such as pgAdmin 4 for database management

Install Node.js and npm

If Node.js and npm aren't installed, download them from the official Node.js website. Follow the installation prompts for your operating system.

Verify the installation by running:

node -v
npm -v

These commands should display the installed versions of Node.js and npm.

Set Up PostgreSQL

Download PostgreSQL from the official PostgreSQL website and run the installer. During installation:

1. Set a password for the default postgres user.
2. Install pgAdmin 4 if you prefer a graphical interface.

After installation, set up your database:

1. Open pgAdmin 4 and connect to your PostgreSQL server.
2. Create a new database:
   • Right-click on Databases, select Create, then Database.
   • Enter a name for your database and save.

Alternatively, use the command line:

psql postgres

In the PostgreSQL prompt, you can create a database and a role with login and password, and grant privileges by consulting PostgreSQL's official documentation or a trusted database administration resource for the precise SQL commands.

An Introduction to Strapi

Strapi is an open-source headless CMS based on Nodejs and used in designing APIS and managing content. Strapi helps us scaffold our backend quickly, build APIs and consume the APIs from the client-side. The client can be mobile, web, desktop, cURL, etc.

The APIs are created from the Strapi UI admin panel. We create collections as single-types; a collection in Strapi maps to the endpoints:

• POST /YOUR_COLLECTION_s: Creates new content.
• GET /YOUR_COLLECTION_s: Gets all the contents.
• GET /YOUR_COLLECTION_s/:ID: Gets a single content based on its ID.
• PUT /YOUR_COLLECTION_s/:ID: Edits content.
• DELETE /YOUR_COLLECTION_s/:ID: Deletes content.

By default, Strapi gives us RESTful APIs, but also we can create GraphQL APIs in Strapi. We can then use the GraphQL playground in the browser to run the queries and mutations. Setting up Strapi is very easy; run the command below:

     npx create-strapi-app strapi-api
     # OR
     yarn create strapi-app strapi-api

Next, we’re going to install some other dependencies that are required in this project.

     npm install --save @strapi/utils
     # OR
     yarn add @strapi/utils

Then, we run the yarn develop command to start the server at localhost:1337. The API endpoints are consumed from the localhost:1337 URL. Also, we can load the admin UI from the same URL at localhost:1337/admin.

Strapi contains both a server and a database. The server hosts the APIs, and the database is used to store the application's content. Strapi uses the Koajs framework for its server.

To verify this, go to strapi-API/config/ folder. You should see the following:

1    |---- config
2    |     |- api.js
3    |     |- admin.js
4    |     |- database.js
5    |     |- middleware.js
6    |     |- server.js
7    |     |- cron-tasks.js
8    |     |- plugins.js

This is where Strapi API configurations are kept. The api.js file contains general settings for API calls.

1    // path: ./config/api.js
2    module.exports = ({ env }) => ({
3      responses: {
4        privateAttributes: ['_v', 'id', 'created_at'],
5      },
6      rest: {
7        prefix: '/v1',
8        defaultLimit: 100,
9        maxLimit: 250,
10      },
11    });

The admin.js file contains an admin panel configuration for your Strapi application.

1    // path: ./config/admin.js
2    module.exports = ({ env }) => ({
3      apiToken: {
4        salt: env('API_TOKEN_SALT', 'someRandomLongString'),
5      },
6      auth: {
7        secret: env('ADMIN_JWT_SECRET', 'someSecretKey'),
8      },
9    });

The cron-tasks.js file is where we can set our cron jobs on Strapi. These jobs are scheduled to run periodically based on the format we input: \[SECOND (optional)\] [MINUTE] \[HOUR\] [DAY OF MONTH] \[MONTH OF YEAR\] [DAY OF WEEK]. To enable cron jobs, set cron.enable to true in the ./config/server.js file.

1    // path: ./config/cron-tasks.js
2    module.exports = {
3      /**
4       * Simple example.
5       * Every monday at 1am.
6       */
7    
8      '0 0 1 * * 1': ({ strapi }) => {
9        // Add your own logic here (e.g. send a queue of email, create a database backup, etc.).
10      },
11    };

The server.js file is where we configure the Strapi server. We can set our host, port, and session keys. Strapi, by default, serves at 0.0.0.0 at port 1337. We can change them in this file.

1    // path: ./config/server.js
2    module.exports = ({ env }) => ({
3      host: env('HOST', '0.0.0.0'),
4      port: env.int('PORT', 1337),
5      app: {
6        keys: env.array('APP_KEYS'),
7      },
8    });

The database.js file is where is the database that will be used to store application content is configured. The database's client, hostname, port, etc., are set here.

1    // path: ./config/database.js
2    module.exports = ({ env }) => ({
3      connection: {
4        client: 'sqlite',
5        connection: {
6          filename: env('DATABASE_FILENAME', '.tmp/data.db'),
7        },
8        useNullAsDefault: true,
9        debug: false,
10      },
11    });

You see here that these are the default database settings for Strapi. It is using the SQLite database, as we said earlier.

• The connection field contains database configuration options.
• The settings field contains database settings that are specific to Strapi.
• The connection.client field specifies the database client to create the connection.
• The connection.connection field is used to configure database connection information.
• The connection.connection.filename field defines the path to the database file for sqlite.

In the below section, we will install the PostgreSQL binary.

PostgresDB Setup

We need to set up and install PostgreSQL for Strapi. If you don't have PostgresSQL installed in your machine, go to PostgresSQL downloads and download the binaries for your machine.

• Linux
• macOS
• Solaris
• Windows
• BSD

After installation, start the Postgres server. Make sure you remember the Postgres port, username, and password because we will use them in connecting Strapi to the Postgres.Create a database in PostgreSQL, name it bank because we will be building a bank app to demonstrate further how to connect Strapi to PostgreSQL.

Also, if you want to build the PostgreSQL from the source, download the source code from here and compile it.

Strapi PostgreSQL Setup

To configure our Strapi to use our PostgreSQL, we will add some configurations in our strapi-api/config/database.js file. This will allow us connect Strapi to PostgreSQL.

Open the strapi-api/config/database.js and paste the below code in the file:

1    // strapi-api/config/database.js
2    module.exports = ({ env }) => ({
3      connection: {
4        client: 'postgres',
5        connection: {
6          host: env('DATABASE_HOST', 'localhost'),
7          port: env.int('DATABASE_PORT', 5432),
8          database: env('DATABASE_NAME', 'bank'),
9          user: env('DATABASE_USERNAME', 'postgres'),
10          password: env('DATABASE_PASSWORD', '0000'),
11          schema: env('DATABASE_SCHEMA', 'public'), // Not required
12          ssl: {
13            rejectUnauthorized: env.bool('DATABASE_SSL_SELF', false),
14          },
15        },
16        debug: false,
17      },
18    });

• In the connection object, we set the client to postgres. This client is the PostgreSQL database client to create the connection to the DB.
• The host is the hostname of the PostgreSQL server we set it to localhost.
• The port is set to 5432, and this is the default port of the PostgreSQL server.
• The database is set to the bank, and this is the name of the database we created in the PostgreSQL server.
• The password is the password of our PostgreSQL server.
• The username is the username of our PostgreSQL. It is set to Postgres because it is the username of our PostgreSQL server.
• The schema is the database schema, and it is set to the public here. This schema is used to expose databases to the public.

With this Strapi PostgreSQL setup, our Strapi is using PostgreSQL to persist our API content. Now, start Strapi.

     yarn develop

Strapi will load localhost:1337/admin on our browser. Now register and click on the LET'S START button, this will take you to the admin panel.

If you get a “cannot find module ‘pg’” error, install it by running yarn add pg or npm install --``save pg

Building our Collections

Everything is ready to roll. We have connected our PostgreSQL for Strapi. Now, we start building our collections. We are building a bank app; this is a bank admin app that bankers will use to manage accounts in Strapi, and the DB persistence will be PostgreSQL. Let's write out the core functions of our bank app.

• New accounts can be created in the app.
• Transactions can be carried out, i.e., money can be sent from a user to another user.

We will have two models: Account and Transact.

The Account holds the accounts in the bank, and the Transact holds the transactions carried out.

The Account Model

1    Account {
2      name
3      balance
4    }

The name field will hold the name of the account holder, and the balance will hold the balance of the account holder in Dollars.

The Transact Model

1    Transact {
2      sender
3      receiver
4      amount
5    }

• The sender field holds the name of the account holder that transfers the money.
• The receiver is the beneficiary.
• The amount is the amount the sender sends to the receiver.

Let's begin creating the collections in our Strapi admin. We will start with the Account model.

• Click on the Create First Content Type button and type in "account" for a collection name.Now we add the fields for the account collection.
• Click on the + Add another field button and select Text and type in name, and then click on the + Add another field button to add another field.
• Select Number and on the Number format select float (ex. 3.3333333), then type in balance and click on the Finish button.
• On the Account page that appears click on the Save button that is on the top-right corner of the page.

We generate the Transact collection:

• Click on the + Create new collection type link, a modal will show up, type in transact. Click on the + Add another field button.
• Add the fields: sender, receiver, and amount. The fields sender and receiver will be Text fields while amount will be a Number field with float (ex. 3.333333) Number format.
• After adding them click on the Finish button and the Save button.

Now we have created our collections.

Business Logic

We need to implement our business logic. This business logic will be some Strapi API endpoints to transfer money from a sender to the recipient.

The logic will be this:

• deduct the amount from the sender
• add the amount to the receiver
• add the transaction to transact collection

I want this to be done in the /transfer API, a POST method. A transfer HTTP request will look like this:

1    http://localhost:1337/transfer
2    Method: POST
3    Body:
4    {
5      sender: nnamdi
6      receiver: chidme
7      amount:  10
8    }

So we see that collections can't handle this. This is a single endpoint. The single-type does not do the job for me. I usually find it hard to create a single API endpoint from the Strapi admin panel, so I go to the project source code to add it.

APIs in a Strapi project are kept in the api folder. So we go to our src/api folder, we will see folders created for our APIs: transact and account.

1    |--- src
2    |    |--- api
3    |    |    |--- account
4    |    |    |    |--- content-types
5    |    |    |    |    |--- account
6    |    |    |    |    |    |- schema.json
7    |    |    |    |--- controllers
8    |    |    |    |    |- account.js
9    |    |    |    |--- routes
10    |    |    |    |--- services
11    |    |    |--- transact
12    ...

The routes/[apiName].js file contains the endpoints contained in an API. Strapi provides two different router file structures (core routers and custom routers). Core routers are the common CRUD operations automatically created by Strapi.

The controllers folder contains files that the user can use to customize the endpoints in an API. The user can apply his logic for an endpoint.

• These two things are what we need in other to create our transfer API. So we create a transfer folder in our api folder:

1mkdir src/api/transfer

• Next, we create routes and controllers folders inside the transfer folder.

1mkdir src/api/transfer/routes src/transfer/controllers

• Create a transfer.js file inside the routes folder:

1touch src/api/transfer/routes/transfer.js

Inside it, we configure a custom router with a /transfer endpoint. Then, we will make the handler point to an index function that will export from controllers.

1      module.exports = {
2        routes: [
3          {
4            method: "POST",
5            path: "/transfer",
6            handler: "transfer.index",
7            config: {
8              policies: [],
9              middlewares: [],
10            },
11          },
12        ],
13      };

• Create a transfer.js file in the controllers folder.

1touch src/api/transfer/controllers/transfer.js

Here, we will export an index function. This function will be called when the localhost:1337/transfer HTTP request is made. The function will handle that request. This is where we will apply our business logic of sending money from an account to another beneficiary account.

See the code below:

1      "use strict";
2      const { sanitize } = require("@strapi/utils");
3      /**
4       * A set of functions called "actions" for `transfer`
5       */
6      module.exports = {
7        index: async (ctx) => {
8          const {
9            data: { sender, receiver, amount },
10          } = ctx.request.body;
11          let entity;
12          // deduct amount from sender
13          // add amount to reciver
14          // add the transaction to transact
15          const [senderAcc] = await strapi.entityService.findMany(
16            "api::account.account",
17            {
18              filters: { name: { $eq: sender } },
19            }
20          );
21          const [receiverAcc] = await strapi.entityService.findMany(
22            "api::account.account",
23            {
24              filters: { name: { $eq: receiver } },
25            }
26          );
27          senderAcc.balance = parseFloat(senderAcc.balance) - parseFloat(amount);
28          receiverAcc.balance = parseFloat(receiverAcc.balance) + parseFloat(amount);
29          await strapi.entityService.update("api::account.account", senderAcc.id, {
30            data: senderAcc,
31          });
32          await strapi.entityService.update("api::account.account", receiverAcc.id, {
33            data: receiverAcc,
34          });
35          entity = await strapi.entityService.create("api::transact.transact", {
36            data: { sender, receiver, amount },
37          });
38          const sanitizedEntity = await sanitize.contentAPI.output(entity);
39          return sanitizedEntity;
40        },
41      };

The ctx holds the res and req just like in Expressjs or Koajs. The ctx is an object that contains properties and methods for accessing the incoming message and for responding to the client.

We retrieved the sender, receiver, and amount from data field on the ctx.request.body. Notice we have a strapi object. Yes, it is a Strapi object that is global in a Strapi project. We use the object to access different properties and methods.

Here we are using it to use the entityService API object, which contains methods to access the database. See the functions in it: create, update, find, findOne, etc. They are used to create data in the database, update the database, retrieve values from the database.

So, we retrieved the sender's account details and also the receiver's account details. We then made the transaction, subtracted the amount from the sender's balance, and added the receiver's balance.

Next, we updated the sender's and receiver's balances in the database with their new values.

Then, we created a new transaction in the transact table, and finally, we returned the result of the new transaction.

The sanitizeOutput function removes all private fields from the model and its relations. Save the file and this will restart our Strapi server. You won't see the transfer API appear on the admin panel, and it is a standalone API, not a collection type.

Allow Access

Now, we will allow access to all our APIs.

Click on the Settings item on the sidebar menu, then on the Roles item on the second sidebar menu that appears. On the right section, click on the Public item and scroll down. You will see all the APIs with their handlers. Click on the Select all checkbox in each API and click on the Save button at the top. This will allow public access to all the APIs in our Strapi project:

• account
• transact
• transfer

Seed Data

Now, we seed our data.

1. Click on Content Manager in the left sidebar and click on the Account in the second left sidebar under Collection Types. Click on the + Create new entry button.

2. Add the data:

1name -> nnamdi
2balance -> 2000000

3. Click on the Save button and the Publish button.

4. Add another data:

1name -> chidume
2balance -> 1000000

5. Click on the Save button and the Publish button.

See our PostgreSQL UI, the contents were persisted on PostgreSQL:

Build the Frontend: Bank Admin

Our frontend will be a bank admin app. We will use Nextjs to build the app. So we scaffold our project.

    yarn create next-app strapi-bank

Our app will have two page routes:

• /
• /account/[id]

The index / route will display all the accounts on the system.

The /account/[id] route will display a particular account details. This is a dynamic route, the id can hold any value, its dynamic, and it will be the unique id of an account.

We will have components:

• Header: This will render the Header.
• AccountCard: This component will display a few of the account details in the / route.
• AddAccountDialog: This is a dialog that renders the UI we will use to add new accounts to the system.
• TransactionDialog: This dialog renders UI where transactions will be made, sending money from one Account to another.
• TransactionCard: This component will render the transactions of a user.
• Accounts: This is the page component for the / page. It displays all the accounts in the bank.
• Account: This is the page component for the /account/[id] page.

Our final app will look like this:

OK, so we start creating the components.

     mkdir components
        
     mkdir components/TransactionCard
     touch components/TransactionCard/index.js
     touch components/TransactionCard/TransactionCard.module.css
        
     mkdir components/TransactionDialog
     touch components/TransactionDialog/index.js
        
     mkdir components/AddAccountDialog
     touch components/AddAccountDialog/index.js
        
     mkdir components/AccountCard
     touch components/AccountCard/index.js
     touch components/AccountCard/AccountCard.module.css
        
     mkdir components/Header
     touch components/Header/index.js
     touch components/Header/Header.module.css
        
     touch styles/AccountView.module.css
     mkdir pages/account
     touch pages/account/[id].js

Header

This will be a simple UI, it will display the text Bank Admin. Paste the below code on components/Header/index.js:

1      import { header, headerName } from "./Header.module.css";
2        
3        export default function Header() {
4          return (
5            <section className={header}>
6              <div className={headerName}>Bank Admin</div>
7            </section>
8          );
9        }

AccountCard

This component will be rendered by the Accounts component. It will display a mini detail of an account.

Paste the below code in components/AccountCard/index.js:

1      import styles from "./AccountCard.module.css";
2      import Link from "next/link";
3      export default function AccountCard({ account }) {
4        const {
5          id,
6          attributes: { name, balance, createdAt },
7        } = account;
8        return (
9          <Link href={`account/${id}`}>
10            <div className={styles.account}>
11              <div className={styles.accountdetails}>
12                <div className={styles.accountname}>
13                  <h3>
14                    <span style={{ fontWeight: "100" }}>Account: </span>
15                    {name}
16                  </h3>
17                </div>
18                <div className={styles.accountbalance}>
19                  <span>
20                    <span style={{ fontWeight: "100" }}>Balance($): </span>
21                    {balance}
22                  </span>
23                </div>
24                <div className={styles.accountcreated_at}>
25                  <span>Created: {createdAt}</span>
26                </div>
27              </div>
28            </div>
29          </Link>
30        );
31      }

It receives the account object in its props argument. Next, we destructure id, name, balance, createdAt from the account.attributes object. Yes, id and createdAt are fields set by Strapi in each model content.

So, the AccountCard component renders the details.

TransactionCard

This component will render a specific transaction passed to it. It will display the sender, receiver, and the amount sent. The Account page component renders this component to show the transactions done by an account user—the debits and credits.

Paste the code below in components/TransactionCard/index.js:

1      import styles from "./TransactionCard.module.css";
2      export default function TransactionCard({ transaction }) {
3        const {
4          attributes: { sender, receiver, amount, createdAt },
5        } = transaction;
6        return (
7          <div className={styles.transactionCard}>
8            <div className={styles.transactionCardDetails}>
9              <div className={styles.transactionCardName}>
10                <h4>
11                  <span>Sender: </span>
12                  <span style={{ fontWeight: "bold" }}>{sender}</span>
13                </h4>
14              </div>
15              <div className={styles.transactionCardName}>
16                <h4>
17                  <span>Receiver: </span>
18                  <span style={{ fontWeight: "bold" }}>{receiver}</span>
19                </h4>
20              </div>
21              <div className={styles.transactionCardName}>
22                <h4>
23                  <span>Amount($): </span>
24                  <span style={{ fontWeight: "bold" }}>{amount}</span>
25                </h4>
26              </div>
27              <div className={styles.transactionCardName}>
28                <h4>
29                  <span>Created At: </span>
30                  <span style={{ fontWeight: "bold" }}>{createdAt}</span>
31                </h4>
32              </div>
33            </div>
34          </div>
35        );
36      }

It receives a transaction object in its props. The fields sender, receiver, amount, createdAt are destructured from the transaction.attributes object. These are then rendered by the component.

Accounts

This component is rendered when the index page / route is navigated. This component will make an HTTP request to the Strapi backend to retrieve the list of accounts and render them.

Paste the below code on pages/index.js:

1      import Head from "next/head";
2      import styles from "../styles/Home.module.css";
3      import Header from "../components/Header";
4      import AccountCard from "../components/AccountCard";
5      import { useEffect, useState } from "react";
6      import axios from "axios";
7      import TransactionDialog from "../components/TransactionDialog";
8      import AddAccountDialog from "../components/AddAccountDialog";
9      export default function Home() {
10        const [accounts, setAccounts] = useState([]);
11        const [showTransactModal, setShowTransactModal] = useState(false);
12        const [showAddAccountModal, setShowAddAccountModal] = useState(false);
13        useEffect(() => {
14          async function fetchData() {
15            const { data } = await axios.get("http://localhost:1337/api/accounts");
16            setAccounts(data?.data);
17          }
18          fetchData();
19        }, []);
20        return (
21          <div className={styles.container}>
22            <Head>
23              <title>Bank Admin</title>
24              <link rel="icon" href="/favicon.ico" />
25            </Head>
26            <main className={styles.main}>
27              <Header />
28              <div className={styles.breadcrumb}>
29                <div>
30                  <span style={{ margin: "1px" }}>
31                    <button onClick={() => setShowTransactModal(true)}>
32                      Transact
33                    </button>
34                  </span>
35                  <span style={{ margin: "1px" }}>
36                    <button onClick={() => setShowAddAccountModal(true)}>
37                      Add Account
38                    </button>
39                  </span>
40                </div>
41              </div>
42              <div className={styles.accountcontainer}>
43                <div className={styles.youraccounts}>
44                  <h3>Accounts</h3>
45                </div>
46                <div>
47                  {accounts.map((account, i) => (
48                    <AccountCard key={i} account={account} />
49                  ))}
50                </div>
51              </div>
52              {showAddAccountModal ? (
53                <AddAccountDialog
54                  closeModal={() => setShowAddAccountModal((pV) => !pV)}
55                />
56              ) : null}
57              {showTransactModal ? (
58                <TransactionDialog
59                  closeModal={() => setShowTransactModal((pV) => !pV)}
60                />
61              ) : null}
62            </main>
63          </div>
64        );
65      }

We have three states:

• accounts: is a state that holds the accounts retrieved from the /accounts endpoint.
• showTransactModal: This is a boolean state that toggles the visibility of the TransactionModal.
• showAddAccountModal: this is also a boolean state used to display and remove the AddAccountModal.

The useEffect callback calls the /api/accounts endpoint, and the result is set in the accounts state.

The accounts array is rendered and each account is rendered by the AccountCard component, each account is passed to the AccountCard via its account props.

See that we are conditionally rendering the AddAccountDialog and TransactDialog dialog components. The Transact button toggles the TransactDialog and the Add Account button toggles the AddAccountDialog.

See that we pass a function to each dialog via closeModal props. The function will enable the dialogs to close themselves from their components.

Account

This is a page component that is rendered when the /api/account/[id] route is navigated. This component displays the account details and its transactions. We can delete an account from there also.

Paste the below code in pages/account/[id].js:

1      import styles from "../../styles/AccountView.module.css";
2      import { useRouter } from "next/router";
3      import TransactionCard from "../../components/TransactionCard";
4      import axios from "axios";
5      import { useEffect, useState } from "react";
6      export default function Account() {
7        const router = useRouter();
8        const {
9          query: { id },
10        } = router;
11        const [account, setAccount] = useState();
12        const [transactions, setTransactions] = useState([]);
13        useEffect(() => {
14          async function fetchData() {
15            const { data: AccountData } = await axios.get(
16              "http://localhost:1337/api/accounts/" + id
17            );
18            var { data: transactsData } = await axios.get(
19              "http://localhost:1337/api/transacts"
20            );
21            transactsData = transactsData?.data?.filter(
22              (tD) =>
23                tD.attributes?.sender == AccountData?.data?.attributes?.name ||
24                tD.attributes?.receiver == AccountData?.data?.attributes?.name
25            );
26            setAccount(AccountData?.data);
27            setTransactions(transactsData);
28          }
29          fetchData();
30        }, [id]);
31        async function deleteAccount() {
32          if (confirm("Do you really want to delete this account?")) {
33            await axios.delete("http://localhost:1337/api/accounts/" + id);
34            router.push("/");
35          }
36        }
37        return (
38          <div className={styles.accountviewcontainer}>
39            <div className={styles.accountviewmain}>
40              <div style={{ width: "100%" }}>
41                <div className={styles.accountviewname}>
42                  <h1>{account?.attributes?.name}</h1>
43                </div>
44                <div className={styles.accountviewminidet}>
45                  <div>
46                    <span style={{ marginRight: "4px", color: "rgb(142 142 142)" }}>
47                      Balance($):
48                    </span>
49                    <span style={{ fontWeight: "600" }}>
50                      {account?.attributes?.balance}
51                    </span>
52                  </div>
53                  <div style={{ padding: "14px 0" }}>
54                    <span>
55                      <button onClick={deleteAccount} className="btn-danger">
56                        Delete
57                      </button>
58                    </span>
59                  </div>
60                </div>
61                <div className={styles.accountviewtransactionscont}>
62                  <div className={styles.accountviewtransactions}>
63                    <h2>Transactions</h2>
64                  </div>
65                  <div className={styles.accountviewtransactionslist}>
66                    {!transactions || transactions?.length <= 0
67                      ? "No transactions yet."
68                      : transactions?.map((transaction, i) => (
69                          <TransactionCard key={i} transaction={transaction} />
70                        ))}
71                  </div>
72                </div>
73              </div>
74            </div>
75          </div>
76        );
77      }

The component retrieves the id from the URL. We have states account and transactions, that hold the account and its transactions respectively.

The useEffect hook callback calls the /api/accounts/" + id endpoint with the id value to get the account via its id. Next, it calls the /api/transacts endpoint to retrieve the transactions and filter out the transaction made or received by the current account user. The result is set in the transactions state while the account details are set in the account state.

The UI then displays the account details and their transactions.

There is a Delete button that when clicked deletes the current account user. It does this by calling the endpoint /api/accounts/" + id over the DELETE HTTP method with the account's id. This makes Strapi delete the account.

AddAccountDialog

This component is a dialog that we use to add a new account.

Paste the below code in components/AddAccountDialog/index.js:

1      import { useState } from "react";
2      import axios from "axios";
3      export default function AddAccountDialog({ closeModal }) {
4        const [disable, setDisable] = useState(false);
5        async function addAccount(e) {
6          setDisable(true);
7          const accountName = e.target.accountName.value;
8          const accountBalance = e.target.accountBalance.value;
9          // add account
10          await axios.post("http://localhost:1337/api/accounts", {
11            data: {
12              name: accountName,
13              balance: parseFloat(accountBalance),
14            },
15          });
16          setDisable(false);
17          closeModal();
18          location.reload();
19        }
20        return (
21          <div className="modal">
22            <div className="modal-backdrop" onClick={closeModal}></div>
23            <div className="modal-content">
24              <div className="modal-header">
25                <h3>Add New Account</h3>
26                <span
27                  style={{ padding: "10px", cursor: "pointer" }}
28                  onClick={closeModal}
29                >
30                  X
31                </span>
32              </div>
33              <form onSubmit={addAccount}>
34                <div className="modal-body content">
35                  <div style={{ display: "flex", flexWrap: "wrap" }}>
36                    <div className="inputField">
37                      <div className="label">
38                        <label>Name</label>
39                      </div>
40                      <div>
41                        <input id="accountName" name="accountName" type="text" />
42                      </div>
43                    </div>
44                    <div className="inputField">
45                      <div className="label">
46                        <label>Balance($):</label>
47                      </div>
48                      <div>
49                        <input
50                          id="accountBalance"
51                          name="accountBalance"
52                          type="text"
53                        />
54                      </div>
55                    </div>
56                  </div>
57                </div>
58                <div className="modal-footer">
59                  <button
60                    disabled={disable}
61                    className="btn-danger"
62                    onClick={closeModal}
63                  >
64                    Cancel
65                  </button>
66                  <button disabled={disable} className="btn" type="submit">
67                    Add Account
68                  </button>
69                </div>
70              </form>
71            </div>
72          </div>
73        );
74      }

We have input boxes to type in the account name and its initial balance deposited. The Add Account button when clicked calls the addAccount function. This function retrieves the account name and balance and calls the /api/accounts endpoint via the POST HTTP with the payload: account name and balance. This creates a new account with this payload.

TransactionDialog

This component is where we send money from one account to another.

Paste the below code to components/TransactionDialog/index.js:

1      import { useState } from "react";
2      import axios from "axios";
3    
4      export default function TransactionDialog({ closeModal }) {
5        const [disable, setDisable] = useState(false);
6        async function transact(e) {
7          setDisable(true);
8          const sender = e.target.sender.value;
9          const receiver = e.target.receiver.value;
10          const amount = e.target.amount.value;
11          await axios.post("http://localhost:1337/api/transfer", {
12            data: { sender, receiver, amount },
13          });
14          setDisable(false);
15          closeModal();
16          location.reload();
17        }
18        return (
19          <div className="modal">
20            <div className="modal-backdrop" onClick={closeModal}></div>
21            <div className="modal-content">
22              <div className="modal-header">
23                <h3>Transaction</h3>
24                <span
25                  style={{ padding: "10px", cursor: "pointer" }}
26                  onClick={closeModal}
27                >
28                  X
29                </span>
30              </div>
31              <form onSubmit={transact}>
32                <div className="modal-body content">
33                  <div style={{ display: "flex", flexWrap: "wrap" }}>
34                    <div className="inputField">
35                      <div className="label">
36                        <label>Sender</label>
37                      </div>
38                      <div>
39                        <input id="sender" type="text" />
40                      </div>
41                    </div>
42                    <div className="inputField">
43                      <div className="label">
44                        <label>Receiver</label>
45                      </div>
46                      <div>
47                        <input id="receiver" type="text" />
48                      </div>
49                    </div>
50                    <div className="inputField">
51                      <div className="label">
52                        <label>Amount($)</label>
53                      </div>
54                      <div>
55                        <input id="number" name="amount" type="text" />
56                      </div>
57                    </div>
58                  </div>
59                </div>
60                <div className="modal-footer">
61                  <button
62                    disabled={disable}
63                    className="btn-danger"
64                    onClick={closeModal}
65                  >
66                    Cancel
67                  </button>
68                  <button disabled={disable} className="btn" type="submit">
69                    Transact
70                  </button>
71                </div>
72              </form>
73            </div>
74          </div>
75        );
76      }

The input boxes collect the sender and receiver names and the amount to transfer.

The transact function does the job. It retrieves the sender, receiver, and amount values from the input boxes, and then calls the endpoint /api/transfer via HTTP POST passing in the sender, receiver, and amount as payload. The /api/transfer endpoint will then transfer the amount from the sender to the receiver.

We are done building our components, let's test it.

Test

Add new account

Perform a transaction

Delete an account

Source Code

• Frontend code
• Backend code

Conclusion

Strapi is great! It's awesome! You see how we were able to integrate PostgreSQL into our Strapi project seamlessly.

We started by introducing Strapi and the goodies it brings to software development. Later on, we learned about the default DB it uses for data persistence.

Next, we introduced PostgreSQL and showed where to download and install it. We learned how to configure a Strapi project to use PostgreSQL as the database to store its application content.

We went further to build a bank app to demonstrate how to use PostgreSQL in Strapi to the fullest.

Building a Content Structure in Strapi

After setting up Strapi with PostgreSQL, you can start building your content structure. The next steps include creating content types, defining relationships, and using the Strapi Content Manager to manage your data.

Create Content Types and Fields

In the Strapi admin panel, you can create new content types that define the structure of your data. These content types represent the various entities in your application, such as articles, users, or products. For a deeper understanding, refer to this guide on content modeling in Strapi.

To create a content type:

1. Navigate to the Content-Type Builder in the admin panel.
2. Click on "Create new collection type" or "Create new single type".
3. Provide a name for your content type and proceed to add fields.

When adding fields, choose from various data types like text, number, date, or boolean. For instance, you might consider integrating rich text with Strapi for more advanced content editing.

Define Relationships between Content Types

Strapi allows you to define relationships between content types, enabling complex data structures. For example, you might have an article content type that relates to an author content type. To define a relationship:

1. In the Content-Type Builder, select the content type you want to modify.
2. Add a new field and select "Relation" as the field type.
3. Specify the nature of the relationship (one-to-one, one-to-many, or many-to-many) and choose the related content type.

These relationships are stored in your PostgreSQL database, which supports relational data. Additionally, you can utilize the Dynamic Zone feature to create flexible and reusable content blocks.

Use the Strapi Content Manager

The Content Manager in Strapi provides an interface for adding, editing, and managing your content. With your content types and relationships defined, you can:

• Create new entries for your content types.
• Edit existing content.
• Manage media files associated with your content.

The Content Manager simplifies handling your data without writing any code. All changes are saved directly to your PostgreSQL database.

Always consider using Strapi in your projects. It is straightforward and highly configurable.