migrate-chatbot-from-sqlite-to-postgresql

Migrate Chatbot from SQLite to PostgreSQL

Why Migrate from SQLite to PostgreSQL?

SQLite is excellent for local development, but:

  • It doesn’t persist in cloud environments like Render.

  • It’s not ideal for concurrent access.

  • PostgreSQL is a production-grade relational database.

By using PostgreSQL, your chatbot’s history and logic can persist between sessions and survive redeployments.

Migrate locally before deploying to Render!

Here’s a clear, step-by-step guide for setting up your local environment with PostgreSQL and SQLAlchemy, including all tools and Python packages you’ll need.

✅ 1. Install PostgreSQL Locally

On Windows:

  1. Download PostgreSQL from the official site:
    👉 https://www.postgresql.org/download/windows/

  2. Run the installer and remember your password (you’ll use it later).

  3. Make sure psql is added to your system PATH.

  4. Use pgAdmin (GUI tool included) or psql (CLI) to manage the database.

✅ 2. Create a Local Database

Open pgAdmin or terminal and run:

CREATE DATABASE chatbot_db;

You’ll use this DB name in your environment variable.

✅ 3. Set Environment Variable Locally

You can put this in a .env file in your project root:

DATABASE_URL=postgresql://username:password@localhost:5432/chatbot_db

Replace:

  • username = your PostgreSQL user (default is postgres)

  • password = the password you set during installation

  • 5432 = default port

  • chatbot_db = the DB name you created

✅ 4. Install Required Python Packages

Run this in your project folder:

pip install sqlalchemy psycopg2 python-dotenv flask openai

If you also need tools for development:

pip install flask-cors debugpy

✅ 5. Your requirements.txt (for both local & Render)

Create a requirements.txt file (if you don’t already have it):

Flask
openai
SQLAlchemy
psycopg2
python-dotenv
flask-cors
debugpy
To generate it automatically:
pip freeze > requirements.txt

✅ 6. Check Your Code Structure

You should now have:

project/
├── app.py or chatbot.py
├── db.py
├── models.py
├── .env
├── requirements.txt
├── database-py

Code of  project files:

To do the project code a clean code, I have created new file: db.py and moved functions: save_to_db, create_tables,  get_last_message, get_conversation_history from chatbot.py to db.py  and removed these functions from chatbot.py.

  1. File:db.py: This file contains database related code.
# db.py

# This code is designed to work with a PostgreSQL database using SQLAlchemy.
# seperating database from  the main code with using postgresql database


from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import os
from dotenv import load_dotenv
import re
from models import Chat
from sqlalchemy import select, desc

from sqlalchemy.orm import Session
from models import Chat
from database import SessionLocal  # <-- now comes from separate file


load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()


class Chat(Base):
    __tablename__ = "chats"

    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(String, index=True)
    user_input = Column(Text)
    bot_response = Column(Text)
    timestamp = Column(DateTime, default=datetime.utcnow)
    language = Column(String)


def create_tables():
    Base.metadata.create_all(bind=engine)


def save_to_db(session_id, user_input, bot_response, language):
    normalized_input = user_input.lower().translate(
        str.maketrans('', '', re.escape('!"#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~'))
    )
    db = SessionLocal()
    try:
        new_chat = Chat(
            session_id=session_id,
            user_input=normalized_input,
            bot_response=bot_response,
            language=language,
            timestamp=datetime.utcnow()
        )
        db.add(new_chat)
        db.commit()
    except Exception as e:
        db.rollback()
        raise e
    finally:
        db.close()

#Get conversation history for a specific session
def get_conversation_history(session_id):
    with SessionLocal() as session:
        history = session.query(Chat).filter_by(session_id=session_id).order_by(Chat.timestamp.asc()).all()
        return [(chat.user_input, chat.bot_response, chat.timestamp, chat.language) for chat in history]


def get_last_message(user_input):
    normalized_input = user_input.lower().translate(
        str.maketrans('', '', r'''!"#$%&'()*+,-./:;<=>?@[\]^_`{|}~''')
    )

    with SessionLocal() as session:
        result = session.execute(
            select(Chat.bot_response)
            .where(Chat.user_input == normalized_input)
            .order_by(desc(Chat.id))
            .limit(1)
        ).fetchone()

    return result[0] if result else None

2. File: database.py: This file contains Database relate imports

# database.py
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL")

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

 

3. File: .env: this file contains environment variables and only used in local development.

OPENAI_API_KEY: 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

