How to switch from SQLite to Postgres in Botpress?

Botpress uses SQLite by default and provides Postgres support out of the box. In cases where you need high security, data integrity and reliability, you should use Postgres. Botpress supports Postgres 9.5 or higher. This tutorial will see how to switch from SQLite to Postgres in Botpress.

Main principle

• SQLite: Self contained, very lightweight, file based and fully open-source RDBMS known for its portability, reliability and strong performance even in low memory environments.
• Postgres: PostgreSQL, also known as PostgreSQL. It was created with the goal of being highly extensible and standards compliant. It is an object relational database and is capable of concurrency.

When to use SQLite and Postgres?

SQLite is highly useful for the following

• SQLite is highly useful for the following
• Standalone applications
• Small apps that do not require expansion
• Applications which need to read and write files to disk directly
• Developing and testing

Postgres is recommended for the following

• Data integrity and reliability is highly concerned
• Custom Procedures which is extensible to run complex tasks
• Complexity with ease. Postgres gives you the functionality to maintain such a complex database smoothly without limitations.

How to enable Postgres in Botpress?

Install Postgres 9.5 or higher and create a database. eg: botpressdb.

Set below 2 environment variables or create .env file with below content where bp.exe (on windows) or bp (on Linux) exists.

DATABASE=postgres DATABASE_URL=postgres://postgres:password@x.x.x.x:5432/botpressdb 

Optionally you can also set DATABASE_POOL to configure minimum and maximum DB connections via pool

 DATABASE_POOL={"min": 3, "max": 10} 

Now launch Botpress, after launch Botpress will automatically create the following 12 tables.

• srv_metadata
• srv_channel_users
• workspace_users
• workspace_invite_codes
• srv_logs
• dialog_sessions
• srv_ghost_files
• srv_ghost_index
• srv_notifications
• srv_kvs
• data_retention
• events

That’s it, Botpress is now using Postgres.

Note: Used Botpress v12.2.1