I'm an experienced DBA who's not very experienced with Laravel. My primary developer is moderately experienced in Laravel, however, has a tendency to gloss over Database details. The issue at hand is that we've been using "migrations" and "seeders" using artisan. This has all worked relatively well (with a few hiccups) on the development environment. Our product has now launched in its initial version and is now on production. Concerns:
The developer has created many of the migrations, and I have very little knowledge of the gotchas of these migrations on production. For example: most of the migrations he's written have an up() and no down(). Being that the system is small, the usual course is to just reset the entire DB and reload all migrations and seeders every time-- obviously we can't do this on production, so I'm concerned about just running "laravel migrate" when the system is full of live data.
Of similar concern, most of our seeders start with "delete()" basically deleting all data in the table prior to running, there's no way I'm interesting in running db:seed on production, with the files we currently have.
I see nothing in the system that he's using that differentiates between production and other environments so we don't do things like drop tables, etc.
My NORMAL way of setting up a DB is to have a "restricted" application user, I.E. the apps DB user does not have permissions to create/drop tables, and can only insert and delete, preventing the accidental dropping of tables. It appears that I have to have full DB permissions in order to run the migrations and seeders and also that the same DB connect file (and embedded username/pass) are used for both the app and the schema generation, which I'd rather have a DBA user and an APP user for security reasons.
Our schema is relatively simple, having only about 30 tables, and I'm very comfortable managing it, especially since laravel's schema builder doesn't support many postgres features (json data types, full text indexes, etc..), and we're constantly doing db::raw() commands to create our indexes, initially set our sequence values, etc.
So to boil it down to a single question: Am I missing something re:mifrations (documentation from a DBA's perspective on how to use migrations/seeders in a production environment), or should I just be managing the schema myself with .sql files? Most of the examples I see on the net gloss over production issues like this and I'm not comfortable leaving my data at risk.
...most of the migrations he's written have an up() and no down()...
Thats not the way to do migrations. He is being very lazy (or just bad). A migration should be two way. That way if someone goes wrong, you can 'rollback'. So therefore there should always be a matching down() for whatever your up() is.
Of similar concern, most of our seeders start with "delete()" basically deleting all data in the table prior to running, there's no way I'm interesting in running db:seed on production, with the files we currently have.
Once again - not the normal way to do it. You should just 'seed' what you have.
I see nothing in the system that he's using that differentiates between production and other environments so we don't do things like drop tables, etc.
Laravel has an environmental detection system - see here for more info. If you want to seed based upon your environment - you can do something like this within DatabaseSeeder.php:
if( App::environment() === 'dev' )
{
$this->call('UserTableSeeder');
}
...the apps DB user does not have permissions to create/drop tables, and can only insert and delete, preventing the accidental dropping of tables
You could do this and just 'create' tables yourself, then run migrations that only insert/remove columns in the tables.