DATABASE_URL=postgresql://username:password@localhost:5432/chatbot_db  #this is for local development

4. File models.py:  This file contains database table (chats) related code.

# models.py


from sqlalchemy import Column, Integer, String, Text, DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class Chat(Base):
    __tablename__ = "chats"

    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(String)
    user_input = Column(Text)
    bot_response = Column(Text)
    timestamp = Column(DateTime, default=datetime.utcnow)
    language = Column(String)

5. File: requirements.txt: this file needs both local and Deployment on Render

Flask
gunicorn
openai
SQLAlchemy
psycopg2
python-dotenv
flask-cors
debugpy

6. The main file: chatbot.py: this file is the main file for chatbot

# chatbot.py — AI Chatbot with language detection, datetime handling, and PostgreSQL (even SQLite) chat history
# This code is designed to work with a PostgreSQL database using SQLAlchemy.
# Seperating database from the main code with using PostgreSQL database

from flask import Flask, request, make_response, redirect, session

import uuid
import os
import json
import logging
import re
from datetime import datetime
from openai import OpenAI
from openai.types.chat import ChatCompletionMessage
from dotenv import load_dotenv
load_dotenv()

# Importing functions from db.py to use PostgreSQL database

from db import save_to_db, create_tables,  get_last_message, get_conversation_history # Importing the save_to_db and create_tables,  get_last_message functions from db.py to use pstgressql database
create_tables()  # Ensure tables are created before running the app


# === Initialize Flask App ===
app = Flask(__name__)
# app.secret_key = "your_secret_key_here"

# === Enable Logging ===
logging.basicConfig(level=logging.DEBUG)

# === Initialize OpenAI Client ===
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))


# === Detect and Handle Simple Date/Time Questions ===
def detect_datetime_question(user_input):
    text = user_input.lower()
    now = datetime.now()

    checks = {
        ("what time", "current time", "now", "time is it"): f"The current time is {now.strftime('%H:%M:%S')}.",
        ("what's the date", "what is the date", "current date", "today's date", "what date is it", "which date"): f"Today's date is {now.strftime('%Y-%m-%d')}.",
        ("which month", "what month", "current month", "month is this"): f"This month is {now.strftime('%B')}.",
        ("which day", "what day", "day is it", "current day", "today"): f"Today is {now.strftime('%A')}.",
        ("which year", "what year", "current year", "year is this"): f"This year is {now.strftime('%Y')}."
    }

    for keywords, answer in checks.items():
        if any(kw in text for kw in keywords):
            return answer
    return None

# This function checks if the response from the OpenAI API is valid by looking for common error messages or keywords.
# This function checks if the response contains date/time-like content. It uses a list of keywords and regex patterns to identify such content.

import re
# This function checks if the response contains date/time-like content. It uses a list of keywords and regex patterns to identify such content.
def looks_like_datetime_response(response: str) -> bool:
    response = response.lower()

    # Regex: look for very specific datetime phrases or formats
    datetime_patterns = [
        r"\btoday is\b",
        r"\bcurrent date\b",
        r"\bthe date is\b",
        r"\bnow is\b",
        r"\btime is\b",
        r"\b\d{4}-\d{2}-\d{2}\b",  # e.g., 2025-05-17
        r"\b\d{1,2} (january|february|march|april|may|june|july|august|september|october|november|december) \d{4}\b",
        r"\bit's (monday|tuesday|wednesday|thursday|friday|saturday|sunday)\b",
        r"\bthis month is\b",
        r"\bthe month is\b",
        r"\btoday\b",
        r"\bthis year\b",
        r"\bthis week\b"
    ]

    for pattern in datetime_patterns:
        if re.search(pattern, response):
            return True
    return False


# === Validate AI Response ===
def is_valid_response(response: str) -> bool:
    if not response or not response.strip():
        return False
    invalid_keywords = ["something went wrong", "error:", "exception", "traceback",
                        "insufficient_quota", "no longer supported", "invalid request",
                        "you exceeded your quota"]
    return not any(err in response.lower() for err in invalid_keywords)

