Make Dify compatible with MySQL database

[powerfooI]

Checklist:

[!IMPORTANT]
Please review the checklist below before submitting your pull request.

• [x] Please open an issue before creating a PR or link to an existing issue
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [x] I ran dev/reformat(backend) and cd web && npx lint-staged(frontend) to appease the lint gods

Description

As issue #8343 said, some organizations are expecting to take mysql as database of dify, while there are some postgresql native usages in dify's definition of models that make seamless switch to mysql difficult.

What this PR does are mainly as following,

1. Replace some server side postgresql statements like 'some words'::text with dialect irrelevant ones. Concretely, I did the following replacements.

server_default=db.text("uuid_generate_v4()") --> default=lambda: uuid.uuid4()
server_default=db.text("true") --> default=True
server_default=db.text("false") --> default=False
server_default=db.text\("'(.*)'::character varying"\) --> default="$1"
server_default=db.text\("'(.*)'::text"\) --> server_default=db.text("$1")
server_default=db.text("CURRENT_TIMESTAMP(0)") --> server_default=func.current_timestamp()
db.Column(db.Text, ..., server_default="...") --> db.Column(db.Text, ..., default="...")

2. Ignore direct JSON index: MySQL does not support indexing JSON column directly. (Reference)

3. Add env vars and docker-compose files for MySQL.

4. Create a separate migrations folder for mysql database and update upgrade-db command.

This PR resolves #8343 and is related to #415, #3813, #2371.

Type of Change

• [ ] Bug fix (non-breaking change which fixes an issue)
• [x] New feature (non-breaking change which adds functionality)
• [ ] Breaking change (fix or feature that would cause existing functionality to not work as expected)
• [ ] This change requires a documentation update, included: Dify Document
• [ ] Improvement, including but not limited to code refactoring, performance optimization, and UI/UX improvement
• [ ] Dependency upgrade

Testing Instructions

Assume a main workflow: User opens the dify webpage, signs up the first account, logs in to the webpage, creates a dataset, uploads several papers, dify embeds the documents, the user does hit tests, creates workflow to retrieve the document segment, dify generates answer for user's question with help of LLM.

I have tested the changes with the main workflow on the following deployment x database matrix and all tests passed.

Deployment	Database	Test Result
Docker-compose	PostgreSQL 15.8 (default)	✅
Docker-compose	MySQL 5.7.44	✅
Docker-compose	MySQL 8.0.39	✅
Middleware	PostgreSQL 15.8 (default)	✅
Middleware	MySQL 5.7.44	✅
Middleware	MySQL 8.0.39	✅
Middleware	OceanBase 4.2.1.7	✅

Migrations Compatibility

This PR takes a snapshot of the old migrations' head and create a new migrations folder to make dify compatible with both postgresql and mysql. For users using dify of previous version, they should do flask db upgrade and flask db upgrade -d migrations_new. For users using dify with mysql database, they only need to do flask db upgrade -d migrations_new. In other word, users using pg and users using mysql will get the identical database schema at the snapshot point.

[laipz8200]

I think we're not quite ready to maintain two sets of migrations yet.

[powerfooI]

Actually we just need to archive the old migrations and create future revisions in new migrations.

[qiqizjl]

Is there any further update? MySQL is also a common database

[powerfooI]

Is there any further update? MySQL is also a common database

Hi @qiqizjl , I forked dify in oceanbase-devhub/dify and made it compatible with mysql database. We deployed it over one hundred times during out workshop events and it worked fine. If you are interested in taking mysql as the database of Dify, we recommend that you can try this repo: https://github.com/oceanbase-devhub/dify.

[joeylee2023]

Is there any further update? MySQL is also a common database

Hi @qiqizjl , I forked dify in oceanbase-devhub/dify and made it compatible with mysql database. We deployed it over one hundred times during out workshop events and it worked fine. If you are interested in taking mysql as the database of Dify, we recommend that you can try this repo: https://github.com/oceanbase-devhub/dify.

---

This version works for mysql? thx

[junjiem]

Entities should not be multiplied unnecessarily.