0

I added an ENUM property to an entity in a Symfony 5 based project:

MyEntity

/**
 * @var string
 *
 * @ORM\Column(type="string", columnDefinition="ENUM('foo', 'bar', 'buz')", options={"comment": "Lorem ipsum...."})
 */
private $myEnum = 'foo';

config.yaml

doctrine:
    dbal:
        driver: pdo_mysql
        server_version: 5.7
        host: '%database_host%'
        port: '%database_port%'
        dbname: '%database_name%'
        user: '%database_user%'
        password: '%database_password%'
        charset: UTF8
        mapping_types:
            enum: string

It works fine. But the PHPUnit tests run against SQLite. As result, I'm getting errors like this one:

Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing 'CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ... my_enum ENUM('foo', 'bar', 'buz'), ...)':
SQLSTATE[HY000]: General error: 1 near "'foo'": syntax error

Is it possible / How to get the combination ENUM type + Doctrine + SQLite working? (At least in this case, for PHPUnit within a Symfony project)

automatix
  • 14,018
  • 26
  • 105
  • 230

1 Answers1

1

columnDefinition is (or at least can be) platform dependent.

As mentioned in the docs:

columnDefinition: DDL SQL snippet that starts after the column name and specifies the complete (non-portable!) column definition.

Note above (emphasis mine) the "non-portable" bit.

Since ENUM is not supported by SQLite, you cannot use it there.

If you need to enforce an enum-like field and do it in a way that's platform independent, you'll need to do it in code, not on your mapping configuration.

E.g. define the restriction on the constructor/setters, etc, checking that the value for $foo is within certain accepted values, with an assertion or anything like that.

If your integration testing infrastructure does not fully match your real application platform, using anything that platform dependent is going to be risky or even impossible.

This is true of mapping, but also valid for any queries that you perform that leverage certain platform features, but that may not exist in SQLite.

(If you wanted to have enum-like functionality on SQLite, you could do it with something like CHECK constraints in your columnDefinition for the field, but then that wouldn't work with MySQL).

yivi
  • 42,438
  • 18
  • 116
  • 138
  • Thank you for your answer! Yes, SQLite doesn't support the `ENUM` data type directly, but it provides a way to achieve the same via constraints, e.g.: `TEXT CHECK( product_Type IN ('A','B','C') )` (s. [here](https://www.educba.com/sqlite-enum/)). So I assumed, Doctrine would find the `columnDefinition` and handle that the DBAL specific way. But it seems to have been a wrong assumption... ) – automatix Mar 17 '22 at 15:06
  • Yup, I mentioned the `CHECK` constraints in my answer. The `columnDefinitions` are not managed by doctrine. You are telling the engine "I want this definition exactly, pass it to the DB server". If you want portability among different DBs, stay clear from `columnDefinitions` (or make sure the definitions you are using are legal in any platform you want to run your code). Same with custom SQL queries. – yivi Mar 17 '22 at 15:09