SQLite schema namespace adjustment incorrectly implied for ManyToMany inserts. #6601

New Issue

admin · 2026-01-22T15:35:33+01:00

admin commented

2026-01-22 15:35:33 +01:00

Originally created by @AlexHowansky on GitHub (Jan 6, 2021).

Many databases provide schema namespacing that can be used to segregate tables. The schema name can be prepended to the tablename with a namespace separator, typically a dot. For example, if you created the USERS table in the APP schema namespace, your select might look like SELECT APP.USERS.ID FROM APP.USERS.

Doctrine recognizes this dot notation for databases like Oracle that support schema namespaces. However, since SQLite does not, Doctrine translates APP.USERS to APP__USERS to provide a sort of faux namespacing. This feature works correctly when using migrations to create the table and when using Doctrine to query it, but is broken when inserting via a ManyToMany relationship.

If all the following are true:

The SQLite driver is used.
Table names are overridden in the entity metadata definition in order to provide a schema namespace.
A ManyToMany relationship via a join table is created.
An insert is performed via the ManyToMany relationship.

Then the table name of the join table is not translated to SCHEMA__TABLE as expected, and the insert fails because it will incorrectly refer to SCHEMA.TABLE.

For example, if we define a SQLite database in our .env file:

DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"

And create Student and Course entities like:

/**
 * @ORM\Entity()
 * @ORM\Table(name="APP.COURSE")
 */
class Course
{
    /**
     * @ORM\ManyToMany(targetEntity=Student::class, inversedBy="courses")
     * @ORM\JoinTable(name="APP.COURSE_STUDENT_JOIN")
     */
    private $students;

/**
 * @ORM\Entity()
 * @ORM\Table(name="APP.STUDENT")
 */
class Student
{
    /**
     * @ORM\ManyToMany(targetEntity=Course::class, mappedBy="students")
     */
    private $courses;

Then we can run migrations to create the database and we'll get the fake namespaced table names as expected:

% ./bin/console make:migration
% ./bin/console doctrine:migrations:migrate
% sqlite3 var/data.db ".tables"
APP__COURSE                  APP__STUDENT
APP__COURSE_STUDENT_JOIN     doctrine_migration_versions

We can successfully use the entities to create new rows:

