test(integration): run integration test for TiDB #14631

Mini256 · 2022-08-03T08:32:05Z

What does this PR do?

In this PR, I will replicate the MySQL integration test case to test TiDB.

About TiDB

TiDB (/’taɪdiːbi:/, "Ti" stands for Titanium) is an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads. It is MySQL compatible and features horizontal scalability, strong consistency, and high availability. The goal of TiDB is to provide users with a one-stop database solution that covers OLTP (Online Transactional Processing), OLAP (Online Analytical Processing), and HTAP services. TiDB is suitable for various use cases that require high availability and strong consistency with large-scale data.

GitHub Repo: pingcap/tidb (with 34.2k🌟 and 5.5k🍴)
TiDB Cloud (provide serverless cluster for developers)
Document about MySQL Compatibility

Test steps

Build the prisma binary

pnpm i
pnpm run build

Create the tidb testcases via copying the mysql testcase

cd packages/integration-tests
cp -r ./src/__tests__/integration/mysql ./src/__tests__/integration/tidb

Start up a TiDB cluster via tiup playground

# Install the deployment tool TiUP.
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
# Start a minimum cluster locally.
tiup playground v7.0.0 --without-monitor --tiflash 0

Run the testes:

export TEST_TIDB_BASE_URI="mysql://root@127.0.0.1:4000"
pnpm run test:tidb

Found issues

In the first running of test, 4 test cases failed:

introspection: findUnique where PK with include (The naming strategy of constraint and the index generated by the foreign key is incompatible with MySQL. To fix it, modify the jest snapshot, for example: the default constraint name in MySQL is posts_ibfk_1, but in TiDB is fk_1)
introspection: findUnique where unique with foreign key and unpack (ditto)
introspection: findUnique where composite PK with foreign key (ditto)
runtime: findMany where in[string] (If the SQL statement does not declare order by, TiDB does not guarantee the order of the returned results, so to fix it, add orderBy: { job: 'asc' } for the testcase)

The first running result

➜  integration-tests git:(test-for-tidb) ✗ pnpm run test:tidb                                   

