Agentic Database CLI

I have developed a truly agentic AI Database Agent built as a terminal-native CLI tool. This agent can seamlessly integrate with a Next.js project, understand natural language queries, and perform complex, full-stack database operations—from schema creation and API generation to dynamic frontend integration.

🎥 Live Demo

A complete walkthrough of the agent's features and its execution of the test queries can be viewed here:

Watch the Live Demo on YouTube Watch the Live Demo on YouTube PART 2

---

✨ Key Features

This solution was designed to be more than just a script; it's an autonomous system that mirrors the ambitions of Orchids AI.

• 🤖 Agent-based AI System: The agent operates autonomously to complete end-to-end tasks from a single, high-level natural language prompt. It reasons, plans, and executes a sequence of actions, including a mechanism for automatic error resolution.

• 💻 Terminal-Native Interface: Provides a seamless and intuitive CLI experience directly within the developer's workflow. It features both a direct query mode for complex tasks and an interactive mode for guided operations.

• 🧠 Context-Aware Code Analysis: Before performing any action, the agent reads and understands existing files (e.g., db/schema.ts, React components). This ensures all modifications are additive, contextually appropriate, and non-destructive.

• 📂 Repository Awareness: The agent intelligently navigates the Next.js project structure, creating files and directories in their conventional locations (e.g., src/app/api, src/components, src/hooks).

• ⚡ Full-Stack Integration: The agent's capabilities span the entire stack. It doesn't just create a backend; it builds the necessary React components and hooks to consume the new APIs and integrates them directly into the existing UI.

• 🛠️ Advanced Tool Calling: The agent is equipped with a suite of specialized tools (e.g., createDatabaseSchema, runMigrations, createFrontendIntegration). It leverages Google Gemini's function-calling capabilities to autonomously select and execute the correct tool for each sub-task.

• 📝 Real-time Progress Logging: The CLI provides clear, step-by-step feedback on the agent's thought process and actions, giving the user full visibility into the workflow.

• 🛡️ File System Integration: Features safe file editing and execution capabilities with backup mechanisms and rollback support for critical operations.

🚀 Getting Started

Follow these steps to set up and run the project locally.

1. Prerequisites

• Node.js (v18 or later)
• pnpm, npm, or yarn

2. Installation

1. Clone the repository:

   git clone https://github.com/your-username/your-repo-name.git
   cd your-repo-name

2. Install dependencies:

   npm install --legacy-peer-deps

3. Environment Configuration

1. Create a .env file in the root of the project by copying the example file:

   cp .env.example .env

2. Add your credentials to the .env file:

   # Your Neon PostgreSQL connection string
   DATABASE_URL="postgresql://user:password@ep-..."
   
   # Your Google AI Studio API Key for Gemini
   GEMINI_API_KEY="your_gemini_api_key"

---

💻 Usage

The Database Agent can be run in two modes:

1. Direct Query Mode (Recommended)

This mode is for executing complex, multi-step tasks from a single prompt. This is the primary mode demonstrated in the video.

Syntax:

npm run agent "your query here"

Test Query 1: Recently Played Songs

This command demonstrates the agent's ability to create tables, populate them, and integrate with the existing Spotify UI:

npm run agent "Can you store the recently played songs in a table"

Test Query 2: Made for You & Popular Albums

This command showcases the agent's capability to handle multiple related tables and complex data relationships:

npm run agent "Can you store the 'Made for you' and 'Popular albums' in a table"

Additional Examples:

npm run agent "create a products table and its apis"
npm run agent "the products table is already there, create frontend components and hooks to fetch that data and add it into the existing UI"

2. Interactive Mode

Run this mode for a guided experience where you can select a specific task:

npm run agent

---

📂 Project Structure Overview

The agent creates and modifies files in a structured and predictable manner:

