Laravel Execute sql queries from an sql file

Question

Because for testing there's no testing database I use a manually generated sql script to clean a database clone of my production database. Assuming that my the legacy database is the following:

ohimesama
id: PK
namae: Varchar (200)

oujisama:
id: pk
namae: Varchar(200)


ohimesamagasuki:
id: pk
ohimesama_id: fk ohimesama
oujisama_id: fk oujisama

And the test database cleanup sql (cleanup.sql) script is:

DELETE * from ohimesama where namae not in ['Gardinelia', 'Jasmine'];
DELETE * from oujisama where namae not in ['Gaouron', 'Sasuke','Aladin'];
DELETE * from ohimesamagasuki where ohimesama_id not in (SELECT id from ohimesama) and oujisma not in
(SELECT id from oujisama);

And because I want to be able to execute all theese commands with one transaction IU want to be able to read the cleanup.sql file and execute the sql commands using Laravel Database Layer without the need for writing it manually.

How I can do that?

Dimitrios Desyllas · Accepted Answer · 2021-06-05 14:50:52Z

2

As seen in this medium article you can use this one single liner:

    DB::unprepared(file_get_contents('cleanup.sql'));

The only issue is that sql commands are not chunked so in large sql files it may cause a slowdown. Also file_get_contents has a read limit as well.

In case of large sql files is reccomended to manually read and chunk it into selerate sql commands.

Also if a single command fails to get executed does not proceed to the next one as you would in via mysql or psql commands in a shell environment

edited Jun 5, 2021 at 14:50

answered Nov 13, 2019 at 14:59

Dimitrios Desyllas

10.4k17 gold badges89 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Execute sql queries from an sql file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related