AnasAhmed001 avatar

database-specialist

Expert database design, queries, and optimization for both SQL (Neon serverless PostgreSQL) and NoSQ

作者 AnasAhmed001|オープンソース

Database Specialist

Expert guidance for SQL (Neon PostgreSQL) and NoSQL (MongoDB) database development.

Quick Start Workflow

  1. Choose database - SQL (Neon PostgreSQL) or NoSQL (MongoDB)
  2. Design schema - Define tables/collections, relationships, indexes
  3. Setup connection - Configure connection strings and pooling
  4. Create migrations - Version control schema changes
  5. Implement queries - CRUD operations, joins, aggregations
  6. Optimize performance - Add indexes, analyze queries
  7. Integrate with ORM - Prisma, SQLAlchemy, or Mongoose

Decision Tree

What database are you using?

Neon PostgreSQL (SQL)

MongoDB (NoSQL)

  • Setup → Connection patterns below
  • Schema design → Document modeling below
  • Queries → CRUD and aggregation below
  • Performance → Indexing strategies below

Installation

Neon PostgreSQL

# Node.js
npm install pg
npm install prisma @prisma/client  # If using Prisma

# Python
pip install psycopg2-binary
pip install sqlalchemy  # If using SQLAlchemy

MongoDB

# Node.js
npm install mongodb
npm install mongoose  # If using Mongoose

# Python
pip install pymongo
pip install motor  # For async

Basic Patterns

Pattern 1: Neon PostgreSQL with Prisma

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

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

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Create
await prisma.user.create({
  data: { email: 'user@example.com', name: 'John' }
});

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

// Update
await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Jane' }
});

// Delete
await prisma.user.delete({ where: { id: 1 } });

Pattern 2: Neon PostgreSQL Raw SQL

const { Pool } = require('pg');

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

// Create
await pool.query(
  'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
  ['user@example.com', 'John']
);

// Read
const result = await pool.query('SELECT * FROM users WHERE email = $1', ['user@example.com']);

// Update
await pool.query('UPDATE users SET name = $1 WHERE id = $2', ['Jane', 1]);

// Delete
await pool.query('DELETE FROM users WHERE id = $1', [1]);

Pattern 3: MongoDB with Mongoose

const mongoose = require('mongoose');

await mongoose.connect(process.env.MONGODB_URI);

const userSchema = new mongoose.Schema({
  email: { type: String, required: true, unique: true },
  name: String,
  posts: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Post' }],
  createdAt: { type: Date, default: Date.now }
});

const User = mongoose.model('User', userSchema);

// Create
const user = await User.create({
  email: 'user@example.com',
  name: 'John'
});

// Read
const users = await User.find().populate('posts');

// Update
await User.findByIdAndUpdate(userId, { name: 'Jane' });

// Delete
await User.findByIdAndDelete(userId);

Pattern 4: MongoDB Raw Driver

const { MongoClient } = require('mongodb');

const client = new MongoClient(process.env.MONGODB_URI);
await client.connect();
const db = client.db('mydb');
const users = db.collection('users');

// Create
const result = await users.insertOne({
  email: 'user@example.com',
  name: 'John',
  createdAt: new Date()
});

// Read
const user = await users.findOne({ email: 'user@example.com' });

// Update
await users.updateOne(
  { _id: userId },
  { $set: { name: 'Jane' } }
);

// Delete
await users.deleteOne({ _id: userId });

Schema Design

SQL (Neon PostgreSQL)

-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table (one-to-many relationship)
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_users_email ON users(email);

MongoDB

// Users collection schema
{
  _id: ObjectId,
  email: String,  // indexed
  name: String,
  posts: [ObjectId],  // references to posts
  createdAt: Date
}

// Posts collection schema
{
  _id: ObjectId,
  userId: ObjectId,  // indexed
  title: String,
  content: String,
  createdAt: Date
}

// Create indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.posts.createIndex({ userId: 1 });

Common Queries

Neon PostgreSQL

-- Join with aggregation
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 0;

-- Pagination
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- Search
SELECT * FROM posts
WHERE title ILIKE '%search%' OR content ILIKE '%search%';

-- Transaction
BEGIN;
INSERT INTO users (email, name) VALUES ('user@example.com', 'John');
INSERT INTO posts (user_id, title) VALUES (1, 'My Post');
COMMIT;

MongoDB

// Aggregation pipeline
await db.collection('posts').aggregate([
  { $match: { userId: new ObjectId(userId) } },
  { $group: { _id: '$userId', count: { $sum: 1 } } },
  { $lookup: {
      from: 'users',
      localField: '_id',
      foreignField: '_id',
      as: 'user'
    }
  }
]);

