Skip to content

Tutorial: Setting Up a Free Local AI Agent for MySQL/PostgreSQL

Prerequisites

  • Hardware: A computer with at least 8GB RAM (16GB recommended for LLMs).
  • Operating System: Windows, macOS, or Linux.
  • Software:
    • Python 3.11 or higher.
    • MySQL or PostgreSQL installed locally.
    • Docker (optional, for running databases).
    • Ollama for running a local LLM.

Step 1: Set Up Your Database

You can use either MySQL or PostgreSQL. Below are instructions for both.

Option 1: MySQL

  1. Install MySQL:
    • Download and install MySQL Community Server from MySQL Official Website.
    • Follow the installer prompts, set a root password, and ensure the server is running.
  2. Verify Installation:
    • Open a terminal and run: mysql -u root -p
    • Enter your root password. If you see the MySQL prompt, it’s working.
  3. Create a Sample Database:
    • In the MySQL prompt, create a database and table: CREATE DATABASE store; USE store; CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50) ); INSERT INTO products (name, price, category) VALUES ('Laptop', 999.99, 'Electronics'), ('Headphones', 49.99, 'Electronics'), ('Coffee Mug', 9.99, 'Home');

Option 2: PostgreSQL

  1. Install PostgreSQL:
  2. Verify Installation:
    • Open a terminal and run: psql -U postgres
    • Enter your password. If you see the PostgreSQL prompt, it’s working.
  3. Create a Sample Database:
    • In the PostgreSQL prompt, create a database and table: CREATE DATABASE store; \c store CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50) ); INSERT INTO products (name, price, category) VALUES ('Laptop', 999.99, 'Electronics'), ('Headphones', 49.99, 'Electronics'), ('Coffee Mug', 9.99, 'Home');

Alternative: Use Docker

If you prefer Docker:

  • MySQL: docker run -d -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root mysql:latest
  • PostgreSQL: docker run -d -p 5432:5432 --name postgres -e POSTGRES_PASSWORD=postgres postgres:latest

Step 2: Install Ollama for Local LLM

Ollama allows you to run LLMs locally, such as Llama 3 or Mistral, which will power the AI agent.

  1. Install Ollama:
  2. Pull a Model:
    • In a terminal, run: ollama pull llama3
    • This downloads the Llama 3 model (8B parameters, suitable for most systems). Alternatively, try mistral for better SQL performance.
  3. Verify Ollama:
    • Run: ollama run llama3
    • Type a test prompt like “Hello, world!” and ensure you get a response.

Step 3: Set Up Python Environment

  1. Install Python:
  2. Create a Virtual Environment: python -m venv ai-agent-env source ai-agent-env/bin/activate # On Windows: ai-agent-env\Scripts\activate
  3. Install Dependencies:
    • Install LangChain, database drivers, and Ollama integration: pip install langchain langchain-community langchain-ollama mysql-connector-python psycopg2-binary

Step 4: Create the AI Agent Script

Create a Python script to build an AI agent that uses LangChain to interact with your database and Ollama for natural language processing.

  1. Create a File:
    • Create a file named sql_agent.py.
  2. Write the Code:
    • Below is a sample script for PostgreSQL. For MySQL, replace the database connection details accordingly. from langchain_community.utilities import SQLDatabase from langchain_community.agent_toolkits import create_sql_agent from langchain_ollama import ChatOllama # Database connection (PostgreSQL example) db = SQLDatabase.from_uri("postgresql+psycopg2://postgres:postgres@localhost:5432/store") # Initialize Ollama LLM llm = ChatOllama(model="llama3", temperature=0) # Create SQL agent agent = create_sql_agent(llm=llm, db=db, verbose=True) # Example query query = "What are the names and prices of products in the Electronics category?" response = agent.run(query) print(response)
    • For MySQL:
      • Replace the database URI with: db = SQLDatabase.from_uri("mysql+mysqlconnector://root:root@localhost:3306/store")
  3. Run the Script: python sql_agent.py
    • Expected output (example): The products in the Electronics category are: - Laptop: $999.99 - Headphones: $49.99

Step 5: Test the AI Agent

Try different natural language queries, such as:

  • “How many products are in the store?”
  • “What is the average price of products?”
  • “List all categories in the database.”

The agent will:

  1. Convert the query to SQL using the LLM.
  2. Execute the SQL on the database.
  3. Return the result in natural language.