.
├── cli.ts                    # The entry point for the CLI tool
├── utils/                    # Agent utilities and core logic
│   ├── agent.ts             # Main agent orchestrator and AI integration
│   ├── progress-logger.ts   # Real-time progress feedback system
│   ├── sandbox.ts           # Sandboxed execution environment
│   ├── test.ts              # Agent testing utilities
│   ├── config/
│   │   └── agent-config.ts  # AI model and system prompt configuration
│   └── tools/               # Modular tool collection
│       ├── filesystem.ts    # File system operations (read, write, edit)
│       ├── database.ts      # Core database operations
│       └── database/        # Specialized database tools
│           ├── schema-tools.ts       # Schema validation and creation
│           ├── migration-tools.ts    # Migration generation and execution
│           ├── crud-tools.ts         # CRUD operations generation
│           ├── seed-tools.ts         # Seed data generation and execution
│           ├── query-tools.ts        # Database query execution
│           ├── api-route-generator.ts # API endpoint generation
│           ├── frontend-integration.ts # React component generation
│           └── component-analyzer.ts  # Existing component analysis
│
├── db/
│   ├── schema.ts             # Agent reads and writes table schemas here
│   ├── migrations/           # Drizzle migrations are generated here
│   └── seeds/                # Seed scripts are generated and stored here
│
├── src/
│   ├── app/
│   │   ├── api/
│   │   │   └── [tableName]/  # Generated API routes
│   │   │       ├── route.ts
│   │   │       └── [id]/
│   │   │           └── route.ts
│   │   └── page.tsx          # Agent can modify this to add new components
│   │
│   ├── components/
│   │   └── [tableName]/      # Generated React components (e.g., tables, cards)
│   │
│   └── hooks/
│       └── use-[tableName].ts # Generated custom hooks for data fetching
│
└── package.json              # CLI scripts: agent, agent:minimal, agent:no-timestamps

---

🧠 Architectural Concepts

• Agentic Workflow: The agent follows a logical, mandatory workflow for database operations: Analyze → Create Schema → Generate Migration → Run Migration → Create Seed Data → Run Seed Data → Generate API → Integrate Frontend. This structured approach ensures reliability and correctness.

• Tool-Based Architecture: Instead of a monolithic prompt, the agent uses a collection of discrete, well-defined tools. This modular design makes the system more robust, easier to debug, and highly extensible. The AI's role is to act as an orchestrator, intelligently selecting and sequencing these tools to fulfill the user's request.

• Context-Aware Decision Making: The agent analyzes existing code patterns, naming conventions, and project structure before making any modifications, ensuring consistency with the established codebase.

• Error Recovery & Validation: Built-in mechanisms for detecting and automatically resolving common issues during schema creation, migration execution, and API generation.

---

🎯 Orchids Assignment Demonstration

This project specifically addresses the Orchids Full Stack SWE Takehome requirements:

✅ CLI Tool Implementation

• Terminal-native interface with real-time progress logging
• Context gathering and project analysis capabilities
• File modification tracking and status updates

✅ Database Agent Features

• Autonomous schema creation and migration management
• API endpoint generation with full CRUD operations
• Frontend integration with React components and custom hooks
• Spotify-specific data modeling (songs, albums, playlists)

✅ Test Query Execution

Both required test queries are fully supported:

1. Recently Played Songs: Creates table, populates data, generates API routes, and integrates with existing Spotify UI
2. Made for You & Popular Albums: Handles multiple related tables with proper data relationships and frontend integration

✅ Technical Excellence

• Uses Drizzle ORM as recommended
• TypeScript throughout for type safety
• Production-ready error handling and validation
• Extensible architecture for future enhancements

---

🔧 Environment Options

You can customize output using environment variables:

• SHOW_MINIMAL=true – Minimal output mode
• SHOW_TIMESTAMPS=false – Hide execution timestamps
• DEBUG_MODE=true – Enable detailed debugging information

Set these in your .env file or terminal before running the agent.

---

🤝 Contributing

This project demonstrates advanced agentic AI capabilities and serves as a foundation for building sophisticated database automation tools. The modular architecture makes it easy to extend with additional features and AI models.

---

📚 Learn More

To learn more about the technologies used:

• Next.js Documentation - Next.js features and API
• Drizzle ORM - TypeScript ORM documentation
• Google Gemini AI - Gemini API documentation

---

🚀 Deploy on Vercel

The easiest way to deploy your Next.js app is to use the Vercel Platform from the creators of Next.js.

Check out the Next.js deployment documentation for more details.