InnoMarts logo
InnoMarts
Fast websites
← Back to course

PostgreSQL and Applications: Connecting Your Database to Real Projects

PostgreSQL and Applications: Connecting Your Database to Real Projects

Welcome back.

In the previous lesson, you built a small shop database.

You created:

customers
categories
products
orders
order_items

You used:

Very good.

Now PostgreSQL is no longer just a mysterious terminal creature.

It is becoming a real tool.

But there is still one very important question:

How does an application actually connect to PostgreSQL?

Because in real projects, users do not open psql and type SQL manually.

Usually.

A backend application connects to the database.

Then the application reads data.

Writes data.

Updates data.

Deletes data.

Hopefully not all data.

That would be a dramatic Tuesday.

Today we will learn how applications connect to PostgreSQL.

This lesson is not about one specific framework.

The ideas apply to:

Django
Spring Boot
Node.js
Express
NestJS
Laravel
Rails
Go applications
any backend that needs PostgreSQL

Frameworks change.

Connection principles stay.

Very useful.

Very database.

Very “please do not put your password directly in the code”.

What You Will Learn

In this lesson, you will learn:

By the end of this lesson, you will understand the bridge between PostgreSQL and backend applications.

Because a database without an application is useful.

But a database connected to an application becomes powerful.

Like a warehouse connected to a shop.

Or like a coffee machine connected to a tired developer.

Important infrastructure.

The Basic Idea

An application needs connection information to talk to PostgreSQL.

Usually it needs:

host
port
database name
username
password

Example:

host: localhost
port: 5432
database: shop_db
username: shop_user
password: strong_password

The application uses these values to open a connection.

Then it can send SQL queries.

For example:

SELECT * FROM products;

The application sends the query.

PostgreSQL runs it.

PostgreSQL returns the result.

The application uses the result.

Maybe it shows products on a website.

Maybe it creates an API response.

Maybe it generates a report.

Maybe it breaks because someone forgot a semicolon.

Classic.

PostgreSQL Host

The host tells the application where PostgreSQL is running.

For local development, the host is often:

localhost

or:

127.0.0.1

This means:

PostgreSQL is running on the same machine as the application.

Example:

host=localhost

In production, PostgreSQL may run on another server.

Example:

host=db.example.com

or:

host=10.0.0.5

or inside Docker:

host=postgres

The host depends on where the database lives.

The application needs the correct address.

Otherwise it will knock on the wrong door.

PostgreSQL will not answer from a house where it does not live.

Very reasonable.

PostgreSQL Port

PostgreSQL usually listens on port:

5432

So a common connection uses:

port=5432

A port is like a door number on a server.

The server may run many services.

PostgreSQL uses port 5432 by default.

If you changed the port, you must use the new one.

But for beginners, most of the time:

5432

is the answer.

A rare moment of simplicity.

Enjoy it.

Database Name

A PostgreSQL server can contain many databases.

For example:

learning_postgresql
shop_db
portfolio_db
blog_db

Your application must know which database to use.

Example:

database=shop_db

If your application connects to the wrong database, strange things happen.

Tables may be missing.

Data may look old.

Your brain may start debugging the wrong universe.

Always check the database name.

Small typo.

Big headache.

Username and Password

PostgreSQL uses users and passwords to control access.

Example:

username=shop_user
password=strong_password

The user determines what the application can do.

A user can have permissions like:

connect to a database
read tables
insert rows
update rows
delete rows
create tables

Do not give every application full superuser power.

That is like giving a child a chainsaw because they wanted to cut paper.

Technically effective.

Deeply unwise.

Do Not Use the postgres User in Applications

PostgreSQL often has a default administrative user called:

postgres

This user is powerful.

Very powerful.

Too powerful for normal applications.

Do not use postgres as your application user.

Instead, create a specific user for your application.

Example:

shop_user
blog_user
app_user
portfolio_user

Why?

Because if your application has a bug or gets attacked, you want to limit the damage.

An application user should have only the permissions it needs.

Not the keys to the whole kingdom.

The database kingdom has dragons.

