Databases
Overview
AnswerAI supports four database types:
- SQLite (Default)
- MySQL
- PostgreSQL
- MariaDB
This guide will walk you through the process of configuring each database type for use with AnswerAI.
SQLite (Default)
SQLite is the default database for AnswerAI. To configure it, use the following environment variables:
DATABASE_TYPE=sqlite
DATABASE_PATH=/root/.answerai #your preferred location
A database.sqlite
file will be created and saved in the path specified by DATABASE_PATH
. If not specified, the default storage path will be in your home directory -> .answerai
Note: If no database environment variables are specified, SQLite will be the fallback database choice.
MySQL
To configure MySQL, use the following environment variables:
DATABASE_TYPE=mysql
DATABASE_PORT=3306
DATABASE_HOST=localhost
DATABASE_NAME=answerai
DATABASE_USER=user
DATABASE_PASSWORD=123
Ensure that you have created the database and granted the necessary permissions to the user before connecting AnswerAI.
PostgreSQL
For PostgreSQL configuration, use these environment variables:
DATABASE_TYPE=postgres
DATABASE_PORT=5432
DATABASE_HOST=localhost
DATABASE_NAME=answerai
DATABASE_USER=user
DATABASE_PASSWORD=123
PGSSLMODE=require
Make sure you have created the database and granted the appropriate permissions to the user before connecting AnswerAI.
MariaDB
To configure MariaDB, use the following environment variables:
DATABASE_TYPE=mariadb
DATABASE_PORT=3306
DATABASE_HOST=localhost
DATABASE_NAME=answerai
DATABASE_USER=answerai
DATABASE_PASSWORD=mypassword
As with the other database types, ensure that you have created the database and granted the necessary permissions to the user before connecting AnswerAI.
Database Synchronization in Production
AnswerAI uses TypeORM to configure database connections. By default, the synchronize
option is set to true
. This means that the database schema will be automatically created on every application launch.
Warning: Be cautious with this option in production environments. Enabling synchronize
in production can lead to data loss. This option is primarily useful during development and debugging.
To override the synchronize
value, set the following environment variable:
OVERRIDE_DATABASE=false
Best Practices
-
Development Environment:
- Use SQLite for quick setup and testing.
- Enable
synchronize
to automatically update the schema during development.
-
Production Environment:
- Use a more robust database like MySQL, PostgreSQL, or MariaDB.
- Disable
synchronize
by settingOVERRIDE_DATABASE=false
. - Implement proper database migration strategies for schema changes.
-
Security:
- Use strong, unique passwords for your database users.
- Limit database user permissions to only what's necessary for AnswerAI.
- Use SSL/TLS connections when possible, especially if your database is on a different server.
-
Performance:
- Regularly maintain and optimize your database.
- Monitor database performance and scale resources as needed.
Troubleshooting
- If you encounter connection issues, verify that the database server is running and accessible from your AnswerAI instance.
- Double-check your environment variables for typos or incorrect values.
- Ensure that the specified database and user exist and have the necessary permissions.
- Check AnswerAI logs for any database-related error messages.
Video Tutorial
For a visual guide on using SQLite and MySQL/MariaDB with AnswerAI, watch this tutorial:
Remember to always backup your data before making any changes to your database configuration or performing migrations.