// Pagination
await posts.find()
  .sort({ createdAt: -1 })
  .skip(20)
  .limit(10)
  .toArray();

// Search
await posts.find({
  $or: [
    { title: { $regex: 'search', $options: 'i' } },
    { content: { $regex: 'search', $options: 'i' } }
  ]
});

// Transaction
const session = client.startSession();
session.startTransaction();
try {
  await users.insertOne({ email: 'user@example.com' }, { session });
  await posts.insertOne({ userId, title: 'Post' }, { session });
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

Performance Optimization

Indexing (SQL)

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Multi-column index
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

-- Partial index
CREATE INDEX idx_active_posts ON posts(user_id) WHERE published = true;

-- Analyze query
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1;

Indexing (MongoDB)

// Single field index
db.users.createIndex({ email: 1 }, { unique: true });

// Compound index
db.posts.createIndex({ userId: 1, createdAt: -1 });

// Text index for search
db.posts.createIndex({ title: 'text', content: 'text' });

// Analyze query
db.posts.find({ userId: ObjectId(id) }).explain('executionStats');

Connection Pooling

// PostgreSQL pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// MongoDB connection pool (automatic)
const client = new MongoClient(uri, {
  maxPoolSize: 50,
  minPoolSize: 10,
  maxIdleTimeMS: 30000
});

Migrations

Prisma Migrations

# Create migration
npx prisma migrate dev --name add_posts_table

# Apply migrations
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

SQL Migrations

-- migrations/001_create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- migrations/002_add_posts.sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Environment Variables

# Neon PostgreSQL
DATABASE_URL="postgresql://user:password@ep-xxx.neon.tech/dbname?sslmode=require"

# MongoDB
MONGODB_URI="mongodb+srv://user:password@cluster.mongodb.net/dbname"

Reference Files

  • neon-postgres-guide.md - Complete Neon PostgreSQL guide with setup, queries, optimization, transactions, and best practices

Best Practices

General

  1. Use connection pooling - Reuse connections
  2. Add indexes - Index foreign keys and frequently queried fields
  3. Use prepared statements - Prevent SQL injection
  4. Normalize data - Avoid data duplication (SQL)
  5. Denormalize when needed - For read performance (MongoDB)
  6. Use transactions - For operations that must succeed/fail together
  7. Monitor queries - Use EXPLAIN/explain() to find slow queries
  8. Backup regularly - Automate database backups

SQL-Specific

  • Use foreign key constraints with CASCADE
  • Create indexes on join columns
  • Use appropriate data types (INTEGER vs BIGINT, VARCHAR vs TEXT)
  • Avoid SELECT * in production
  • Use LIMIT for large result sets

MongoDB-Specific

  • Embed related data for one-to-few relationships
  • Reference for one-to-many or many-to-many
  • Use projection to limit returned fields
  • Create indexes before querying
  • Use aggregation pipeline for complex queries

Common Patterns

Pagination (SQL)

-- Page 1 (skip 0, limit 10)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 0;

-- Page 2 (skip 10, limit 10)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 10;

Pagination (MongoDB)

const page = 1;
const limit = 10;
const skip = (page - 1) * limit;

const posts = await db.collection('posts')
  .find()
  .sort({ createdAt: -1 })
  .skip(skip)
  .limit(limit)
  .toArray();

Search (SQL)

-- Case-insensitive search
SELECT * FROM posts
WHERE title ILIKE '%search%' OR content ILIKE '%search%';

-- Full-text search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
@@ to_tsquery('english', 'search & term');

Search (MongoDB)

// Regex search
await posts.find({
  $or: [
    { title: { $regex: 'search', $options: 'i' } },
    { content: { $regex: 'search', $options: 'i' } }
  ]
});

// Text search (requires text index)
await posts.find({ $text: { $search: 'search term' } });

Example Workflow

User request: "Design a database for a blog with users, posts, and comments"

SQL (Neon PostgreSQL) Approach

  1. Design schema

    CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

  2. Add indexes

    CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);

  3. Create with Prisma

    npx prisma migrate dev --name init

MongoDB Approach

  1. Design collections

    // users collection
{
  _id: ObjectId,
  email: String,
  name: String,
  createdAt: Date
}

// posts collection (embedded comments for performance)
{
  _id: ObjectId,
  userId: ObjectId,
  title: String,
  content: String,
  comments: [
    {
      userId: ObjectId,
      content: String,
      createdAt: Date
    }
  ],
  createdAt: Date
}

  2. Create indexes

    db.users.createIndex({ email: 1 }, { unique: true });
db.posts.createIndex({ userId: 1 });
db.posts.createIndex({ createdAt: -1 });

Result: Fully designed database schema with relationships and indexes optimized for queries.

Skill をダウンロード