(How) Is it possible to use ENUMs in Doctrine and an SQLite database?

Question

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)

Answer 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).