PostgreSQL

PostgreSQL

PostgreSQL là open-source relational database mạnh mẽ.

Setup

# Install
brew install postgresql  # macOS
sudo apt install postgresql  # Linux

# Start service
brew services start postgresql

Basic SQL

-- Create table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO users (name, email)
VALUES ('Phi', 'phi@example.com');

-- Query
SELECT * FROM users WHERE email = 'phi@example.com';

-- Update
UPDATE users SET name = 'Anh Phi' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Relationships

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  user_id INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Join query
SELECT users.name, posts.title
FROM users
JOIN posts ON users.id = posts.user_id;

Using with Node.js (pg library)

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

// Query
const result = await pool.query("SELECT * FROM users");
console.log(result.rows);

// Parameterized query (prevent SQL injection)
const user = await pool.query("SELECT * FROM users WHERE email = $1", ["phi@example.com"]);

Prisma ORM

schema.prisma

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create user
const user = await prisma.user.create({
  data: {
    name: "Phi",
    email: "phi@example.com",
  },
});

// Find users
const users = await prisma.user.findMany({
  include: { posts: true },
});