Skip to content

About

Data Flow project using FastAPI,K8s,Terraform,AWS S3,Apache NiFi,Redis,KeyCloak,Swagger,Docker,Postgresql

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository
BranchesTags

Latest commit

 

History

11 Commits

Folders and files

Repository files navigation

Running PostgreSQL with Docker Desktop on macOS

This guide explains how to quickly set up a PostgreSQL database using Docker Desktop on your Mac.

Prerequisites

Steps

1. Pull the PostgreSQL Docker Image

Open your terminal and run:

docker pull postgres:latest

2. Start a PostgreSQL Container

Run the following command to start a new PostgreSQL container:

docker run --name my-postgres -e POSTGRES_PASSWORD=password -e POSTGRES_USER=myuser -e POSTGRES_DB=mydatabase -p 5432:5432 -d postgres:latest
  • --name my-postgres: Names your container (change as desired)
  • -e POSTGRES_PASSWORD=password: Sets the database password (change for security)
  • -e POSTGRES_USER=myuser: Sets the database user (change as needed)
  • -e POSTGRES_DB=mydatabase: Sets the default database name
  • -p 5432:5432: Maps port 5432 on your Mac to the container
  • -d: Runs the container in detached mode
  • postgres:latest: Uses the latest PostgreSQL image

3. Verify the Container is Running

Check running containers:

docker ps

You should see my-postgres listed.

4. Connect to PostgreSQL

You can connect using any PostgreSQL client (e.g., Postico, DBeaver, or psql).

Connection details:

  • Host: localhost
  • Port: 5432
  • User: myuser
  • Password: password
  • Database: mydatabase

Or connect from the terminal using psql (if installed):

psql -h localhost -U myuser -d mydatabase

5. Stopping and Removing the Container

To stop the container:

docker stop my-postgres

To remove the container:

docker rm my-postgres

For more options and details, see the official PostgreSQL Docker image documentation.

The error message zsh: command not found: psql means that the psql command-line tool (the PostgreSQL client) is not installed on your Mac. While your PostgreSQL server is running in Docker, you need a client to connect to it from your terminal.

Here’s how you can install psql on macOS:

Option 1: Install via Homebrew (Recommended)

If you have Homebrew installed, run:

brew install libpq
brew link --force libpq
  • libpq provides the psql client.
  • The brew link --force libpq command makes psql available in your terminal.

Option 2: Use Postgres.app (GUI + CLI)

  • Download and install Postgres.app.
  • After installation, add its binaries to your PATH (instructions are on their website).

Option 3: Use Docker to Run psql

If you don’t want to install anything else, you can use the psql client inside another temporary Docker container:

docker run -it --rm --network=host postgres:latest psql -h localhost -U myuser -d mydatabase
  • This will prompt you for the password (password).

Summary:

  • To use psql from your terminal, install it via Homebrew or Postgres.app.
  • Alternatively, use Docker to run psql without installing anything extra.

Here are several ways to open a psql container to access your PostgreSQL database:

Method 1: Connect to your existing PostgreSQL container

# Connect to your running postgres container
docker exec -it api-postgres-1 psql -U myuser -d mydatabase

Method 2: Use a temporary psql container

# Run a temporary container with psql client
docker run -it --rm --network api_app-network postgres:latest psql -h postgres -U myuser -d mydatabase

Method 3: Connect using the container name directly

# If your container is named differently, check first:
docker ps

# Then connect (replace container name if different):
docker exec -it api-postgres-1 bash
# Once inside the container:
psql -U myuser -d mydatabase

Method 4: One-liner to get into psql directly

# Direct connection to your database
docker exec -it api-postgres-1 psql -U myuser -d mydatabase -c "\l"

Method 5: Connect from outside Docker (if you have psql installed)

# If you have psql installed on your Mac
psql -h localhost -p 5432 -U myuser -d mydatabase

Quick Database Inspection Commands

Once you're in psql, try these commands:

-- List all databases
\l

-- Connect to your database
\c mydatabase

-- List all tables
\dt

-- Describe the cars table structure
\d cars

