Setup Local DB

🚀 Goal

• Install PostgreSQL locally

• Connect it to VS Code

• Run SQL queries and manage your database

---

🧠 Prerequisites

• VS Code installed

• Basic Terminal or Command Prompt knowledge

---

🖥️ On macOS

1. Install PostgreSQL via Homebrew

brew install postgresql
brew services start postgresql

2. Create a User & Database

psql postgres

-- Inside the prompt:
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
\q

3. Install VS Code PostgreSQL Extension

Open VS Code and install:

🔍 PostgreSQL by Microsoft

Or from terminal:

code --install-extension ms-azuretools.vscode-postgresql

4. Connect to PostgreSQL in VS Code

1. Open Command Palette (Cmd+Shift+P)

2. Run PostgreSQL: New Connection

3. Fill in:

   • Host: localhost

   • Port: 5432

   • Username: myuser

   • Password: mypassword

   • Database: mydb

---

🖥️ On Windows

1. Install PostgreSQL

• Download from: https://www.postgresql.org/download/windows/

• During installation:

  • Set password for postgres superuser

  • Default port: 5432

2. Add PostgreSQL to System PATH So you can use psql in CMD/PowerShell:

• Add: C:\Program Files\PostgreSQL\15\bin (adjust based on version)

3. Create a New Database

Open pgAdmin or PowerShell:

psql -U postgres
-- Enter the password you set
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
\q

4. Install VS Code PostgreSQL Extension

Same as macOS:

• Open VS Code → Extensions → Search PostgreSQL by Microsoft → Install

5. Connect from VS Code

Use Ctrl+Shift+P → PostgreSQL: New Connection

---

⚙️ Run SQL Files in VS Code

1. Create a new file: init.sql

2. Write SQL statements (e.g., CREATE TABLE, INSERT INTO)

3. Right-click in the editor → Execute Query

---

✅ Optional: Load Your .sql File

From terminal or command prompt:

psql -U myuser -d mydb -f path/to/create_employee_db.sql

---

📦 Recommended Tools

Tool	Use
pgAdmin	GUI for PostgreSQL
DBeaver	Cross-platform DB GUI
Postico (Mac)	Native PostgreSQL GUI
psql	Command-line PostgreSQL tool

---

💡Create Employee DB like schema below:

📐Create employee_db

psql postgres

👉Inside the psql prompt, run:

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE employee_db OWNER myuser;
\q

👉Create Table (SQL schema):

-- Connect to the new database
\c employee_db

-- Table: employees
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    date_of_birth DATE,
    hire_date DATE,
    employment_status VARCHAR(50),
    department VARCHAR(50)
);

-- Table: addresses
CREATE TABLE addresses (
    address_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    address_line1 VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50),
    address_type VARCHAR(20)
);

-- Table: awards
CREATE TABLE awards (
    award_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    achievement_type VARCHAR(50),
    award_name VARCHAR(100),
    date_received DATE
);

-- Table: loans
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    loan_type VARCHAR(50),
    amount NUMERIC(10, 2),
    interest_rate NUMERIC(5, 2)
);

-- Table: roles
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    reporting_to INTEGER,
    role_title VARCHAR(100),
    department VARCHAR(50),
    FOREIGN KEY (reporting_to) REFERENCES employees(employee_id)
);

-- Table: salary_details
CREATE TABLE salary_details (
    salary_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    base_salary NUMERIC(10, 2),
    bonus NUMERIC(10, 2),
    effective_date DATE
);

-- Table: work_history
CREATE TABLE work_history (
    work_history_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    company_name VARCHAR(100),
    position_held VARCHAR(100),
    start_date DATE
);

👉Insert DB:

INSERT INTO employees (first_name, last_name, email, phone, date_of_birth, hire_date, employment_status, department)
VALUES ('John', 'Doe', '[e-mail]', '123-456-7890', '1990-01-01', '2020-06-15', 'Active', 'Engineering');

---

🧠 Using MCP DB Server to working with DB