Intermediate

Database and SQL with AI

Lesson 1 of 4 Estimated Time 50 min

Database and SQL with AI

Databases are central to most applications, and SQL can be complex. Whether you’re writing raw SQL queries, designing schemas, or managing migrations, AI can significantly accelerate database work. From complex joins to optimization strategies, AI understands database patterns deeply.

Schema Design with AI

Getting Design Advice

Before writing queries, you need a schema. AI can help:

You: "I'm building a blog platform. I need:
     - Users (name, email, bio)
     - Blog posts (title, content, published_date)
     - Comments (author, text, post)
     - Likes (user, post)

     Design the schema for me."

AI: [Generates complete schema with relationships]

Refining Schema

You: "This schema looks good, but:
     1. Users can follow other users
     2. Posts can have multiple tags
     3. Comments can be nested (replies)

     Update the schema to support this."

AI: [Adds follow table, post_tags junction, comment hierarchy]

Understanding Design Decisions

You: "Why use a junction table for tags instead of
     storing them as an array in posts?"

AI: "Junction tables allow:
    - Multiple posts per tag
    - Querying posts by tag efficiently
    - Adding metadata to relationships

    Arrays would make tag queries slower."

Complex Query Generation

Starting Point: Describe What You Need

You: "Get all blog posts from users I follow,
     ordered by date, with comment counts and like counts."

AI: [Generates the SQL]

Specify the Constraints

You: "Get posts from my followers, excluding:
     - Posts older than 7 days
     - Posts with no engagement
     - Posts marked private

     Include:
     - Comment count
     - Like count
     - Follower's name

     Order by most recent first"

AI: [Generates optimized SQL with all constraints]

Provide Context

You: "Here's my schema: [paste schema]
     Here's a similar working query: [paste query]

     Generate a new query that: [requirements]"

AI: [Generates query matching your style]

JOIN Complexity

JOINs are where SQL gets complicated. AI excels here:

Visual JOIN Explanation

You: "I have users, posts, and comments.
     I want all users with their post counts and
     total comments on those posts.

     Schema: [schema]"

AI: [Explains JOIN strategy]
"You'll need:
1. LEFT JOIN posts on users.id
2. LEFT JOIN comments on posts.id
3. GROUP BY user
4. Use COUNT(DISTINCT post_id) to avoid double-counting"

Then generates the SQL

Multi-Table JOINs

You: "Query template:
     - users
     - orders (user_id, date)
     - order_items (order_id, product_id, quantity)
     - products (name, price)
     - categories (name)

     Get: User name, order date, product names, total price per order
     Order by user, then by date descending"

AI: [Generates correct multi-table JOIN]

Query Optimization

Identifying Performance Issues

You: "This query is slow:
     [paste slow query]

     What's the issue?"

AI: [Explains the problem]
"You're joining 5 tables without WHERE clause.
Add filters to reduce rows early.
Add index on (user_id, date) for faster filtering."

Optimization Strategies

Strategy 1: Index Usage

You: "What indexes would help this query?
     [paste query]"

AI: "Add indexes on:
    - users(id)
    - posts(user_id)
    - comments(post_id)
    - posts(created_date) for date filtering"

Strategy 2: Query Rewriting

You: "This query uses a subquery and is slow:
     [paste query]"

AI: "Rewrite using JOIN instead of subquery for better performance:
    [optimized query]"

Strategy 3: Denormalization

You: "Users have many posts, comments on those posts.
     Getting comment count per user is slow.
     Should I denormalize?"

AI: "Consider adding comment_count to users table if:
    - You query it frequently
    - Updates are acceptable to be slightly delayed
    - The benefit outweighs denormalization complexity"

Schema Migrations

Generating Migration Scripts

You: "I need to:
     1. Add email_verified boolean to users
     2. Create passwords table (separate from users)
     3. Add foreign key

     Database: PostgreSQL
     Migration tool: Alembic

     Generate migration script"

AI: [Generates complete migration]

Migration With Data Transformation

You: "I'm splitting user_name into first_name and last_name.
     Existing data format: 'John Doe'
     Need to: Split on space

     Generate safe migration with data transformation"

AI: [Generates migration that:
     1. Adds new columns
     2. Splits data
     3. Validates split correctly
     4. Drops old column]

Rollback Strategies