-- Count rows in cars table
SELECT COUNT(*) FROM cars;

-- Show first 5 cars
SELECT * FROM cars LIMIT 5;

-- Exit psql
\q

Most Common Method

For your setup, this should work:

docker exec -it api-postgres-1 psql -U myuser -d mydatabase

When prompted, enter the password: password

This connects you directly to the PostgreSQL database that your FastAPI is using! 🚀

Importing Car Data with NiFi and PostgreSQL

1. PostgreSQL Table Structure

Run this SQL to create the table:

CREATE TABLE cars (
    brand varchar(255),
    price double precision,
    body varchar(255),
    mileage integer,
    engv double precision,
    engtype varchar(255),
    registration varchar(10),
    year integer,
    model varchar(255),
    drive varchar(255)
);

2. CSV Structure (No Header Row)

The CSV file should have columns in this order (no header row):

brand,price,body,mileage,engv,engtype,registration,year,model,drive
Ford,15500,crossover,68,2.5,Gas,yes,2010,Kuga,full
Mercedes-Benz,20500,sedan,173,1.8,Gas,yes,2011,E-Class,rear
...

3. NiFi CSVReader Controller Service Schema

Use the following Avro schema in the 'Schema Text' property:

{
  "type": "record",
  "name": "cars",
  "fields": [
    { "name": "brand", "type": ["null", "string"] },
    { "name": "price", "type": ["null", "double"] },
    { "name": "body", "type": ["null", "string"] },
    { "name": "mileage", "type": ["null", "int"] },
    { "name": "engv", "type": ["null", "double"] },
    { "name": "engtype", "type": ["null", "string"] },
    { "name": "registration", "type": ["null", "string"] },
    { "name": "year", "type": ["null", "int"] },
    { "name": "model", "type": ["null", "string"] },
    { "name": "drive", "type": ["null", "string"] }
  ]
}
  • Set 'Treat First Line as Header' to false.
  • Set 'Value Separator' to ,.
  • Set 'Record Separator' to \n.

4. NiFi ReplaceText Processor (for NA → empty string)

  • Search Value: NA
  • Replacement Value: (leave blank for empty string)
  • Replacement Strategy: Literal Replace
  • Evaluation Mode: Line-by-Line

5. Flow Order

  1. Input CSV → 2. ReplaceText (NA → empty) → 3. PutDatabaseRecord

This workflow ensures your car data is imported cleanly from CSV to PostgreSQL using NiFi, with correct handling of 'NA' values and matching data types.

ALTER TABLE cars ALTER COLUMN registration TYPE varchar(10);


## Running Redis with Docker

To start a Redis server using Docker, run:

docker run -d --name my-redis -p 6379:6379 redis:latest


- This will start Redis on port 6379 (the default).
- The FastAPI app is configured to connect to Redis at `localhost:6379`.

## Using Redis in the FastAPI API

- The API uses Redis to cache responses for GET endpoints (e.g., `/cars/`, `/cars/by_brand/{brand}`) for faster performance.
- When you update car prices (e.g., with the `/cars/increase_price/{brand}` endpoint), the relevant cache entries are cleared automatically.

## Running Keycloak with Docker

To start a Keycloak server using Docker, run:

docker run -d --name my-keycloak -p 8080:8080
-e KEYCLOAK_ADMIN=admin
-e KEYCLOAK_ADMIN_PASSWORD=admin
quay.io/keycloak/keycloak:latest start-dev


- This will start Keycloak on port 8080.
- Access the admin console at: http://localhost:8080
- Login with: `admin` / `admin`

### Keycloak Setup Steps:
1. Create a new realm (e.g., "cars-api")
2. Create a new client (e.g., "fastapi-client")
3. Set client protocol to "openid-connect"
4. Set Access Type to "confidential"
5. Add valid redirect URIs (e.g., "http://localhost:8000/*")
6. Copy the client secret for use in your FastAPI app

curl -X POST http://localhost:8080/realms/cars-api/protocol/openid-connect/token -H "Content-Type: application/x-www-form-urlencoded" -d "grant_type=password" -d "client_id=fastapi-client" -d "client_secret=rkwYLI0JwdwnKS4lAIezJBCHQF8KqEXj" -d "username=testuser" -d "password=password123"
  