And invoices.

Create a Database for an Application

Open PostgreSQL as the postgres user:

sudo -iu postgres psql

Create a database:

CREATE DATABASE shop_db;

Create a user:

CREATE USER shop_user WITH PASSWORD 'change_this_password';

Give the user access to the database:

GRANT CONNECT ON DATABASE shop_db TO shop_user;

Now connect to the database:

\c shop_db

Give permissions on the public schema:

GRANT USAGE ON SCHEMA public TO shop_user;

Allow the user to work with tables:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_user;

Allow the user to use sequences.

This is important for SERIAL IDs.

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO shop_user;

For future tables, you can set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO shop_user;

This is already much better than using the postgres superuser.

The application gets access.

But not unlimited power.

Very civilized.

Test the New User

Exit PostgreSQL:

\q

Now try connecting as the new user:

psql -h localhost -U shop_user -d shop_db

PostgreSQL will ask for the password.

Enter the password you created.

If connection works, you will enter psql.

Then test:

SELECT current_user;

You should see:

shop_user

Good.

Now your application can use this user too.

The database door opens.

But only with the correct key.

Very secure.

Very adult.

What Is a Connection String?

A connection string puts all connection information in one line.

Common PostgreSQL format:

postgresql://username:password@host:port/database

Example:

postgresql://shop_user:change_this_password@localhost:5432/shop_db

This contains:

username: shop_user
password: change_this_password
host: localhost
port: 5432
database: shop_db

Many tools and frameworks use connection strings.

They are compact.

Useful.

And dangerous if you paste them everywhere.

Because they contain passwords.

Treat connection strings like keys.

Do not publish them.

Do not commit them to GitHub.

Do not send them to random people.

Do not put them in screenshots.

Your future self will thank you.

Security people will also stop screaming.

Environment Variables

Applications usually read database settings from environment variables.

Environment variables are values stored outside the code.

Example:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=shop_db
DB_USER=shop_user
DB_PASSWORD=change_this_password

Or:

DATABASE_URL=postgresql://shop_user:change_this_password@localhost:5432/shop_db

Why use environment variables?

Because code should not contain secrets.

Bad:

password directly inside source code

Good:

password in environment variable

This lets you use different settings for:

local development
testing
production

Same code.

Different configuration.

Very useful.

Very professional.

Very “I have learned from painful mistakes”.

The .env File

During local development, many projects use a .env file.

Example:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=shop_db
DB_USER=shop_user
DB_PASSWORD=change_this_password

Or:

DATABASE_URL=postgresql://shop_user:change_this_password@localhost:5432/shop_db

The application reads this file when it starts.

Important:

Do not commit .env files with real passwords.

Add .env to .gitignore.

Example .gitignore:

.env
.env.local
.env.production

You can create a safe example file:

.env.example

Example:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=shop_db
DB_USER=shop_user
DB_PASSWORD=your_password_here

This shows other developers what variables are needed.

But it does not expose real secrets.

Good practice.

Very good.

Gold star.

Database gold star.

Example: Generic Application Settings

Many applications need settings like this:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=shop_db
DB_USER=shop_user
DB_PASSWORD=change_this_password

Then the application builds a connection.

Conceptually:

Connect to DB_HOST on DB_PORT.
Use DB_NAME.
Login with DB_USER and DB_PASSWORD.

Some frameworks prefer one URL:

DATABASE_URL=postgresql://shop_user:change_this_password@localhost:5432/shop_db

Both styles are common.

Which one you use depends on the framework.

But the idea is the same.

Your app needs directions to the database.

Without directions, it wanders around like a lost tourist with a broken GPS.

Example: Django Settings

In Django, database settings often look like this:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "shop_db",
        "USER": "shop_user",
        "PASSWORD": "change_this_password",
        "HOST": "localhost",
        "PORT": "5432",
    }
}

But putting the password directly here is not good.

Better idea:

import os

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ.get("DB_NAME"),
        "USER": os.environ.get("DB_USER"),
        "PASSWORD": os.environ.get("DB_PASSWORD"),
        "HOST": os.environ.get("DB_HOST", "localhost"),
        "PORT": os.environ.get("DB_PORT", "5432"),
    }
}