# === Chatbot Main Logic ===
def chatbot_response(session_id, user_input):
    normalized_input = user_input.lower().translate(str.maketrans('', '', re.escape('!"#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~')))

    # Handle datetime Qs
    datetime_answer = detect_datetime_question(user_input)
    if datetime_answer:
        return datetime_answer

    # Return cached reply
    cached = get_last_message(normalized_input)
    if cached:
        return cached

    # Query OpenAI
    messages = [
    {
        "role": "system",
        "content": (
            "You are a helpful assistant. detect the language of the user input. "
            "respond to the input in the same language. "
            "Return the result in the following JSON format:\n\n"
            "{\n"
            '  "language": "<language_code>",\n'
            '  "response": "<your response in the same language>"\n'
            "}\n\n"
            "Use ISO 639-1 language codes (e.g., 'en', 'sv', 'tr', 'fa')."
        )
    },
    {
        "role": "user",
        "content": user_input
    }
]

    try: # Send request to OpenAI
        completion = client.chat.completions.create(
            model="gpt-3.5-turbo",
            messages=messages,
            temperature=0.7
        )
        reply = completion.choices[0].message.content
        data = json.loads(reply)
        response = data.get("response", "").strip()
        language = data.get("language", "").lower().strip()
    except Exception as e:
        logging.error(f"Failed to parse OpenAI response: {e}")
        response = reply.strip() if 'reply' in locals() else "Sorry, I don't have an answer right now."
        language = None

    if is_valid_response(response) \
        and not detect_datetime_question(user_input) \
        and not looks_like_datetime_response(response):
        save_to_db(session_id, normalized_input, response, language)

    return response



# === Home Page ===
@app.route("/", methods=["GET"])
def home():
    session_id = request.cookies.get("session_id") or str(uuid.uuid4())
    html = """
        <html><head><style>
        body { font-family: Arial; max-width: 600px; margin: auto; padding: 20px; }
        input[type="text"] { width: 80%; padding: 10px; }
        button { padding: 10px 15px; margin-top: 10px; }
        </style></head><body>
        <h2>Chatbot</h2>
        <form action="/chat" method="post">
            <input type="text" name="message" placeholder="Type your message" required>
            <button type="submit">Send</button>
        </form>
        <form action="/history" method="get">
            <button type="submit">Show History</button>
        </form>
        </body></html>
    """
    response = make_response(html)
    response.set_cookie("session_id", session_id, max_age=60 * 60 * 24 * 7)
    return response

# === Chat Handler ===
@app.route("/chat", methods=["POST"])
def chat():
    session_id = request.cookies.get("session_id") or str(uuid.uuid4())
    user_input = request.form.get("message", "")
    if not user_input:
        return redirect("/")

    bot_reply = chatbot_response(session_id, user_input)
    html = f"""
        <html><head><style>
        body {{ font-family: Arial; max-width: 600px; margin: auto; padding: 20px; }}
        .user {{ color: #0066cc; font-weight: bold; }}
        .bot {{ color: #009933; }}
        .chat-box {{ border-bottom: 1px solid #ccc; padding: 10px 0; }}
        button {{ padding: 10px 15px; margin-top: 10px; }}
        </style></head><body>
        <h2>Chatbot</h2>
        <div class="chat-box">
            <div class="user">You: {user_input}</div>
            <div class="bot">Bot: {bot_reply}</div>
        </div>
        <form action="/" method="get"><button type="submit">New Question</button></form>
        <form action="/history" method="get"><button type="submit">Show History</button></form>
        </body></html>
    """
    response = make_response(html)
    response.set_cookie("session_id", session_id, max_age=60 * 60 * 24 * 7)
    return response

# === History Page ===
@app.route("/history", methods=["GET"])
def history():
    session_id = request.cookies.get("session_id")
    if not session_id:
        return redirect("/")

    history = get_conversation_history(session_id)

    history_html = ""
    for user_input, bot_response, timestamp, language in history:
        lang_display = language.upper() if language else "??"
        history_html += f"""
            <div class="chat-box">
                <div><strong>[{timestamp}] [{lang_display}]</strong></div>
                <div class="user">You: {user_input}</div>
                <div class="bot">Bot: {bot_response}</div>
            </div>
        """

    html = f"""
        <html><head><style>
        body {{ font-family: Arial; max-width: 600px; margin: auto; padding: 20px; }}
        .user {{ color: #0066cc; font-weight: bold; }}
        .bot {{ color: #009933; }}
        .chat-box {{ border-bottom: 1px solid #ccc; padding: 10px 0; }}
        button {{ padding: 10px 15px; margin-top: 10px; }}
        </style></head><body>
        <h2>Chat History</h2>
        {history_html}
        <form action="/" method="get"><button type="submit">New Question</button></form>
        </body></html>
    """
    return html


