In this 20-minute video for Premium members, I'm comparing the speed of FIVE different approaches to UPSERT 1M records into existing "products" DB table with 1M records in it, testing MySQL / SQLite / PostgreSQL, also comparing local MacBook Pro vs cheapest Laravel Forge $6 server.
Premium Members Only
This advanced tutorial is available exclusively to Laravel Daily Premium members.
Join Premium - $29/month
Already a member? Login here
Premium membership includes:
Access to all premium tutorials
Video and Text Courses
Private Discord Channel
I really love SQLite. I use it in production on projects of all sizes with no issues. Just do not forget to set a busy_timeout (usually 3000 ~ 10000ms) to prevent db lockouts and optionally enable WAL (depending on the use case) and it flies!
How do you handle migrations for new table or table structure changes on a shared hosting if you are using SQLite?
The same way as MySQL: migrations. You just can't reorder columns, but you can add (always to the end) or remove with no issues. The order is irrelevant to Laravel anyway.
terminal access will most likely be restricted or not provided in shared hosting. then you have to download the sqlite file, manually modify the migrations and reupload. it is a nightmare. migrations run the same irrespective of back-end, but VPS is costly so mostly prefer shared hosting and no terminal access then mantaining SQLite will be a disaster waiting to happen.
If your provider doesn't provide shell access, it’s a shame (on their part, and yours for using a trash provider). As a last resort, just create a cron job:
cd app_dir && php artisan migrateto run every minute, and then remove the cron after 2 or 3 minutes. Alternatively, you can ask the provider to run the migration for you. Another option is to use Adminer. Just upload adminer.php to your server (in a password-protected directory) and you're good to go.