Now the password comes from environment variables.

Much better.

Django gets the connection details.

Your code stays safer.

Everyone is less nervous.

Except maybe the CSS developer.

But that is a different course.

Example: Spring Boot Settings

In Spring Boot, application.properties can look like this:

spring.datasource.url=jdbc:postgresql://localhost:5432/shop_db
spring.datasource.username=shop_user
spring.datasource.password=change_this_password

Better with environment variables:

spring.datasource.url=${DB_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

Then your environment can contain:

DB_URL=jdbc:postgresql://localhost:5432/shop_db
DB_USER=shop_user
DB_PASSWORD=change_this_password

This is much safer than hardcoding secrets.

In real projects, the database password should not live in your Git repository.

Git remembers everything.

Like an elephant.

But with commit history.

Example: Node.js Connection

A Node.js application may use a connection string:

DATABASE_URL=postgresql://shop_user:change_this_password@localhost:5432/shop_db

Then the app reads it.

Conceptually:

const connectionString = process.env.DATABASE_URL;

Many Node.js libraries support this style.

The important idea is not the exact library.

The important idea is:

Read secrets from environment variables.
Do not hardcode them.

This rule follows you across languages.

Python.

Java.

JavaScript.

Go.

PHP.

Everywhere.

Secrets in code are like open windows in winter.

Bad idea.

Expensive later.

Local Development vs Production

Local development usually means:

application and database run on your computer
host = localhost
port = 5432

Production usually means:

application runs on a server
database may run on another server
host is not localhost
password is stronger
security matters more
backups matter more
monitoring matters more

In local development, mistakes are annoying.

In production, mistakes become invoices.

Very motivational.

That is why configuration should be flexible.

You may have:

local database
test database
production database

Each environment has different values.

Same application code.

Different environment variables.

This is normal.

This is professional.

This is how we avoid changing code just to connect somewhere else.

Docker and PostgreSQL Host Names

If your application and PostgreSQL run inside Docker Compose, the host is often the service name.

Example:

services:
  app:
    environment:
      DB_HOST: postgres
      DB_PORT: 5432

  postgres:
    image: postgres

Inside Docker, the app may connect to:

postgres

not:

localhost

Why?

Because inside the app container, localhost means the app container itself.

Not the PostgreSQL container.

This is a very common mistake.

Very common.

Very painful.

Very Docker.

Simple rule:

From your computer: localhost may work.
From another container: use the service name.

So in Docker Compose, the database host might be:

postgres

or:

db

depending on your service name.

Docker networking is powerful.

And occasionally spicy.

Common Connection Errors

Connection Refused

Example error:

connection refused

This usually means:

PostgreSQL is not running.
Wrong host.
Wrong port.
PostgreSQL is not listening there.
Firewall blocks connection.

Check PostgreSQL status:

systemctl status postgresql

On some systems, the service name may differ.

You can also try:

psql -h localhost -U shop_user -d shop_db

If psql cannot connect, your application probably cannot connect either.

Do not blame the framework first.

First check the database.

Frameworks are guilty often.

But not always.

Password Authentication Failed

Example:

password authentication failed for user

This usually means:

wrong username
wrong password
user does not exist
environment variable has wrong value

Check the username:

SELECT current_user;

Check that the user exists:

\du

If needed, reset the password:

ALTER USER shop_user WITH PASSWORD 'new_password_here';

Then update your environment variable.

Do not update only your memory.

Applications cannot read your memory.

Thankfully.

Database Does Not Exist

Example:

database "shop_db" does not exist

This means exactly what it says.

Usually.

Check databases:

\l

Create the database if needed:

CREATE DATABASE shop_db;

Or fix the database name in your configuration.

This error is often just a typo.

A tiny typo with a big attitude.

Permission Denied

Example:

permission denied for table products

This means the user connected successfully.

But it does not have permission to do something.

Give permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_user;

For sequences:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO shop_user;

If the app can read but cannot insert rows with SERIAL IDs, sequence permissions may be the problem.

PostgreSQL permissions are powerful.

Also slightly annoying.

Like a serious security guard with paperwork.

Table Does Not Exist

Example:

relation "products" does not exist

Possible reasons:

You connected to the wrong database.
The table was not created.
The table is in another schema.
The migration did not run.
The table name is different.

Check tables:

\dt

Check current database:

SELECT current_database();

This error often happens when your application connects to one database, but you created tables in another.

Classic.

Painful.

Very educational.

Basic Security Rules

Here are simple rules.

Not advanced security.

Basic survival.

Do Not Hardcode Passwords

Bad:

password inside source code

Good:

password in environment variable

Code can be shared.

Secrets should not be shared.

Very simple.

Very ignored.

Very dangerous.

Do Not Commit .env

Add .env to .gitignore.

.env
.env.local
.env.production

If you accidentally commit a real password, change it.

Do not just delete the file from the latest commit and pretend nothing happened.

Git history remembers.

Git is not your friend in this situation.

Git is a witness.

Use a Separate Database User

Do not use the postgres superuser in your application.

Create an app-specific user:

shop_user

Give only needed permissions.

This limits damage if something goes wrong.

And something eventually goes wrong.

That is not pessimism.

That is software development.

Use Strong Passwords

Do not use:

password
123456
postgres
admin
qwerty

These are not passwords.

These are invitations.

Use strong passwords.

Especially in production.

Your database deserves better.

Keep Production Database Private

A production PostgreSQL database should usually not be open to the whole internet.

Better:

private network
firewall rules
limited IP access
secure hosting configuration

If the database is publicly reachable, security becomes much more serious.

Databases like privacy.

So do users.

So do lawyers.

Application Migrations

Many frameworks use migrations.

A migration is a controlled database change.

Examples:

create table
add column
change column type
create index
drop table

Instead of manually changing the database every time, the framework tracks changes.

Django has migrations.

Spring Boot often uses tools like Flyway or Liquibase.

Node.js ORMs also have migration tools.

The idea is:

Database structure should be versioned.

This matters because applications and databases must match.

If your code expects a column called email, but the database does not have it, the app breaks.

Very confidently.

Migrations help keep structure under control.

They are not magic.

But they are better than random manual changes at midnight.

ORM vs Raw SQL

Many applications use an ORM.

ORM means Object-Relational Mapping.

Examples:

Django ORM
Hibernate / JPA
Prisma
TypeORM
Sequelize
SQLAlchemy

An ORM lets you work with database rows as objects.

Example idea:

Product object
Customer object
Order object

The ORM generates SQL.

This is convenient.

But you still need to understand SQL.

Why?

Because when something becomes slow or broken, the ORM will not save your soul.

It will generate SQL.

PostgreSQL will execute SQL.

Performance problems are still SQL problems.

So learning SQL is not wasted.

Even if you use an ORM.

Actually, especially if you use an ORM.

Because now you know what is happening under the blanket.

And sometimes under the blanket there is a monster query.

Common Application Flow

A typical backend flow looks like this:

User opens website.
Frontend sends request to backend.
Backend connects to PostgreSQL.
Backend runs query.
PostgreSQL returns data.
Backend returns JSON or HTML.
Frontend shows result.

Example:

GET /products

Backend query:

SELECT
  p.id,
  p.name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.name;

Backend returns JSON:

[
  {
    "id": 1,
    "name": "Laptop",
    "price": 900.00,
    "category": "Electronics"
  }
]

This is how PostgreSQL becomes part of an application.

The user never sees SQL.

But SQL does the work.

Like a backstage worker.

Quiet.

Important.

Slightly underappreciated.

Connection Pooling

Opening a database connection takes time.

If every request opens a new connection and closes it immediately, performance can suffer.

Many applications use a connection pool.

A connection pool keeps several database connections open and reuses them.

Simple idea:

Do not create a new connection every time.
Reuse existing connections.

Most frameworks handle this for you.

But it is good to know the idea.

Because production systems care about connections.

Too many connections can overload PostgreSQL.

Too few connections can slow the app.

Balance matters.

Like coffee.

Too little is bad.

Too much and you can hear colors.

Practice

Create a new database:

CREATE DATABASE app_demo_db;

Create a new user:

CREATE USER app_demo_user WITH PASSWORD 'change_this_password';

Grant connection:

GRANT CONNECT ON DATABASE app_demo_db TO app_demo_user;

Connect to the database:

\c app_demo_db

Create a simple table:

CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Grant table permissions:

GRANT USAGE ON SCHEMA public TO app_demo_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_demo_user;

Grant sequence permissions:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_demo_user;

Insert a test row:

INSERT INTO messages (title, content)
VALUES ('Hello App', 'This row could be read by a backend application.');

Test connection:

psql -h localhost -U app_demo_user -d app_demo_db

Then run:

SELECT * FROM messages;

If this works, you have a database, a user, permissions, and a working connection.

That is the foundation of application integration.

Not flashy.

Very important.

Like good wiring.

Nobody sees it.

Everyone complains when it fails.

Mini Challenge

Create a database for a blog application.

Requirements:

database name: blog_app_db
user name: blog_app_user
tables: authors, posts

Rules:

Create the database:

CREATE DATABASE blog_app_db;

Create the user:

CREATE USER blog_app_user WITH PASSWORD 'change_this_password';

Grant connection:

GRANT CONNECT ON DATABASE blog_app_db TO blog_app_user;

Connect:

\c blog_app_db

Create tables:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title VARCHAR(150) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Grant permissions:

GRANT USAGE ON SCHEMA public TO blog_app_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_app_user;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO blog_app_user;

Create useful indexes:

CREATE INDEX idx_posts_author_id
ON posts(author_id);

CREATE INDEX idx_posts_created_at
ON posts(created_at);

Now create a .env.example file for the application:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=blog_app_db
DB_USER=blog_app_user
DB_PASSWORD=your_password_here

Or:

DATABASE_URL=postgresql://blog_app_user:your_password_here@localhost:5432/blog_app_db

Then answer:

Which values are safe to commit?
Which values should stay secret?
Why should .env be ignored by Git?
Why should the app not use the postgres user?

This is practical.

This is real.

This is the boring foundation that prevents exciting disasters.

And in databases, boring is often good.

Very good.

Common Mistakes

Using postgres User Everywhere

Do not do this.

The postgres user is for administration.

Your app should have its own user.

Give it only the permissions it needs.

This is basic security.

Basic security is not optional.

It is like wearing shoes in a workshop.

Hardcoding Secrets

Bad:

spring.datasource.password=my_real_password

Bad:

"PASSWORD": "my_real_password"

Bad:

const password = "my_real_password";

Use environment variables.

Your code is not a vault.

Forgetting Sequence Permissions

If your app can insert rows but fails with sequence permission errors, remember:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

SERIAL uses sequences.

Sequences need permissions.

PostgreSQL is precise.

Sometimes painfully precise.

Connecting to the Wrong Database

Always check:

SELECT current_database();

Many bugs are just:

I created the table in one database.
My app connects to another database.

This is painful.

But very common.

Using localhost Incorrectly in Docker

Inside Docker Compose, localhost from the app container usually means the app container.

Not the database container.

Use the database service name.

Example:

postgres

or:

db

Docker is not wrong.

It is just very literal.

Like PostgreSQL.

They probably get along.

Summary

Today you learned:

This is an important lesson.

You now understand how PostgreSQL connects to real applications.

This is where database knowledge becomes backend power.

The database stores the truth.

The application uses the truth.

The user sees the result.

And somewhere in the middle, a developer hopes the environment variables are correct.

Very real.

Very professional.

Very Monday.

Next Lesson

In the next lesson, we will talk about backups, restore, and basic database maintenance.

Because creating a database is good.

Using a database is better.

But being able to recover your data after something goes wrong is priceless.

Backups are boring.

Until the day they save your life.

Then they become beautiful.