tortoise / tortoise/tortoise-orm

bulk_create not compatible with mariadb

Open
#1,077 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Describe the bug
Using bulk_create to insert and update values on mariadb causes incompatible sql code to be generated

To Reproduce

   users = [
        model.User(user_id="1",name="James",email="james@email.com"),
        model.User(user_id="2",name="Claire",email="claire@email.com")
    ]

    await model.User.bulk_create(users, update_fields=["name","email"], on_conflict="user_id", batch_size=3)

Expected behavior
The SQL generated and passed is:

INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','james@email.com') 
    AS `new_User` ON DUPLICATE KEY UPDATE `name`='email',`name`='email'

(as it happens I am not sure why name=email is listed twice when I would expect name=name, email=email - so not even sure this would completely work on mysql)

But, on mariadb, the syntax needed is:

INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','james@email.com') 
     ON DUPLICATE KEY UPDATE `name`=VALUES(name),`email`=VALUES(email)

Additional context
Mysql 8 has deprecated VALUES - it does work, but gives a warning (as I understand it), this might require a dialect configuration. Or accept that VALUES works on both mysql and mariadb, even though it generates a warning on mysql

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at the bulk_create implementation and the SQL generation path for MySQL-compatible databases, then reproduce the supplied example against MariaDB. Done means bulk_create with update_fields and on_conflict produces valid MariaDB SQL with the correct column mappings, while preserving appropriate MySQL behavior; add or run coverage for this case if the existing test layout is found.

Written by the indexing model from the issue text.

Assessment

Tech stack
mariadb, python
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.