ChatSQL
Flask Web Application to interact with a database AI assistant powered by OpenAI API.
Install / Use
/learn @georgijspi/ChatSQLREADME
0. Table of contents
- 0. Table of Contents
- 1. Introduction
- 2. System Architecture
- 3. High-Level Design
- 4. Problems and Resolution
- 5. Installation Guide
- 6. References
- 7. Appendix
1. Introduction
1.1 Overview
The ChatSQL project presents itself as a revolution to user interaction with databases. Driven by the need to make database interactions easier for people with little to no IT literacy, ChatSQL is an approachable assistant that helps with natural language discussions about data and databases without requiring a lot of SQL understanding.
Underneath the hood of ChatSQL, lies a pivotal feature which is; the integration of OpenAI's API with the use of a powerful library, LangChain, to manage the LLM logic of the application which is crucial for translating natural language into SQL queries and converting SQL output back into natural language responses. This allows the project to leverage OpenAI's advanced natural language processing abilities to process and interpret user queries posed in a layman's language in addition to the context of the database to convert said query into a valid and executable SQL code.
The application is integrated within a Flask backend environment which facilitates easy access to the Chat Bot. Flask is a light and powerful web application framework for Python. It exceeds as a leading tool for developers based on its simplicity and flexibility. The central aspect of our application is a dynamic interaction mechanism where users can upload, query, and interact with databases through a conversational interface.
Some core Flask functionality includes:
- uploading and downloading database files
- managing user sessions
- rendering HTML templates
- handling form data
The backend logic makes use of custom error handling, secure file processing, and conversation tracking via Flask sessions. The integration of SQLAlchemy allows the developers to update the sample databases with ease, along with some helper functions to edit this.
We utilise a modular architecture which is implemented using Flask's Blueprint system to organise and manage page routing, which provides for easy means of development, upkeep and scalability.
This document outlines the design and functionality of the Flask web application, from its configuration, database handling, Flask routes and the LLM access layer. Special emphasis is placed on the interaction between the Flask backend and the LLM layer, illustrating how the backend manages OpenAI queries and provides the user with a seamless and simple database querying method.
1.2 Glossary
Define and technical terms used in this document. Only include those with which the reader may not be familiar.
General terminology:
- LLM: Large Language Model
- Prompt:
- SQL:
- SQL Query:
SELECTSQL Query- [LLM] hallucination:
"POST"request:"GET"request:
Libraries and terminology used in chatbot.py:
- LangChain[1]: A python library used for managing interactions with AI pipelines and memory.
- OpenAI API[2]: An API(Application Programming Interface) which allows users to utilise the OpenAI machine learning models.
- LLMChain: LangChain component for handling LLM interactions.
- ConversationBufferWindowMemory: A memory handling component in LangChain for storing and handling conversation history.
- HumanMessagePromptTemplate: A template to construct messages in Langchain prompts.
- ChatPromptTemplate: LangChain template tool for creating prompts.
- sqlite3: Python library for interacting with SQLite databases.
- re: Python regex module for handling and formatting expressions.
- dotenv: Python library for loading environment variables from a file.
Flask terminology
- Flask[3]: A python-based web application framework.
- Blueprint: Flask feature which helps in organising a Flask application into components. Each Blueprint represents a set of operations/functionality.
- Session: Feature that allows storage of information specific to a user throughout the user's interactions. This is implemented on top of cookies.
- Route: A URL pattern in Flask applications which manages routing a specific function based on the URL.
- Jinja2 Template Rendering: templating language to dynamically build HTML files.
- SQLAlchemy[4]: An SQL toolkit and Object-Relational Mapping (ORM) library for Python.
- Flask-SQLAlchemy: An extension for Flask that adds support for SQLAlchemy with use of helpers and useful defaults.
- Werkzeug: Comprehensive WSGI application library. Used as a basis for Flask.
app.config: A dictionary-like object to store the configuration variables of the application.
2. System Architecture
The System Architecture is made up of a number of modules which seamlessly blend together to create ChatSQL.
The system architecture of the Flask web application is designed to be a robust and user-friendly chatbot experience with the integration NLP and database query capabilities. The architecture is modular, ensuring separation of features/modules and easy maintainability. Key components of the system include the Flask web framework and the OpenAI API for the LLM functionality of language processing, and SQLite for database management. This architecture brief demonstrates the actual design components of the system as implemented and demonstrated.
2.1 Filesystem:
code/
├── FlaskProto
│ ├── app.py
│ ├── chatbot.py
│ ├── chat_history.json
│ ├── config.py
│ ├── database.py
│ ├── db-descriptions
│ │ ├── sampleDB-1.html
│ │ ├── sampleDB-2.html
│ │ ├── sampleDB-3.html
│ │ └── sampleDB-4.html
│ ├── db_sample
│ │ ├── sample_1.sqlite3
│ │ ├── sample_2.sqlite3
│ │ ├── sample_3.sqlite3
│ │ └── sample_4.db
│ ├── db_uploads
│ │ └── # user uploaded database files
│ ├── instance
│ │ └── sample_databases.db
│ ├── requirements.txt
│ ├── routes.py
│ ├── static
│ │ └── images
│ │ ├── cube.svg
│ │ ├── hero.webp
│ │ ├── indent.svg
│ │ └── trans.svg
│ └── templates
│ ├── base.html
│ ├── chat.html
│ ├── index.html
| └── upload-db.html
2.2 Flask Web Framework:
At the heart of the system is the Flask web application framework, this serves as the backbone for handling HTTP requests & responses. Flask routes are defined within a Blueprint. Flask routes manage the web application's various endpoints such as:
- Homepage
- Database upload functionality
- Sample Database loading Flask's templating engine can render HTML templates for the user interface by dynamically controlling the content to be rendered as well as building out the website from different modules stored as HTML files.
Flask's powerful built-in session management tools are ideal for storing and handling user input, as well as disposing of user data when it is no longer in use.
2.2.1 app.py
The app.py file is used to load the config from config.py and run the Flask web application via the app.run(debug=True) line in the main function. This starts the web application on local address http://127.0.0.1:5000/http://localhost:5000 which can be accessed from a Browser from the same device that is locally running app.py.
Additionally some helper functions are included which are run from the terminal. These functions are intended to be used during development or as part of setting up the sample databases. They are used to configure and initialise the sample databases in a database model defined in database.py.
- Used to initialise the server's database:
@app.cli.command('init-db')
- Used to populate the server's database:
@app.cli.command('populate-db')
2.2.2 config.py
config.py is used to configure flask variables like folder locations, session and SQLAlchemy databases.
2.2.3 database.py
class SampleDatabase(db.Model): class is defined here which dictates the database model to be used to save and handle the sample databases. It adds columns for id, name, description_path, and database path.
2.2.4 routes.py
routes.py is used to define the routes as a blueprint which is then imported into the app.py file. This file incorporates routing for:
- homepage
- sample-db selection
- uploading database
- downloa