# === Run Server with Debug Support ===
if __name__ == "__main__":
    import debugpy
    debugpy.listen(("0.0.0.0", 5678))
    print("Waiting for debugger connection...")
    create_tables()  # Ensure tables are created before running the app
    app.run(debug=True, use_reloader=False, use_debugger=False)

✅ 7. Run Your App Locally

In terminal:

python chatbot.py

Ensure this line is in your main block to create the tables:

create_tables()

As follow:

<strong>create_tables()</strong>  # Ensure tables are created before running the app
app.run(debug=True, use_reloader=False, use_debugger=False)

Troubleshooting Tips

  • If you get an error like psycopg2 module not found, try installing the binary version:

pip install psycopg2-binary
  • If .env variables aren’t loading, make sure you called load_dotenv() at the top of your script.
  • Donte forget to add:PostgreSQL (C:\Program Files\PostgreSQL\17\bin)   in your path.

✅ Done!

Now your chatbot is connected to PostgreSQL and ready to test locally. Once it works, deploying to Render will be smooth because you’ll already be using DATABASE_URL.

2. PostgreSQL configuration on Render

Migrating a Python Chatbot from SQLite to PostgreSQL for Persistent Deployment on Render. In this part of post, you’ll migrate a Python Flask chatbot from SQLite to PostgreSQL, using SQLAlchemy and environment variables for secure and scalable deployment.

Steps to Deploy Chatbot with PostgreSQL on Render

Step 1. Add PostgreSQL Database in Render

  1. Go to your Render dashboard

  2. Click “Add New” → “PostgreSQL”

  3. Give your database a name like chatbot_db

  4. Choose the free tier unless you need more storage/performance

  5. After it’s created, go to the database page → click “Connection” (Connect)

  6. Copy the External Database URL (it looks like postgres://user:pass@host:port/dbname) for me: (postgresql://chatbot_db_z3f8_user:IkQGqICGvFKHIhNHS0aikxM4wr9JDhOW@dpg-d0ud97mmcj7s739inqrg-a.frankfurt-postgres.render.com/chatbot_db_z3f8)

Step 2. Update Environment Variables

Go to your chatbot Web Service on Render:

  1. Click on your service → Settings → Environment

  2. Add or update the following variable:

Key: DATABASE_URL
Value: <paste your PostgreSQL connection string here>

connection string thatyou have copied in  the step 1.

Make sure DATABASE_URL matches the variable name used in your db.py or settings.

💡 Also ensure other env vars like OPENAI_API_KEY are still there.

like as following:

migrate-chatbot-from-sqlite-to-postgresql-1.png

Step 3: Push Latest Code to Git

Make sure your changes (migrated to PostgreSQL) are committed and pushed to the repo connected to Render:

git add .
git commit -m "Migrate chatbot to PostgreSQL"
git push

Step 4: Verify: Create Table on First Run

If you’re calling create_tables() in if __name__ == "__main__" block, Render will run it on deployment.

Make sure this is in your chatbot.py:

if __name__ == "__main__":
    create_tables()  # Creates the chats table if it doesn't exist
    app.run(debug=True, use_reloader=False, use_debugger=False)

Step 5: Check Logs

If anything fails:

  • Go to Render dashboard → your service → Logs

  • Look for errors like:

    • psycopg2.OperationalError

    • relation "chats" does not exist (means table creation logic failed)

    • .env parsing errors

Step 6: Test Deployment

Once live:

  • Open your Render app URL (e.g. https://yourchatbot.onrender.com)

  • Send some messages to the chatbot

  • Refresh page or come back later — history should persist ✅

Step 7: (Optional) Disable SQLite Completely

You can now safely delete:

  • Old chatbot.db file (local)

  • Any old sqlite3.connect() code if still present

Pro Tip: Hide Secret Info

Make sure .env is in .gitignore:

Pro Tip: Hide Secret Info
Make sure .env is in .gitignore:

Summary of What You Configured for PostgreSQL on Render:

Step What You Did
✅ 1 Created a PostgreSQL database
✅ 2 Added DATABASE_URL as env variable
✅ 3 Switched from SQLite to SQLAlchemy with PostgreSQL
✅ 4 Deployed to Render and tested
✅ 5 Verified data is persistent via PostgreSQL
All Code is on My Github
Conclusion

In this post we have gone through Migration of Chatbot from SQLite to PostgreSQL both in development (local) and deployment on Render.

The next post is: Add Voice functionality to chatbot

This post is part of  AI (Artificial Intelligence) step by step

Back to home page