You: "Here's my migration [paste].
     What could go wrong?
     How should I rollback?"

AI: "Risky operations:
    - Dropping column with data loss
    - Non-nullable constraint on data that exists

    Safer approach:
    [revised migration with backward compatibility]"

ORM Usage (Sequelize, SQLAlchemy, Prisma)

Generating ORM Code from Schema

You: "Here's my schema:
     [SQL schema]

     Generate Sequelize models for this"

AI: [Generates complete models with associations]

Complex ORM Queries

You: "Using SQLAlchemy, get all users with:
     - Posts from last 7 days
     - Comment count > 0
     - Order by comment count descending

     Schema: [schema]"

AI: [Generates SQLAlchemy query]
session.query(User).join(Post)...

ORM to SQL Translation

You: "I have this ORM query:
     [ORM code]

     What SQL does this generate?
     Is it optimized?"

AI: "This generates: [SQL]
    Performance note: Missing index on [column]
    Consider: [optimization suggestion]"

Real-World Database Scenarios

Scenario 1: Analytics Query

You: "I need daily user activity report:
     - New users per day
     - Active users per day
     - Most popular posts per day

     Data from last 30 days.
     Schema: [schema]"

AI: [Generates complex aggregation query with GROUP BY and date functions]

Scenario 2: Data Integrity Check

You: "Check for:
     - Orphaned records (comments with no post)
     - Duplicate users (same email)
     - Posts with invalid user_id

     Schema: [schema]"

AI: [Generates queries to find data issues]

Scenario 3: Bulk Operations

You: "Safely bulk update 10,000 user records to increment
     their level by 1 if they have > 100 posts.

     Database: PostgreSQL
     Production data: handle with care"

AI: [Generates safe batch update with:
    - Progress tracking
    - Rollback capability
    - Verification]

Testing Database Code

Generating Test Data

You: "I need test data for:
     - 100 users
     - 500 posts
     - 2000 comments
     - Realistic relationships

     Generate SQL INSERT statements"

AI: [Generates INSERT statements with realistic data]

Testing Queries

You: "Test this query:
     [paste query]

     Generate test data and verify query is correct"

AI: [Generates test data]
    [Shows expected results]
    [Suggests additional test cases]

Common Database Patterns

Pattern 1: Soft Delete

You: "Implement soft delete:
     - Add deleted_at timestamp
     - Queries should exclude soft-deleted
     - Should be recoverable

     Framework: Sequelize"

AI: [Generates model with scopes for soft delete]

Pattern 2: Audit Trail

You: "Track changes to important tables:
     - What changed
     - Who changed it
     - When

     Database: PostgreSQL"

AI: [Generates audit table and trigger to populate it]

Pattern 3: Temporal Data

You: "Store historical versions of user profiles:
     - Current profile
     - All versions history
     - Query specific point-in-time

     Database: PostgreSQL"

AI: [Generates temporal table design and queries]

Performance Monitoring

Analyzing Slow Queries

You: "This query takes 5 seconds:
     [query]

     Query plan:
     [paste EXPLAIN output]

     How do I optimize?"

AI: [Analyzes query plan]
    "Seq scan indicates missing index.
    Full table scan on [table] is slow.
    Add index on [columns] and query will use it."

Index Recommendations

You: "What indexes would help my most common queries?
     Common queries: [list queries]
     Schema: [schema]"

AI: [Recommends indexes that would help multiple queries]

Exercises

  1. Schema Design: Design a database schema for:

    • E-commerce platform
    • Social network
    • Project management tool Ask AI to review for normalization and optimization.

  2. Complex Queries: For your schema, ask AI to generate:

    • Difficult JOIN queries
    • Aggregation queries
    • Recursive queries (if supported)

  3. Migration Practice: Start with v1 schema, request v2 changes. Ask AI to:

    • Generate migration
    • Identify risks
    • Suggest rollback strategy
    • Ensure data integrity

  4. Performance Optimization: Take a slow query:

    • Ask AI to explain why it’s slow
    • Request optimization
    • Ask for indexes
    • Verify improvement with EXPLAIN

  5. Real Data Problem: Solve a real problem from your database:

    • Duplicate records to fix
    • Data inconsistency to resolve
    • Bulk update needed Ask AI to safely generate the fix.
Next API Development with AI →