Is it possible to call a .sql file in a doctrine migration?

Question

Is it possible to call an .sql file in a migration class?

I could not find anything on that topic.

I don't think you can. You should launch it after your migration. What contains this sql file? — j0k, Nov 23 '12 at 11:02
it creates stored procedures and views and it is dynamically created — Hyperkubus, Nov 23 '12 at 11:08

score 18 · Answer 1 · answered Jun 17 '13 at 11:11

18

Yes, just put that .sql file and refer it from you migration class.

$this->addSql(file_get_contents(__DIR__ . '/sql-dump.sql'));

answered Jun 17 '13 at 11:11

Sreejith Chozhiyath

249
3
6

1

Does this refer to Doctrine 1.2.? I can't find this option :/ – Michal Trojanowski Jun 19 '13 at 08:59
1

What if SQL contains several queries divided by semicolon? – Serhii Smirnov Jun 23 '15 at 14:03
^ It works with multiple queries separated by semi-colons in the included SQL file (at least in Symfony 3.x; most probably works in Symfony 2.8+ as well ... just test it out). – Sawant May 18 '17 at 09:43
I doesn't work with SQLite, because Doctrine calls the query() method, which doesn't support multiple statements in a single string, and will execute only the first one. Multiple statements are supported only with the exec() method. See: https://www.php.net/manual/en/function.sqlite-query.php – Emanuele Jan 05 '20 at 21:57
Does not work with "doctrine/doctrine-migrations-bundle": "3.2.1", – BacLuc Jan 25 '22 at 14:48

score 2 · Answer 2 · answered Jun 02 '16 at 23:13

2

I know it's an old question, but it is still the only real hit when I googled for it. The above answer can be slightly improved. It's a simple thing, but you might not think of it. If you have a sql file that contains multiple queries divided by semicolon, you can explode the contents on semicolon.

<?php

foreach (explode(';', file_get_contents(__DIR__ . '/sql-dump.sql')) as $sql) {
    $this->addSql($sql);
}

answered Jun 02 '16 at 23:13

winkbrace

2,682
26
19

1

What if there will be `;` inside sql query? It may be not safe. May be ";\n" should be better. – luchaninov Dec 02 '16 at 19:58
No need for this, as this (`$this->addSql(file_get_contents(__DIR__ . '/sql-dump.sql'));`) works in case of multiple queries separated by semi-colons. – Sawant May 18 '17 at 09:45
I approve @luchaninov 's answer. This can work for a simple pet project, but is not robust for a production-grade application: - stored procedures will break - a single comment with a semicolon "-- This column to store blabiboduc; null means no blabiboduc on this entry" will break – Guillaume Outters Apr 12 '23 at 08:31

score 2 · Answer 3 · answered Jan 05 '20 at 22:11

If you are using SQLite and want to execute a .sql file which cointains multiple statements separated by semicolon, I suggest using this code:

$this->connection->exec(file_get_contents(__DIR__ . '/sql-dump.sql'));

This beacuse if you use addSql() method, Doctrine will call the query() method, which doesn't support multiple statements in a single string, and will execute only the first one. Multiple statements are supported only with the exec() method. See: php.net/manual/en/function.sqlite-query.php

score 1 · Answer 4 · answered Jun 27 '21 at 10:16

1

For MySQLi I used:

$this->connection->getWrappedConnection()->getWrappedResourceHandle()->multi_query($sql);

answered Jun 27 '21 at 10:16

Serhii Smirnov

1,338
1
16
24

score 0 · Answer 5 · answered Sep 16 '22 at 13:44

After doing some research this works for me
I am using
doctrine-migrations-bundle: "^3.0"
symfony version: 5.4.12
PostgreSQL version: 14.5

Procedure
pq_dump:

pg_dump --column-inserts --data-only -U postgres -h localhost db_name > file_name.sql

Migrations:

public function up(Schema $schema): void
{
    $this->connection->getNativeConnection()->exec(file_get_contents(__DIR__.'./../Sql/file_name.sql'));
}

Note: Make sure this line is comment out in your sql file.
SELECT pg_catalog.set_config('search_path', '', false);

Is it possible to call a .sql file in a doctrine migration?

5 Answers5