 $em->persist((new Course())->setName('Barology'));
 $em->persist((new Student())->setName('Jim'));
 $em->flush();

And to query them:

$barology = $em->getRepository(Course::class)->findOneByName('Barology');
$jim = $em->getRepository(Student::class)->findOneByName('Jim');

However, we'll get an error if we create a relationship that would result in an insert to the join table:

$jim->addCourse($barology);

An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2 FROM APP__COURSE t0 INNER JOIN APP.COURSE_STUDENT_JOIN ON t0.id = APP.COURSE_STUDENT_JOIN.course_id WHERE APP.COURSE_STUDENT_JOIN.student_id = ?' with params [1]:
SQLSTATE[HY000]: General error: 1 no such table: APP.COURSE_STUDENT_JOIN

Note in the query that Doctrine built above, APP.COURSE has been correctly translated into APP__COURSE, but APP.COURSE_STUDENT_JOIN has not. This occurs only for inserts into ManyToMany join tables -- inserts into other tables and read queries on the join tables (e.g., $jim->getCourses()) will be correctly translated.

The above code can be made to work by either removing the schema namespaces from the Entity metadata definitions or changing the database driver to something that supports schema namespaces. Thus, there is a simple workaround when using a single database platform. However, applying this workaround makes the entities no longer transportable or agnostic, and prevents me (for example) from using Oracle for production and SQLite for test.

I've got a minimal repo here to demonstrate: https://github.com/AlexHowansky/doctrine_sqlite_bug

See the src/Command/TestCommand.php file for an example containing the code noted here.

doctrine:common 3.1.0
doctrine:dbal 2.12.1
doctrine:orm 2.8.1

Originally created by @AlexHowansky on GitHub (Jan 6, 2021). Many databases provide schema namespacing that can be used to segregate tables. The schema name can be prepended to the tablename with a namespace separator, typically a dot. For example, if you created the `USERS` table in the `APP` schema namespace, your select might look like `SELECT APP.USERS.ID FROM APP.USERS`. Doctrine recognizes this dot notation for databases like Oracle that support schema namespaces. However, since SQLite does not, Doctrine translates `APP.USERS` to `APP__USERS` to provide a sort of faux namespacing. This feature works correctly when using migrations to create the table and when using Doctrine to query it, but is broken when inserting via a ManyToMany relationship. If all the following are true: - The SQLite driver is used. - Table names are overridden in the entity metadata definition in order to provide a schema namespace. - A ManyToMany relationship via a join table is created. - An insert is performed via the ManyToMany relationship. Then the table name of the join table is not translated to `SCHEMA__TABLE` as expected, and the insert fails because it will incorrectly refer to `SCHEMA.TABLE`. For example, if we define a SQLite database in our `.env` file: `DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"` And create Student and Course entities like: ``` /** * @ORM\Entity() * @ORM\Table(name="APP.COURSE") */ class Course { /** * @ORM\ManyToMany(targetEntity=Student::class, inversedBy="courses") * @ORM\JoinTable(name="APP.COURSE_STUDENT_JOIN") */ private $students; ``` ``` /** * @ORM\Entity() * @ORM\Table(name="APP.STUDENT") */ class Student { /** * @ORM\ManyToMany(targetEntity=Course::class, mappedBy="students") */ private $courses; ``` Then we can run migrations to create the database and we'll get the fake namespaced table names as expected: ``` % ./bin/console make:migration % ./bin/console doctrine:migrations:migrate % sqlite3 var/data.db ".tables" APP__COURSE APP__STUDENT APP__COURSE_STUDENT_JOIN doctrine_migration_versions ``` We can successfully use the entities to create new rows: ``` $em->persist((new Course())->setName('Barology')); $em->persist((new Student())->setName('Jim')); $em->flush(); ``` And to query them: ``` $barology = $em->getRepository(Course::class)->findOneByName('Barology'); $jim = $em->getRepository(Student::class)->findOneByName('Jim'); ``` However, we'll get an error if we create a relationship that would result in an insert to the join table: ``` $jim->addCourse($barology); ``` ``` An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2 FROM APP__COURSE t0 INNER JOIN APP.COURSE_STUDENT_JOIN ON t0.id = APP.COURSE_STUDENT_JOIN.course_id WHERE APP.COURSE_STUDENT_JOIN.student_id = ?' with params [1]: SQLSTATE[HY000]: General error: 1 no such table: APP.COURSE_STUDENT_JOIN ``` Note in the query that Doctrine built above, `APP.COURSE` has been correctly translated into `APP__COURSE`, but `APP.COURSE_STUDENT_JOIN` has not. This occurs only for inserts into ManyToMany join tables -- inserts into other tables and read queries on the join tables (e.g., `$jim->getCourses()`) will be correctly translated. The above code can be made to work by either removing the schema namespaces from the Entity metadata definitions or changing the database driver to something that supports schema namespaces. Thus, there is a simple workaround when using a single database platform. However, applying this workaround makes the entities no longer transportable or agnostic, and prevents me (for example) from using Oracle for production and SQLite for test. I've got a minimal repo here to demonstrate: https://github.com/AlexHowansky/doctrine_sqlite_bug See the `src/Command/TestCommand.php` file for an example containing the code noted here. doctrine:common 3.1.0 doctrine:dbal 2.12.1 doctrine:orm 2.8.1

admin added the Bug label 2026-01-22 15:35:33 +01:00

admin commented

2026-01-22 15:35:34 +01:00

@AlexHowansky commented on GitHub (Jan 11, 2021):

OK I think I found the problem. Doctrine\ORM\Mapping\DefaultQuoteStrategy::getJoinTableName() only works if the schema name and the table name are specified separately:

@ORM\JoinTable(name="COURSE_STUDENT_JOIN", schema="APP")

While Doctrine\DBAL\Platforms\SqlitePlatform allows the schema name to be specified as part of the table name, via dot notation:

@ORM\JoinTable(name="APP.COURSE_STUDENT_JOIN")

It seems perhaps the intent was to allow changing the dot to two underscores after the full schema+table string had been built. Can dots be disallowed in table names based on the currently selected driver? I.e., disallowing dots in SQLite table names would force use of the explicit schema syntax, which avoids this issue.

@AlexHowansky commented on GitHub (Jan 11, 2021): OK I think I found the problem. `Doctrine\ORM\Mapping\DefaultQuoteStrategy::getJoinTableName()` only works if the schema name and the table name are specified separately: @ORM\JoinTable(name="COURSE_STUDENT_JOIN", schema="APP") While `Doctrine\DBAL\Platforms\SqlitePlatform` allows the schema name to be specified as part of the table name, via dot notation: @ORM\JoinTable(name="APP.COURSE_STUDENT_JOIN") It seems perhaps the intent was to allow changing the dot to two underscores *after* the full schema+table string had been built. Can dots be disallowed in table names based on the currently selected driver? I.e., disallowing dots in SQLite table names would force use of the explicit schema syntax, which avoids this issue.

admin referenced this issue

2026-01-22 16:06:18 +01:00

[PR #6601] Fix double spacing in "cascade persist missing" exception message #10063

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6601