[FEATURE] Support multiple database back-ends

[gbaeke]

In deploying Flowise to Azure Container Apps, I had issues with mounting the SQLite database to a CIFS share. SQLite is great for simple deployments but one might want multiple instances of Flowise with a single backend or avoid having to mount SQLite on external storage in a cloud environment.

Implementing other backend databases should be relatively straightforward because of the use of typeorm. I have a modified version of Flowise running with MySQL as a backend and it works great (as far as my tests go). However, it would be great if this were supported by the project out of the box at some time in the feature.

In the meantime, thanks for your efforts with Flowise!

[HenryHengZJ]

thats exactly why we use typeorm so user can easily switch to mysql, postgresql and others databases.

if you dont mind do you have a link to the implementation of switching to mysql? or feel free to open a PR :)

[gbaeke]

I used the following in DataSource.ts:

appDataSource = new DataSource({

        type: 'mysql',

        host: process.env.SQLHOST,

        port: parseInt(process.env.SQLPORT || '3306'),

        username: process.env.SQLUSERNAME,

        password: process.env.SQLPASSWORD,

        database: process.env.SQLDATABASE,

        charset: 'utf8mb4',

        synchronize: false,

        entities: [ChatFlow, ChatMessage, Tool],

        migrations: [],

        ssl: {

            ca:  fs.readFileSync(__dirname + '/ca.pem')

        }

    })

In the container build I ensure ca.pem is copied to the correct folder. ca.pem is retrieved from Azure MySQL Server portal.

I also created the database in MySQL with charset utf8mb4 and collation utf8mb4_unicode. I got errors saving sourceDocuments in chat messages otherwise. Default charset for databases (in Azure MySQL at least) is utf8.

I also needed to change the entities to support longtext in fields. For example in ChatFlow.ts:

/* eslint-disable */
import { Entity, Column, CreateDateColumn, UpdateDateColumn, PrimaryGeneratedColumn } from 'typeorm'
import { IChatFlow } from '../Interface'

@Entity()
export class ChatFlow implements IChatFlow {
    @PrimaryGeneratedColumn('uuid')
    id: string

    @Column()
    name: string

    @Column({ type: "longtext" })
    flowData: string

    @Column({ nullable: true })
    apikeyid: string

    @Column()
    deployed: boolean

    @CreateDateColumn()
    createdDate: Date

    @UpdateDateColumn()
    updatedDate: Date
}

I also did that for longer fields in ChatMessage.ts and Tool.ts.

I did have issues with synchronize: true though. So I run that once locally to create the database and then turn that off in the container build. I guess there are solutions to this but I don't know enough about TypeORM and did not spend extra time in researching that.

[HenryHengZJ]

thanks @gbaeke this is helpful! amazing to see how people are customizing for their own use cases.

we'll be sure to add different database integration as we move closer to production

[atishhamte]

synchronize: false should be there in the production environment, you can lose your data on every build.

this option is for debug or dev mode where every time you need a fresh database

ref: https://typeorm.io/data-source-options#common-data-source-options

[chungyau97]

Hi @gbaeke, could you help verify/suggestion/enhance this documentation? Flowise Docs #9

[HenryHengZJ]

Updated docs - https://docs.flowiseai.com/databases and PR #575 merged