Options for importing database

gbradley commented 5 months ago

This PR adds the ability to import a database - see https://github.com/mehrancodes/laravel-harbor/issues/98. Interested in any feedback!

There are two approaches: importing a SQL file or via a seeder.

Via SQL

To import a SQL file, set the FORGE_DB_IMPORT_SQL environment variable to the path of the SQL file on your server. Note that .gz and .zip formats are also supported.

By default your database will be imported after the site is provisioned. To import the database on each deployment, set FORGE_DB_IMPORT_ON_DEPLOYMENT to true. Note that your SQL file must begin with the DROP TABLE IF EXISTS statements necessary to reset the database.

Via seeder

To seed the database after provisioning, use the FORGE_DB_SEED variable. If true this runs the default seeder, but you may specify a custom seeder name.

If you wish to seed the database on each deployment, you can instead add Laravel's php artisan migrate:fresh --seed command to your deploy script.

Limitations / improvements:

resetting the DB when importing on deploy. This is an awkward one as it forces the user to manually prepend commands to their SQL dump. Perhaps an alternative would be to fully delete and recreate the database each time.
database must exist on the server. A more flexible approach might be for Harbor to inject an environment variable that indicates if this is the site's first deployment or not; with auto_source enabled, users would then be able to detect this value inside their deploy script and handle downloading & importing the database via their own custom script.

mehrancodes commented 5 months ago

@gbradley Thank you so much for this big enhancement! I'll review and test it out soon.

mehrancodes commented 4 months ago

Not a big issue as this is an edge case - We can warn users in the docs to ensure the file permissions are right before enabling DB file import.

It looks like the Forge SDK doesn't show errors when running commands. On my first attempt, the provision process finished without any issues, and I saw the message "Importing database from /root/database-dump/provisioner.gz." However, the database was still empty. I found out there's a file permission issue when I tried running the same import command manually on the server.

$ gunzip < /root/databases-dump/database.gz | mysql -u 325_update_welcome_page -pT7itw.....   325_update_welcome_page

bash: /root/database-dump/provisioner.gz: Permission denied
mysql: [Warning] Using a password on the command line interface can be insecure.

DB Import Command Output

Here’s the response from the executed Forge command showing a finished status. However, there are some errors in the output that might help detect and temporarily stop the command process until we find a better solution. It’s also worth reporting this to the Forge team. What do you think?

Laravel\Forge\Resources\SiteCommand {#466 ▼
  +attributes: array:11 [▶]
  #forge: Laravel\Forge\Forge {#350 ▶}
  +id: 11111
  +serverId: 1111
  +siteId: 1111
  +userId: 1111
  +eventId: 1111111
  +command: "gunzip < /root/database-dump/provisioner.gz | mysql -u 325_update_welcome_page -pT7itw....   325_update_welcome_page"
  +status: "finished"
  +createdAt: "2024-06-16 19:28:25"
  +updatedAt: "2024-06-16 19:28:26"
  +profilePhotoUrl: null
  +userName: null
  +output: """
    mysql: [Warning] Using a password on the command line interface can be insecure.\n
    /home/forge/.forge/provision-54888464.sh: line 3: /root/database-dump/provisioner.gz: Permission denied
    """
  +"duration": "1s"
}

mehrancodes commented 4 months ago

@gbradley BTW, thanks for your patience on this PR. The changes look pretty good, and I'm really loving the new test assertions! I'll be thoroughly testing it out this week.

mehrancodes commented 4 months ago

✅ Works smoothly when there is no file permission issue

mehrancodes / laravel-harbor