## Building a Secure API with FastAPI, PostgreSQL, and Keycloak

Here’s a high-level step-by-step plan to create a secure API using FastAPI, Docker, K8s, Python, Redis, Keycloak, and Swagger, integrating with your PostgreSQL database and NiFi dataflow:

### 1. Design Your API (Swagger/OpenAPI)
- Define your endpoints (e.g., `/cars`, `/cars/{id}`) and request/response models using OpenAPI/Swagger.
- FastAPI auto-generates Swagger UI at `/docs`.

### 2. Set Up FastAPI Project
- Create a new FastAPI project.
- Define Pydantic models matching your `cars` table.
- Implement endpoints for CRUD operations (GET, POST, PUT, DELETE) on cars.

### 3. Connect FastAPI to PostgreSQL
- Use an async ORM like SQLModel, SQLAlchemy, or Tortoise ORM.
- Configure the database connection string to your PostgreSQL instance (the one used by NiFi).

### 4. Add Redis Integration (Optional)
- Use Redis for caching, rate limiting, or session storage as needed.
- Install and configure a Redis client (e.g., `redis-py` or `aioredis`).

### 5. Secure the API with Keycloak
- Set up a Keycloak server (can be run in Docker).
- Create a realm, client, and user roles in Keycloak.
- Configure FastAPI to use OAuth2/OpenID Connect with Keycloak (use `fastapi-keycloak` or `python-jose` for JWT validation).
- Protect your endpoints with authentication and role-based authorization.

### 6. Dockerize the Application
- Write a `Dockerfile` for your FastAPI app.
- Add a `docker-compose.yml` if you want to run FastAPI, PostgreSQL, Redis, and Keycloak together locally.

# Get Token to test on nifi

TOKEN=$(curl -s -X POST http://localhost:8080/realms/cars-api/protocol/openid-connect/token \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=password" \
  -d "client_id=fastapi-client" \
  -d "client_secret=bfee999244ca763250fa4c1f9df53e2ade7f16fe08ad19b3a2ea48e9e3ebedd5" \
  -d "username=testuser" \
  -d "password=password123" | python3 -c "import sys, json; print(json.load(sys.stdin)['access_token'])")

echo "Copy this into NiFi Authorization field:"
echo "Bearer $TOKEN"

### 7. Deploy to Kubernetes
- Write Kubernetes manifests (Deployment, Service, Ingress, ConfigMap/Secret for env vars).
- Deploy your containers (FastAPI, Redis, Keycloak, etc.) to your K8s cluster.

### 8. Test and Document with Swagger
- Access `/docs` on your FastAPI app to use Swagger UI.
- Test endpoints, including authentication with Keycloak tokens.

### 9. Integrate with NiFi (if needed)
- If you want NiFi to call your API, use NiFi’s InvokeHTTP processor.
- If you want your API to trigger NiFi, use NiFi’s ListenHTTP processor.

---

**Summary Table**

| Step | Task                                      | Tool/Tech         |
|------|-------------------------------------------|-------------------|
| 1    | Design API (OpenAPI/Swagger)              | FastAPI           |
| 2    | Implement FastAPI endpoints               | FastAPI, Python   |
| 3    | Connect to PostgreSQL                     | SQLAlchemy/ORM    |
| 4    | Integrate Redis (optional)                | Redis, redis-py   |
| 5    | Secure with Keycloak                      | Keycloak, OIDC    |
| 6    | Dockerize                                 | Docker            |
| 7    | Deploy to Kubernetes                      | K8s, manifests    |
| 8    | Test with Swagger UI                      | FastAPI           |
| 9    | Integrate with NiFi (optional)            | NiFi, HTTP        |

This plan will help you build a robust, secure, and scalable API integrated with your data pipeline.

About

Data Flow project using FastAPI,K8s,Terraform,AWS S3,Apache NiFi,Redis,KeyCloak,Swagger,Docker,Postgresql

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages