YouSearch - YouTube Video Search Application

A powerful Next.js application that syncs your YouTube saved videos (Watch Later, Liked Videos, or custom playlists) to a local MySQL database and provides lightning-fast keyword search across titles, descriptions, and transcripts.

Features

• YouTube Integration: Fetch videos from Watch Later, Liked Videos, or any playlist
• Full-Text Search: Search across video titles, descriptions, and transcripts using MySQL FULLTEXT indexes
• Transcript Support: Optional automatic transcript fetching for enhanced searchability
• Modern UI: Clean, responsive interface built with Next.js 16 and Tailwind CSS
• Real-time Sync: One-click synchronization of your YouTube videos
• Type-Safe: Built with TypeScript throughout

Tech Stack

• Frontend: Next.js 16 (App Router), React 19, TypeScript, Tailwind CSS
• Backend: Next.js API Routes, Prisma ORM
• Database: MySQL 8.0+
• External APIs: YouTube Data API v3, youtube-transcript

Prerequisites

• Node.js 18+ and npm/yarn/pnpm
• MySQL 8.0+ database
• YouTube Data API credentials (API Key or OAuth 2.0)

Installation

1. Clone and Install Dependencies

cd yousearch
npm install

2. Set Up MySQL Database

Create a MySQL database for the application:

CREATE DATABASE yousearch CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Configure Environment Variables

Copy the example environment file:

cp .env.example .env

Edit .env with your configuration:

# Database
DATABASE_URL="mysql://username:password@localhost:3306/yousearch"

# YouTube API - Choose ONE option:

# Option 1: API Key (for public playlists only)
YOUTUBE_API_KEY="your_youtube_api_key_here"

# Option 2: OAuth 2.0 (required for Watch Later/Liked Videos)
GOOGLE_CLIENT_ID="your_client_id.apps.googleusercontent.com"
GOOGLE_CLIENT_SECRET="your_client_secret"
YOUTUBE_ACCESS_TOKEN="obtained_after_oauth_flow"
YOUTUBE_REFRESH_TOKEN="obtained_after_oauth_flow"

4. Set Up YouTube API Credentials

Option A: API Key (Simple, Public Playlists Only)

1. Go to Google Cloud Console
2. Create a new project or select existing one
3. Enable YouTube Data API v3
4. Go to Credentials → Create Credentials → API Key
5. Copy the API key to your .env file

Limitations: API keys can only access public data. To sync Watch Later or Liked Videos, you need OAuth.

Option B: OAuth 2.0 (Recommended, Access Private Playlists)

1. Go to Google Cloud Console
2. Create a new project or select existing one
3. Enable YouTube Data API v3
4. Go to Credentials → Create Credentials → OAuth 2.0 Client ID
5. Configure OAuth consent screen:
   • User Type: External
   • Add scopes: https://www.googleapis.com/auth/youtube.readonly
6. Create OAuth Client ID:
   • Application Type: Web application
   • Authorized redirect URIs: http://localhost:3000/api/auth/callback
7. Download credentials and add to .env

Getting Access Token:

You can use the OAuth 2.0 Playground to get tokens:

1. Go to OAuth 2.0 Playground
2. Click settings (gear icon) → Use your own OAuth credentials
3. Enter your Client ID and Client Secret
4. In Step 1, select YouTube Data API v3 → https://www.googleapis.com/auth/youtube.readonly
5. Click "Authorize APIs" and sign in
6. In Step 2, click "Exchange authorization code for tokens"
7. Copy the access_token and refresh_token to your .env file

5. Run Prisma Migrations

Generate Prisma client and create database tables:

npx prisma generate
npx prisma db push

To view your database in Prisma Studio:

npx prisma studio

6. Install Additional Dependencies

Install the youtube-transcript package for transcript fetching:

npm install youtube-transcript

Install Lucide icons for UI:

npm install lucide-react

Usage

Start Development Server

npm run dev

Open http://localhost:3000 in your browser.

Sync Your YouTube Videos

1. Click the "Sync Videos" button in the top-right corner
2. The app will fetch videos from your Watch Later playlist (default)
3. Videos are stored in MySQL with full metadata and transcripts

Customize sync behavior by modifying the request in components/SyncButton.tsx:

{
  source: 'watchLater',    // or 'liked' or 'playlist'
  playlistId: 'PLxxxxxx', // optional, for custom playlists
  fetchTranscripts: true,  // fetch transcripts (slower)
  maxVideos: 100,          // limit number of videos
  updateExisting: true     // update already-synced videos
}

Search Your Videos

1. Type keywords in the search box
2. Results show matching videos from titles, descriptions, and transcripts
3. Click video titles to open on YouTube
4. Transcript snippets show where keywords were found

API Endpoints

POST /api/videos/sync

Synchronize YouTube videos to database.

Request Body:

{
  "source": "watchLater",
  "playlistId": "optional-playlist-id",
  "fetchTranscripts": true,
  "maxVideos": 100,
  "updateExisting": true
}

Response:

{
  "success": true,
  "videosAdded": 45,
  "videosUpdated": 12,
  "errors": [],
  "duration": 23000
}

GET /api/videos/search

Search videos by keyword.

Query Parameters:

• q (required): Search query
• page: Page number (default: 1)
• limit: Results per page (default: 20, max: 100)
• sortBy: Sort order - relevance, date, or title (default: relevance)
• channel: Filter by channel name

Example:

GET /api/videos/search?q=typescript&page=1&limit=20&sortBy=relevance

Response:

{
  "results": [
    {
      "id": "...",
      "youtubeId": "dQw4w9WgXcQ",
      "title": "Video Title",
      "description": "Video description...",
      "channelTitle": "Channel Name",
      "publishedAt": "2024-01-15T12:00:00.000Z",
      "thumbnailUrl": "https://...",
      "duration": "PT10M30S",
      "snippets": {
        "title": "Video **Title**",
        "transcript": "...found **keyword** here..."
      }
    }
  ],
  "total": 156,
  "page": 1,
  "limit": 20,
  "hasMore": true
}

GET /api/videos/sync

Get sync history.

Response:

{
  "logs": [
    {
      "id": "...",
      "type": "watchLater",
      "status": "success",
      "videosAdded": 45,
      "videosUpdated": 12,
      "startedAt": "2024-01-15T12:00:00.000Z",
      "completedAt": "2024-01-15T12:05:00.000Z"
    }
  ]
}

Database Schema

Videos Table

model Video {
  id           String   @id @default(cuid())
  youtubeId    String   @unique
  title        String
  description  String?
  channelTitle String
  tags         String?  // JSON array
  publishedAt  DateTime
  transcript   String?  // Full transcript text
  thumbnailUrl String?
  duration     String?  // ISO 8601 format
  viewCount    Int?
  likeCount    Int?
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  @@fulltext([title, description, transcript])
}

FULLTEXT Indexes

The app uses MySQL FULLTEXT indexes for fast searching:

• Combined index: title, description, transcript
• Individual indexes on each field for targeted searches

Performance Optimization

Search Performance

• FULLTEXT Search: Uses MySQL's native FULLTEXT search with BOOLEAN MODE for fast keyword matching
• Fallback: Gracefully falls back to LIKE-based search if FULLTEXT fails
• Indexes: B-tree indexes on publishedAt, channelTitle for filtering and sorting

Sync Performance

• Batching: Fetches video details in batches of 50 (YouTube API limit)
• Pagination: Handles large playlists with automatic pagination
• Error Handling: Continues syncing even if individual videos fail
• Logging: Tracks sync history in sync_logs table

Transcript Fetching

• Optional: Can be disabled for faster syncing
• Caching: Transcripts are stored and not re-fetched unless video is updated
• Async: Transcripts are fetched asynchronously to avoid blocking

Troubleshooting

"YouTube API error: 403"

• Check that YouTube Data API v3 is enabled in Google Cloud Console
• Verify your API key or OAuth credentials are correct
• For OAuth: Ensure you have the youtube.readonly scope

"Could not find Watch Later playlist"

• Watch Later requires OAuth authentication (API key won't work)
• Make sure YOUTUBE_ACCESS_TOKEN is set in .env
• Try re-generating your OAuth tokens

"Search returns no results"

• Run a sync first to populate the database
• Check that videos were successfully synced: npx prisma studio
• Try a broader search term

"FULLTEXT search not working"

• Ensure MySQL version is 8.0+
• Check that FULLTEXT indexes were created: npx prisma db push
• MySQL requires at least 3 characters for FULLTEXT search by default

Transcripts not fetching

• Some videos don't have captions available
• Some videos have auto-generated captions that may fail to fetch
• YouTube may rate-limit transcript requests
• Set fetchTranscripts: false in sync request to skip

Production Deployment

Environment Variables

Update .env for production:

DATABASE_URL="mysql://user:pass@prod-host:3306/yousearch?sslmode=require"
NODE_ENV="production"
NEXT_PUBLIC_APP_URL="https://your-domain.com"

Database

• Use a managed MySQL service (AWS RDS, PlanetScale, etc.)
• Enable SSL connections
• Set up automated backups
• Monitor query performance

Deploy to Vercel

vercel --prod

Add environment variables in Vercel dashboard.

Deploy to Other Platforms

The app works on any platform that supports Next.js:

• Railway: Supports MySQL and Next.js
• Fly.io: Deploy with Docker
• AWS: Use Elastic Beanstalk or ECS
• DigitalOcean: App Platform with managed MySQL

License

MIT

Contributing

Contributions welcome! Please open an issue or PR.

Support

For issues or questions:

• Check existing issues on GitHub
• Review YouTube API documentation
• Check Prisma documentation for database issues