Seeds From Sql Laravel Package

Getting Started

To leverage this package for SQL imports, start by installing it via Composer:

composer require vendor/package-name

The core feature introduced in v1.0.3 is the ability to import SQL directly using Laravel's DB::unprepared() method. This is useful for:

• One-off data migrations (e.g., seeding production with a dump).
• Bulk inserts where raw SQL is more efficient than Eloquent.
• Legacy schema/data imports without rewriting queries.

First use case: Import a .sql file into your database:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

// Read SQL file
$sql = File::get(database_path('imports/sample.sql'));

// Execute raw SQL
DB::unprepared($sql);

For Laravel Artisan integration, create a custom command:

php artisan make:command ImportSqlCommand

Implementation Patterns

Workflow for SQL Imports

1. Preprocess SQL (if needed):
   • Use str_replace() to modify placeholders (e.g., replace :table with actual table names).
   • Example:

     $sql = str_replace(':prefix', config('database.connections.mysql.prefix'), $sql);
     

2. Transaction Wrapping (for safety):

   DB::transaction(function () use ($sql) {
       DB::unprepared($sql);
   });
   

3. Chunking Large Imports: Split SQL by semicolons and process in batches to avoid memory issues:

   $queries = explode(';', $sql);
   foreach ($queries as $query) {
       if (trim($query)) {
           DB::unprepared($query);
       }
   }
   

4. Logging/Validation:
   • Log import start/end times for debugging.
   • Validate SQL syntax before execution (e.g., using DB::connection()->getPdo()->prepare()).

Integration Tips

• Service Provider Binding: Bind a helper class to manage imports:

  $this->app->bind('sqlImporter', function () {
      return new SqlImporter(DB::connection());
  });
  

• Event Dispatching: Trigger events before/after imports for notifications or side effects:

  event(new SqlImportStarted('users.sql'));
  DB::unprepared($sql);
  event(new SqlImportCompleted());
  

• Environment Awareness: Restrict imports to non-production environments in config:

  if (app()->environment('local')) {
      DB::unprepared($sql);
  }
  

Gotchas and Tips

Pitfalls

1. Transaction Limits:

   • DB::unprepared() bypasses Laravel’s query builder, so transactions may not roll back if an error occurs mid-import. Use explicit transactions (as shown above).
   • Some databases (e.g., MySQL) have limits on transaction size. For large imports, commit in chunks.

2. Schema Conflicts:

   • Raw SQL ignores Laravel’s schema bindings. If importing tables, ensure:
     • The table exists (or use DB::statement() with CREATE TABLE).
     • Foreign keys are disabled temporarily (if needed):

       SET FOREIGN_KEY_CHECKS = 0;
       -- Your SQL here
       SET FOREIGN_KEY_CHECKS = 1;
       

3. Character Encoding:

   • SQL files may use UTF-8 BOM or other encodings. Strip BOMs or convert encoding:

     $sql = mb_convert_encoding($sql, 'UTF-8', 'UTF-8');
     

4. Performance:

   • Disable indexes temporarily for bulk inserts:

     ALTER TABLE table_name DISABLE KEYS;
     -- Insert data
     ALTER TABLE table_name ENABLE KEYS;
     

Debugging Tips

• Log Raw SQL: Use Laravel’s query logging to inspect executed SQL:

  DB::enableQueryLog();
  DB::unprepared($sql);
  dd(DB::getQueryLog());
  

• Test with Small Files First: Validate the import workflow with a tiny SQL file before running production dumps.
• Database-Specific Quirks:
  • PostgreSQL: Use COPY for faster bulk inserts instead of INSERT statements.
  • SQLite: Disable foreign key checks (PRAGMA foreign_keys=OFF).

Extension Points

1. Custom SQL Parsers: Extend the package to parse SQL files into structured arrays for validation:

   $parsed = parseSqlFile('dump.sql');
   // Validate $parsed['tables'], $parsed['queries'], etc.
   

2. Progress Tracking: Add a listener to track row counts for large imports:

   DB::listen(function ($query) {
       if (str_contains($query->sql, 'INSERT')) {
           // Log affected rows
       }
   });
   

3. Backup Integration: Automate pre-import backups using Laravel’s DB::connection()->getDoctrineSchemaManager():

   $schema = DB::connection()->getDoctrineSchemaManager();
   $backup = $schema->createSchemaSQL($schema->listTableDetails());
   File::put(storage_path('backups/schema.sql'), $backup);
   

Note: This assessment assumes the package is lightweight and focuses on raw SQL imports. For feature-rich alternatives (e.g., Laravel’s built-in Schema::create() or packages like spatie/laravel-import), evaluate trade-offs based on use case. Always test in a staging environment first.