Laravel Sqlite Migrations Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require jjclane/laravel-sqlite-migrations --dev
   

   Add the trait to your migration class:

   use JJCLane\SQLiteMigration\TransformMigration;
   

2. First Use Case Apply the trait to any migration where SQLite compatibility is needed:

   class CreateUsersTable extends Migration
   {
       use TransformMigration;
   
       public function up()
       {
           $this->table('users', function (Blueprint $table) {
               $table->id();
               $table->string('name');
               $table->timestamps();
           });
       }
   }
   

   Run migrations as usual:

   php artisan migrate
   

3. Where to Look First

   • Review the README for SQLite-specific quirks (e.g., NOT NULL constraints, unsupported datatypes).
   • Check the source code for customization points (e.g., getSQLiteColumnType()).

Implementation Patterns

Core Workflows

1. Automatic Transformation Use the TransformMigration trait to auto-convert Laravel Blueprint methods to SQLite-compatible syntax:

   $this->table('posts', function (Blueprint $table) {
       $table->bigIncrements('id'); // Auto-converted to `INTEGER PRIMARY KEY AUTOINCREMENT`
       $table->string('title', 255);
       $table->unsignedDecimal('price', 8, 2); // SQLite lacks `UNSIGNED`; trait handles this
   });
   

2. Explicit Transformation For granular control, use transformMigration():

   $this->transformMigration('products', function (Blueprint $table) {
       $table->json('metadata'); // SQLite supports JSON natively; trait ensures correct syntax
   });
   

3. Schema-Level Adjustments Leverage the trait’s built-in mappings for unsupported datatypes:

   • unsignedInteger() → INTEGER (SQLite lacks UNSIGNED).
   • decimal() → REAL (SQLite’s closest equivalent).
   • timestamp() → INTEGER (stored as Unix timestamp).

4. NOT NULL Workarounds Avoid SQLite’s NOT NULL + DEFAULT NULL conflict by:

   $this->table('orders', function (Blueprint $table) {
       $table->string('status')->nullable(); // Step 1: Add nullable
       // Later migration:
       $table->string('status')->default('pending')->nullable(false); // Step 2: Update
   });
   

5. Foreign Key Handling SQLite requires FOREIGN KEY constraints to be defined in a separate PRAGMA statement. The trait handles this implicitly:

   $this->table('order_items', function (Blueprint $table) {
       $table->foreignId('order_id')->constrained(); // Auto-converted to SQLite-compatible FK
   });
   

Integration Tips

• Testing: Use the package in phpunit.xml for SQLite-specific tests:

  <env key="DB_CONNECTION" value="sqlite_testing"/>
  

• CI/CD: Ensure SQLite is available in your pipeline (e.g., Docker with sqlite3 installed).
• Seeding: SQLite may need adjustments for faker data (e.g., unsignedInteger() → integer()).

Gotchas and Tips

Pitfalls

1. Unsupported Datatypes

   • Issue: unsignedBigInteger() or unsignedMediumInteger() will fail silently (SQLite lacks UNSIGNED).
   • Fix: Use bigInteger() and handle unsigned logic in application code.
   • Workaround: Extend the trait to override getSQLiteColumnType():

     protected function getSQLiteColumnType($type)
     {
         return str_replace('unsigned', '', parent::getSQLiteColumnType($type));
     }
     

2. Foreign Key Constraints

   • Issue: SQLite requires PRAGMA foreign_keys = ON; to enable FKs. The trait doesn’t auto-enable this.
   • Fix: Add to your DatabaseServiceProvider:

     DB::statement('PRAGMA foreign_keys = ON;');
     

3. Case Sensitivity

   • Issue: SQLite table/column names are case-sensitive on Linux/macOS but not on Windows.
   • Fix: Standardize naming (e.g., snake_case) and test across environments.

4. Migration Order Dependencies

   • Issue: SQLite may fail if a referenced table doesn’t exist (e.g., foreignId('user_id')->constrained() before users table).
   • Fix: Use ifNotExists() or manually order migrations.

5. JSON/ENUM Data

   • Issue: SQLite supports JSON but not Laravel’s ENUM or JSON methods directly.
   • Fix: Use raw SQL or extend the trait:

     $table->text('tags')->comment('JSON'); // Workaround for ENUM-like storage
     

Debugging

• Enable Query Logging Add to config/database.php:

  'log_queries' => true,
  'log' => storage_path('logs/queries.log'),
  

  Check for malformed SQLite queries (e.g., unsupported syntax).

• Check Raw SQL Override getSQLite() to log transformations:

  protected function getSQLite($query)
  {
      \Log::debug('SQLite Query:', [$query]);
      return parent::getSQLite($query);
  }
  

Extension Points

1. Custom Column Mappings Override getSQLiteColumnType() to handle niche cases:

   protected function getSQLiteColumnType($type)
   {
       if (str_contains($type, 'uuid')) {
           return 'TEXT';
       }
       return parent::getSQLiteColumnType($type);
   }
   

2. Add SQLite-Specific Methods Extend the trait to support SQLite-only features:

   public function sqliteCheck($column)
   {
       $this->schema->table($this->table, function (Blueprint $table) use ($column) {
           $table->raw("ADD CONSTRAINT CHECK ($column IS NOT NULL)");
       });
   }
   

3. Conditional Transformation Skip transformation for specific tables:

   public function up()
   {
       if ($this->table !== 'excluded_table') {
           $this->transformMigration('table', function (Blueprint $table) { ... });
       }
   }
   

Configuration Quirks

• Default Database Connection The trait assumes SQLite is your default connection. Specify explicitly if needed:

  $this->transformMigration('table', function (Blueprint $table) {
      // ...
  }, 'sqlite_testing'); // Custom connection name
  

• Schema Builder Overrides If using custom schema builders, ensure they’re compatible with the trait’s getSQLite() method.

Performance Tips

• Batch Migrations: Combine related migrations to reduce SQLite’s BEGIN IMMEDIATE overhead.
• Avoid Raw SQL: Use Blueprint methods where possible—the trait optimizes SQLite syntax.