Step 6: Optimize and Secure

  • Limit Permissions: Create a database user with read-only access for the AI agent to prevent accidental modifications: -- PostgreSQL CREATE USER ai_agent WITH PASSWORD 'securepassword'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent; -- MySQL CREATE USER 'ai_agent'@'localhost' IDENTIFIED BY 'securepassword'; GRANT SELECT ON store.* TO 'ai_agent'@'localhost';
  • Prompt Engineering: If the LLM generates incorrect SQL, refine the prompt in the script or use a more capable model like mistral.
  • Logging: Enable verbose mode (verbose=True) in the agent to debug SQL queries.

Common Bugs and Fixes

Here are common issues you might encounter, along with solutions, based on typical setups and references from the web.

  1. Bug: “No active connection to the database”
    • Cause: The database URI is incorrect, or the database server isn’t running.
    • Fix:
      • Verify the database is running: # MySQL mysqladmin -u root -p status # PostgreSQL psql -U postgres -c "SELECT 1"
      • Check the URI format:
        • MySQL: mysql+mysqlconnector://user:password@host:port/dbname
        • PostgreSQL: postgresql+psycopg2://user:password@host:port/dbname
      • Ensure credentials are correct and the database user has access.
      • Reference: This issue was reported in a similar setup with AnythingLLM.
  2. Bug: “LLM generates incorrect SQL queries”
    • Cause: The LLM lacks context about the database schema or misinterprets the query.
    • Fix:
      • Provide the schema explicitly in the prompt or use LangChain’s schema ingestion: db.get_table_info() # Prints schema for debugging
      • Switch to a more SQL-savvy model like mistral: ollama pull mistral
      • Simplify the query or break complex queries into multiple steps.
      • Reference: LangChain’s SQL agent can struggle with complex queries without proper schema context.
  3. Bug: “Agent gets stuck in a loop”
    • Cause: The LLM repeatedly generates incorrect SQL, and the agent retries indefinitely.
    • Fix:
      • Set a maximum retry limit in the agent: agent = create_sql_agent(llm=llm, db=db, verbose=True, max_iterations=5)
      • Check the verbose output to identify the failing query and refine it manually.
      • Reference: This is noted in Percona’s AI agent tutorial.
  4. Bug: “Database driver not found” (e.g., psycopg2 or mysql-connector-python)
    • Cause: Missing Python database driver.
    • Fix:
      • Install the driver: pip install psycopg2-binary # For PostgreSQL pip install mysql-connector-python # For MySQL
      • Ensure the driver matches your database version.
  5. Bug: “Ollama model runs out of memory”
    • Cause: Insufficient RAM or GPU memory for the LLM.
    • Fix:
      • Use a smaller model (e.g., llama3:8b instead of llama3:70b).
      • Increase system swap space or upgrade RAM.
      • Run Ollama on a system with a compatible GPU (NVIDIA recommended).
      • Reference: Ollama documentation mentions memory requirements.
  6. Bug: “SQL syntax error in generated query”
    • Cause: The LLM generates queries incompatible with MySQL/PostgreSQL syntax.
    • Fix:
      • Validate generated queries before execution using LangChain’s sql_db_query_checker tool: from langchain_community.tools.sql_database.tool import QueryCheckerTool agent = create_sql_agent(llm=llm, db=db, extra_tools=[QueryCheckerTool(db=db)])
      • Test with simpler queries to identify syntax issues.
      • Reference: SQLAI.ai emphasizes validating queries to avoid errors.

Additional Resources

Tips for Success

  • Start Simple: Test with basic queries before tackling complex ones.
  • Monitor Performance: Local LLMs can be slow on low-end hardware; consider cloud-based free tiers (e.g., Supabase for PostgreSQL) if needed.
  • Backup Database: Always back up your database before testing AI-generated queries.
  • Community Support: Join forums like Reddit’s r/LocalLLLaMA or Stack Overflow for troubleshooting.

This setup provides a robust, free, and local AI agent for MySQL or PostgreSQL, with solutions to common pitfalls. Let me know if you need help with specific steps or additional features like vector search or schema optimization!

Tags:

Leave a Reply

Discover more from Sowft | Transforming Ideas into Digital Success

Subscribe now to keep reading and get access to the full archive.

Continue reading