I'm a frontend developer — mostly React, mostly browser-side. But over time, I've realized that understanding the database side of things isn't just “backend stuff” — it's essential if you want to build better apps, debug faster, or go fullstack when needed.

This is not a career switch. I'm not trying to become a backend expert or a database admin. I just want to get decent at PostgreSQL — enough to design schemas, write solid queries, and understand what’s happening under the hood.

For the next 30 days, I’ll document my learning process. No filler. Just focused, practical lessons from a frontend developer’s perspective.

Let’s see how far I can get.

Day 1 starts now.

Installing postgresql on my machine

I'm going to use docker for my postgresql install. If you don't have docker installed on your machine, just figure it out!

Following command will fetch official postgres image and create a database named travel_mania inside travel_mania_container.

docker run --name travel_mania_container \
  -e POSTGRES_USER=ugur \
  -e POSTGRES_PASSWORD=ugur1234 \
  -e POSTGRES_DB=travel_mania \
  -p 5432:5432 \
  -d postgres

--name: container name

-e POSTGRES_USER : user for accessing database

-e POSTGRES_PASSWORD : password for accessing database

-p 5432:5432 : exposed port number to access to instance (host:container) - 5432 default port for pg

-d : detached mode (you can use terminal, process runs in the background)

postgres image name (official postgres image)

List running docker instances and see that our new container is there:

docker ps

# Result:
# CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS                                         NAMES
# 2909bc1cba7d   postgres   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp   travel_mania_container

Connect to docker container and access to our database using psql.

psql is the PostgreSQL interactive terminal (CLI tool) — it's the most commonly used command-line interface to interact with a PostgreSQL database.

Connect to container and run psql there:

docker exec -it travel_mania_container psql -U ugur -d travel_mania

Result:

# psql (17.5 (Debian 17.5-1.pgdg120+1))
# Type "help" for help.
#
# travel_mania=#

You can quit by typing \q in psql.

Creating first table

Data stored in sql within tables. Table is simply an excel sheet on steroids.

CREATE TABLE users (
  -- `SERIAL` is PostgreSQL's way of creating auto-incrementing IDs (like auto-generated keys)
  -- `PRIMARY KEY` ensures each user has a unique identifier
  id SERIAL PRIMARY KEY,
  -- `UNIQUE` constraints prevent duplicate emails/usernames
  -- `NOT NULL` makes fields required
  -- `VARCHAR(n)` is for text with a maximum length
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  -- `TIMESTAMP` stores date and time info
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting and querying the data

-- Insert a few test users
INSERT INTO users
  (email, username, first_name, last_name)
VALUES
  ('john@example.com', 'john_traveler', 'John', 'Doe'),
  ('sara@example.com', 'sara_explorer', 'Sara', 'Smith');

-- Query all users
SELECT * FROM users;

-- Query specific fields
SELECT username, first_name, email FROM users;

Deleting data

DELETE FROM users
WHERE id = 1;

GUI Database Client

Until now, we've used the psql CLI tool to connect to our database, but we can also use GUI tools. They provide a much more intuitive experience and are mostly used in day-to-day professional work.

I prefer DBeaver as my database client, but you can use anything you like such as pgAdmin, TablePlus, etc.

End of Day 1

Tomorrow we will learn about importing bulk data into database. After that we will work on joining tables and working with large data.