> @prisma/integration-tests@0.0.0 test:tidb /Users/liangzhiyuan/Projects/prisma/packages/integration-tests
> jest integration.tidb --maxConcurrency=1 --silent

 FAIL  src/__tests__/integration/tidb/runtime.test.ts
  ● Test suite failed to run

    TypeError: Do not know how to serialize a BigInt
        at stringify (<anonymous>)

      at messageParent (../../node_modules/.pnpm/jest-worker@29.5.0/node_modules/jest-worker/build/workers/messageParent.js:29:19)

 FAIL  src/__tests__/integration/tidb/introspection.test.ts (23.615 s)
  ● introspection: findUnique where PK with include

    expect(received).toMatchSnapshot(hint)

    Snapshot name: `introspection: findUnique where PK with include: datamodel 1`

    - Snapshot  - 2
    + Received  + 2

    @@ -10,13 +10,13 @@

      model posts {
        id      BigInt @id @default(autoincrement()) @mysql.UnsignedBigInt
        user_id BigInt @mysql.UnsignedBigInt
        title   String @mysql.VarChar(50)
    -   users   users  @relation(fields: [user_id], references: [id], onDelete: NoAction, map: "posts_ibfk_1")
    +   users   users  @relation(fields: [user_id], references: [id], onDelete: NoAction, map: "fk_1")

    -   @@index([user_id], map: "user_id")
    +   @@index([user_id], map: "fk_1")
      }

      model users {
        id    BigInt  @id @default(autoincrement()) @mysql.UnsignedBigInt
        email String  @unique(map: "email") @mysql.VarChar(50)

      201 |       states[scenario.name] = state
      202 |
    > 203 |       expect(introspectionResult.datamodel).toMatchSnapshot(`datamodel`)
          |                                             ^
      204 |       expect(introspectionResult.warnings).toMatchSnapshot(`warnings`)
      205 |
      206 |       await teardownScenario(state)

      at toMatchSnapshot (src/__tests__/__helpers__/integrationTest.ts:203:45)

  ● introspection: findUnique where unique with foreign key and unpack

    expect(received).toMatchSnapshot(hint)

    Snapshot name: `introspection: findUnique where unique with foreign key and unpack: datamodel 1`

    - Snapshot  - 2
    + Received  + 2

    @@ -10,13 +10,13 @@

      model posts {
        id      BigInt @id @default(autoincrement()) @mysql.UnsignedBigInt
        user_id BigInt @mysql.UnsignedBigInt
        title   String @mysql.VarChar(50)
    -   users   users  @relation(fields: [user_id], references: [id], onDelete: NoAction, map: "posts_ibfk_1")
    +   users   users  @relation(fields: [user_id], references: [id], onDelete: NoAction, map: "fk_1")

    -   @@index([user_id], map: "user_id")
    +   @@index([user_id], map: "fk_1")
      }

      model users {
        id    BigInt  @id @default(autoincrement()) @mysql.UnsignedBigInt
        email String  @unique(map: "email") @mysql.VarChar(50)

      201 |       states[scenario.name] = state
      202 |
    > 203 |       expect(introspectionResult.datamodel).toMatchSnapshot(`datamodel`)
          |                                             ^
      204 |       expect(introspectionResult.warnings).toMatchSnapshot(`warnings`)
      205 |
      206 |       await teardownScenario(state)

      at toMatchSnapshot (src/__tests__/__helpers__/integrationTest.ts:203:45)

  ● introspection: findUnique where composite PK with foreign key

    expect(received).toMatchSnapshot(hint)

    Snapshot name: `introspection: findUnique where composite PK with foreign key: datamodel 1`

    - Snapshot  - 2
    + Received  + 2

    @@ -18,10 +18,10 @@

      model b {
        id  BigInt @id @default(autoincrement()) @mysql.UnsignedBigInt
        one Int
        two Int
    -   a   a      @relation(fields: [one, two], references: [one, two], onDelete: NoAction, onUpdate: NoAction, map: "b_ibfk_1")
    +   a   a      @relation(fields: [one, two], references: [one, two], onDelete: NoAction, onUpdate: NoAction, map: "fk_1")

    -   @@index([one, two], map: "one")
    +   @@index([one, two], map: "fk_1")
      }
      ↵

      201 |       states[scenario.name] = state
      202 |
    > 203 |       expect(introspectionResult.datamodel).toMatchSnapshot(`datamodel`)
          |                                             ^
      204 |       expect(introspectionResult.warnings).toMatchSnapshot(`warnings`)
      205 |
      206 |       await teardownScenario(state)

      at toMatchSnapshot (src/__tests__/__helpers__/integrationTest.ts:203:45)

 › 3 snapshots failed.
Snapshot Summary
 › 3 snapshots failed from 1 test suite. Inspect your code changes or run `npm run test:tidb -- -u` to update them.

Test Suites: 2 failed, 2 total
Tests:       3 failed, 62 passed, 65 total
Snapshots:   3 failed, 127 passed, 130 total
Time:        23.809 s

Finally, after some minor changes mentioned above, all test cases can be passed by TiDB.

CLAassistant · 2022-08-03T08:32:12Z

All committers have signed the CLA.

Mini256 · 2022-08-03T08:44:03Z

If it is possible, I would like to add this set of tests to GitHub Action.

packages/integration-tests/src/__tests__/integration/tidb/__database.ts

Jolg42 · 2022-08-03T09:40:47Z

Hi @Mini256

Thanks for your interest in adding tests for a new database (TiDB)! I'm also curious about it, though let me explain the situation about new databases:

Prisma is currently focusing on the existing supported database providers (PostgreSQL, MySQL, MariaDB, CockroachDB, SQLite & MongoDB), and we do not plan to add or put effort to support new databases at the moment.

About adding support to a new database, from our experience, it requires a lot of effort and often additional code even when a database claims to be "fully wire compatible with X".
The tests you added here are only a small step, and it would require many changes to fully tests all Prisma parts here in prisma/prisma monorepo and https://github.com/prisma/prisma-engines for example.

It would be helpful for us if you could open a feature request issue here about adding support for TiDB to Prisma, so we can collect all the interest from different people in one place. Feel free to add context about this, it really helps to understand why this would be important, for example.

Jolg42 · 2022-08-03T10:04:35Z

Note @Mini256

So, this would only be merged if we decide that

from a product perspective, it makes sense
tests in this git monorepo pass
tests in prisma-engines also fully pass

If, for example, tests would all pass, and this is essentially the same as a currently supported provider like MySQL/MariaDB, that might change our opinion, and we might be much more open to talk about it for getting a product decision 👍🏼

janpio · 2022-08-03T10:10:56Z

Thanks for your PR @Mini256, exciting. The most important thing that Joel wrote is our wish for a feature request issue for TiDB support. That is where everything starts in our process.

Still, this PR is great as it just shows what is working, and what is not.

To give you some more input:

Because TiDB currently does not support foreign keys (which is working in process: pingcap/tidb#18209), test cases 1., 2. and 3. cannot pass. I set them to todo: true, once the foreign keys feature is supported, I will reopen these use cases.

We actually already support PlanetScale/Vitess that also does not support foreign keys (you can read more about this here: https://www.prisma.io/docs/guides/database/using-prisma-with-planetscale), but are not explicitly testing that here in this repository. In prisma-engines, our other repository, we have a full test suite running against Vitess to make sure we cover all the queries and specialties of that database. That might be your next step to see what works, and what does not for TiDB. (The testing setup is a bit more complex, so feel free to ask about details if you can not figure it out).

ghimsim · 2022-08-03T14:29:27Z

Hello Jan and Joel,

Thank you for your detailed responses. We have filed a feature request to support TiDB to Prisma: #14639. We are also completing part of the monorepo tests. We will try the tests on prisma-engines next and keep you guys posted.

I totally understand your need to prioritize your support for databases. However, we are an open source database trying to get traction in the marketplace. We need support from great software like Prisma to get going, so we have somewhat of a chicken and egg thing here - to get more customers we need Prisma, but to get into Prisma, we need more customers.

If we are willing to put in engineering resources to help with the tests, integration and anything else, would you be more open to adding TiDB to your list of supported databases?

Ghim

janpio · 2022-08-03T18:44:40Z

Thanks @ghimsim for that context.

Disclaimer: I can only speak for the Engineering org side of Prisma, not the Product one (but their concerns would probably be similar).

As you certainly know, even if you help us develop this initially, there is a long tail of follow on costs that Prisma will have to carry. Random examples: Longer test runtimes, additional complexity in developing new features, additional things to keep in mind when debugging or making changes. Those are all real costs that can not be prevented even if you promise to help us with this "forever".

So currently this PR and discussion is mostly figuring out the current state of Prisma + TiDB:

If TiDB indeed turns out to be equivalent to Vitess/PlanetScale (so a quirky variant of MySQL), then we would post that in the feature request issue and could then have an internal discussion with Prisma if we are officially willing to carry declare that and carry TiDB as a supported database.
- (If we decide not to, this would still allow you to claim inofficial support by Prisma via mysql and referentialIntegrity=prisma - which would basically be the same thing for your users.)
If it is not, that means either TiDB or Prisma needs to change in some way to get to that state anyway.
- In coordination with us you could certainly contribute changes to Prisma, that could bring us closer - but of course we would need to check if we take on additional responsibility because of that. There might be things we do not want to accept.
- We could also help you to understand what TiDB needs to change or adapt to be able to pass our tests.
- Maybe this leads to us creating additional feature request issues (similar to our preview feature referentialintegrity=prisma) for things that could be useful for all databases. That would then again need prioritization from us, but bring us closer to support that functionality both for TiDB and others.

So for now, figuring out exactly what is happening here and seeing what happens in prisma-engines seems like good next steps to me (now that the feature request issue was created).

janpio · 2022-11-03T20:14:08Z

@Mini256 took this suggestion, and prisma/prisma-engines#3308 now exists <3

Mini256

update image to v7.0.0

.buildkite/test/docker-compose.14.yml

codspeed-hq · 2023-05-22T15:05:02Z

CodSpeed Performance Report

Merging #14631 Mini256:test-for-tidb (a168f44) will degrade performances by 58.0%.

Summary

🔥 0 improvements
❌ 1 regressions
✅ 3 untouched benchmarks

🆕 0 new benchmarks
⁉️ 0 dropped benchmarks

⚠️ Please fix the performance issues or acknowledge them on CodSpeed.

Benchmarks breakdown

	Benchmark	`main`	`Mini256:test-for-tidb`	Change
❌	`typescript compilation ~50 Models`	98.3 ms	332.3 ms	-237.93%

Unblocks testing forks Example: #14631 [skip ci]

Jolg42 · 2023-05-22T15:13:11Z

Note: I noticed tests were not running and fixed that in 82a1154
Now tests are running like expected.

laplab · 2024-04-11T12:24:12Z

Thank you for submitting this PR, @Mini256!

We mentioned in our documentation that TiDB has a community-maintained driver adapter. This driver adapter already allows users to effortlessly query TiDB databases. Thus, we currently do not plan to merge this PR and prisma/prisma-engines#3308.

test: run integration test for TiDB

dcd939a

Mini256 requested review from a team and aqrln and removed request for a team August 3, 2022 08:32

sonatype-lift bot reviewed Aug 3, 2022

View reviewed changes

packages/integration-tests/src/__tests__/integration/tidb/__database.ts Show resolved Hide resolved

test: add to github action

9c76edb

Mini256 requested review from Jolg42 and jkomyno as code owners August 3, 2022 08:56

fix: add tidb docker image into the .buildkite

f602a38

Merge branch 'main' into test-for-tidb

1a3ea8b

Mini256 commented May 22, 2023

View reviewed changes

.buildkite/test/docker-compose.14.yml Outdated Show resolved Hide resolved

Mini256 added 2 commits May 22, 2023 22:31

Update .buildkite/test/docker-compose.14.yml

ce227ec

Merge branch 'main' into test-for-tidb

a168f44

Jolg42 added a commit that referenced this pull request May 22, 2023

ci(action): make docker credentials optional in test-template.yml

82a1154

Unblocks testing forks Example: #14631 [skip ci]

Merge branch 'main' into test-for-tidb

220edb7

Mini256 added 5 commits May 22, 2023 23:18

update the test snap

613e2e0

add tidb uri to env

8aee077

fix: add tidb base uri

781f25b

fix: docker compose up tidb container

6d31d9f

fix: add orderBy to fix findMany where in[string] case

8b2aa1c

Merge branch 'main' into test-for-tidb

d712c95

janpio requested a review from a team as a code owner June 6, 2023 10:18

Mini256 mentioned this pull request Nov 16, 2022

Support for TiDB #14639

Open

4 tasks

janpio self-assigned this Jun 20, 2023

SevInf added the PR: Feature A PR That introduces a new feature label Mar 20, 2024

laplab mentioned this pull request Apr 11, 2024

test: add TiDB into the engine tests prisma/prisma-engines#3308

Closed

laplab closed this Apr 11, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

test(integration): run integration test for TiDB #14631

test(integration): run integration test for TiDB #14631

test(integration): run integration test for TiDB #14631

test(integration): run integration test for TiDB #14631

Conversation

What does this PR do?

About TiDB

Test steps

Found issues

Choose a reason for hiding this comment

CodSpeed Performance Report

Summary

Benchmarks breakdown