Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » The Architecture of a Seamless Magento 2 to Custom Laravel E-commerce Database Migration

The Architecture of a Seamless Magento 2 to Custom Laravel E-commerce Database Migration

Understanding the Magento 2 Database Schema: A Prerequisite

Migrating from Magento 2 to a custom Laravel application necessitates a deep understanding of Magento’s complex, entity-attribute-value (EAV) driven database schema. This is not a simple one-to-one mapping. Magento’s flexibility comes at the cost of a highly normalized, yet often performance-challenging, database structure. Key tables to scrutinize include:

  • catalog_product_entity: The core product table.
  • catalog_product_entity_varchar, catalog_product_entity_int, catalog_product_entity_decimal, catalog_product_entity_text, catalog_product_entity_datetime, catalog_product_entity_boolean: These are the EAV tables storing product attributes. The attribute_id links to eav_attribute, and entity_id links to catalog_product_entity.
  • catalog_category_entity: Stores category information.
  • catalog_category_product: The many-to-many relationship between categories and products.
  • sales_order: Core order data.
  • sales_order_item: Line items for each order.
  • customer_entity: Customer data.
  • quote and quote_item: Represent shopping carts, which often need to be migrated or archived.
  • inventory_stock_item: Stock levels.
  • eav_attribute: Metadata about EAV attributes.
  • eav_entity_type: Defines entity types (e.g., ‘catalog_product’).

A critical first step is to map these Magento tables and their attributes to your Laravel Eloquent models and their corresponding database tables. This often involves creating intermediate tables in your Laravel application to denormalize and simplify access to product data, especially for attributes that are frequently queried.

Strategy for Data Extraction and Transformation

Directly querying Magento’s EAV structure for migration is inefficient and error-prone. A robust strategy involves extracting data into a more manageable, denormalized format before importing into Laravel. We’ll use PHP for this extraction process, leveraging Magento’s APIs or direct database access.

Extracting Product Data

We’ll write a PHP script that iterates through products, fetching their core data and then their EAV attributes. For simplicity, we’ll assume direct database access. In a production scenario, consider using Magento’s Service Contracts for a more decoupled approach.

<?php

require 'vendor/autoload.php'; // Assuming you have Magento's autoloader or a custom one

use Magento\Framework\App\Bootstrap;
use Magento\Framework\App\State;
use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Catalog\Api\ProductAttributeRepositoryInterface;
use Magento\Eav\Api\AttributeSetRepositoryInterface;
use Magento\Eav\Api\AttributeRepositoryInterface;
use Magento\Framework\Api\SearchCriteriaBuilder;

// --- Database Connection (Example using PDO) ---
$dbHost = 'localhost';
$dbName = 'magento_db';
$dbUser = 'magento_user';
$dbPass = 'magento_password';
$pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// --- Magento Bootstrap (if running outside Magento CLI) ---
// This part is complex and depends on your Magento setup.
// For simplicity, we'll use direct DB queries for EAV data.
// In a real scenario, you'd bootstrap Magento and use its repositories.

// --- Fetching Core Product Data ---
$products = [];
$stmt = $pdo->query("SELECT entity_id, sku, created_at, updated_at FROM catalog_product_entity");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[$row['entity_id']] = [
        'entity_id' => $row['entity_id'],
        'sku' => $row['sku'],
        'created_at' => $row['created_at'],
        'updated_at' => $row['updated_at'],
        'attributes' => []
    ];
}

// --- Fetching EAV Attributes ---
// This is a simplified approach. A more robust solution would involve
// joining with eav_attribute and eav_entity_type to get attribute codes.
$attributeStmt = $pdo->query("
    SELECT
        cpe.entity_id,
        cpev.attribute_id,
        ea.attribute_code,
        cpev.value
    FROM catalog_product_entity cpe
    JOIN eav_entity_attribute eea ON cpe.entity_type_id = eea.entity_type_id
    JOIN eav_attribute ea ON eea.attribute_id = ea.attribute_id
    LEFT JOIN catalog_product_entity_varchar cpev ON cpe.entity_id = cpev.entity_id AND ea.attribute_id = cpev.attribute_id
    WHERE ea.entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
    AND ea.backend_type = 'varchar' -- Extend for int, decimal, text, etc.

    UNION ALL

    SELECT
        cpe.entity_id,
        cpei.attribute_id,
        ea.attribute_code,
        cpei.value
    FROM catalog_product_entity cpe
    JOIN eav_entity_attribute eea ON cpe.entity_type_id = eea.entity_type_id
    JOIN eav_attribute ea ON eea.attribute_id = ea.attribute_id
    LEFT JOIN catalog_product_entity_int cpei ON cpe.entity_id = cpei.entity_id AND ea.attribute_id = cpei.attribute_id
    WHERE ea.entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
    AND ea.backend_type = 'int'

    -- Add UNION ALL for other backend types (decimal, text, datetime, boolean)
");

while ($attrRow = $attributeStmt->fetch(PDO::FETCH_ASSOC)) {
    if (isset($products[$attrRow['entity_id']])) {
        $products[$attrRow['entity_id']]['attributes'][$attrRow['attribute_code']] = $attrRow['value'];
    }
}

// --- Outputting to JSON for Laravel Import ---
$jsonData = json_encode(array_values($products), JSON_PRETTY_PRINT);
file_put_contents('magento_products_export.json', $jsonData);

echo "Product data exported to magento_products_export.json\n";
?>

Transforming Categories

Category migration is typically more straightforward. We need to map Magento’s hierarchical category structure to your Laravel application’s category model. This involves extracting category IDs, parent IDs, names, and URLs.

<?php
// ... (PDO connection from previous example) ...

$categories = [];
$stmt = $pdo->query("
    SELECT
        cce.entity_id,
        cce.parent_id,
        cct.value AS name,
        cce.path
    FROM catalog_category_entity cce
    LEFT JOIN catalog_category_entity_varchar cct ON cce.entity_id = cct.entity_id AND cct.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
    WHERE cce.level > 0 -- Exclude root category
");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $categories[] = [
        'magento_id' => $row['entity_id'],
        'parent_magento_id' => $row['parent_id'],
        'name' => $row['name'],
        'path' => $row['path']
    ];
}

$jsonData = json_encode($categories, JSON_PRETTY_PRINT);
file_put_contents('magento_categories_export.json', $jsonData);

echo "Category data exported to magento_categories_export.json\n";
?>

Migrating Customers

Customer data migration requires careful handling of sensitive information and mapping of Magento’s customer attributes (often EAV-based) to your Laravel user model. Password hashes are particularly important; you’ll need to determine Magento’s hashing algorithm and implement a compatible decryption or re-hashing strategy in Laravel.

<?php
// ... (PDO connection from previous example) ...

$customers = [];
$stmt = $pdo->query("
    SELECT
        ce.entity_id,
        ce.email,
        ce.created_at,
        ce.updated_at,
        cpev.value AS firstname,
        cpev2.value AS lastname,
        cpei.value AS store_id -- Example: mapping to a specific store view
    FROM customer_entity ce
    LEFT JOIN customer_entity_varchar cpev ON ce.entity_id = cpev.entity_id AND cpev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'customer'))
    LEFT JOIN customer_entity_varchar cpev2 ON ce.entity_id = cpev2.entity_id AND cpev2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'lastname' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'customer'))
    LEFT JOIN customer_entity_int cpei ON ce.entity_id = cpei.entity_id AND cpei.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'store_id' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'customer'))
    -- Add more joins for other customer attributes as needed
");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $customers[] = [
        'magento_id' => $row['entity_id'],
        'email' => $row['email'],
        'firstname' => $row['firstname'],
        'lastname' => $row['lastname'],
        'created_at' => $row['created_at'],
        'updated_at' => $row['updated_at'],
        'store_id' => $row['store_id'],
        // 'password_hash' => $row['password_hash'] // Magento stores this, handle with care
    ];
}

$jsonData = json_encode($customers, JSON_PRETTY_PRINT);
file_put_contents('magento_customers_export.json', $jsonData);

echo "Customer data exported to magento_customers_export.json\n";
?>

Laravel Data Import and Model Design

Once data is extracted into a clean JSON format, we can develop import scripts in Laravel. This phase is also about designing your Laravel models to accommodate the migrated data efficiently. Denormalization is key here.

Laravel Eloquent Models for Denormalized Data

Instead of replicating Magento’s EAV complexity, create Laravel models that directly map to your desired data structure. For products, this might mean a `Product` model with columns for common attributes like `sku`, `name`, `price`, `description`, and then potentially JSON columns or separate related tables for less common or dynamic attributes.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;

    protected $fillable = [
        'magento_id', // Store the original Magento ID for reference
        'sku',
        'name',
        'description',
        'price',
        'weight',
        'status', // e.g., 1 for enabled, 0 for disabled
        'created_at',
        'updated_at',
        'custom_attributes' // JSON column for any remaining EAV data
    ];

    protected $casts = [
        'custom_attributes' => 'array',
        'price' => 'decimal:2',
    ];

    // Relationship to categories (many-to-many)
    public function categories()
    {
        return $this->belongsToMany(Category::class, 'product_categories', 'product_id', 'category_id');
    }

    // Relationship to stock
    public function stock()
    {
        return $this->hasOne(Stock::class);
    }

    // Add other relationships as needed (e.g., images, reviews)
}
?>

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

    protected $fillable = [
        'magento_id',
        'parent_magento_id', // Store for reference, build tree in application logic
        'name',
        'slug', // Generated from name
        'path', // Store Magento's path for reference
        'level'
    ];

    // Relationship to products (many-to-many)
    public function products()
    {
        return $this->belongsToMany(Product::class, 'product_categories', 'category_id', 'product_id');
    }

    // Method to build category tree (example)
    public static function getCategoryTree()
    {
        $categories = self::orderBy('path')->get();
        $tree = [];
        $levelCache = [];

        foreach ($categories as $category) {
            if ($category->level == 1) {
                $tree[$category->magento_id] = $category;
                $levelCache[$category->level] = &$tree[$category->magento_id];
            } else {
                $parent = $levelCache[$category->level - 1];
                $parent->children[$category->magento_id] = $category;
                $levelCache[$category->level] = &$parent->children[$category->magento_id];
            }
        }
        return $tree;
    }
}
?>

Importing Data with Laravel Artisan Commands

Leverage Laravel’s Artisan command-line interface for robust data import. This allows for progress tracking, error handling, and batch processing.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Product;
use App\Models\Category;
use App\Models\User; // Assuming User model for customers
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;

class ImportMagentoData extends Command
{
    protected $signature = 'magento:import {--type=}'; // e.g., --type=products, --type=categories, --type=customers
    protected $description = 'Imports data from Magento export files.';

    public function handle()
    {
        $type = $this->option('type');

        if (!$type) {
            $this->error('Please specify the type of data to import: --type=products, --type=categories, or --type=customers.');
            return 1;
        }

        switch ($type) {
            case 'products':
                $this->importProducts();
                break;
            case 'categories':
                $this->importCategories();
                break;
            case 'customers':
                $this->importCustomers();
                break;
            default:
                $this->error("Unknown type: {$type}");
                return 1;
        }

        $this->info("Magento data import for type '{$type}' completed.");
        return 0;
    }

    protected function importProducts()
    {
        $filePath = storage_path('app/exports/magento_products_export.json');
        if (!file_exists($filePath)) {
            $this->error("Product export file not found at: {$filePath}");
            return;
        }

        $jsonData = file_get_contents($filePath);
        $productsData = json_decode($jsonData, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            $this->error("Error decoding JSON file: " . json_last_error_msg());
            return;
        }

        $this->info("Importing " . count($productsData) . " products...");
        $bar = $this->output->createProgressBar(count($productsData));
        $bar->start();

        foreach ($productsData as $productData) {
            // Map Magento attributes to your Product model fields
            $mappedData = [
                'magento_id' => $productData['entity_id'],
                'sku' => $productData['sku'],
                'name' => $productData['attributes']['name'] ?? 'N/A', // Handle missing attributes
                'description' => $productData['attributes']['description'] ?? null,
                'price' => $productData['attributes']['price'] ?? 0.00,
                'status' => $productData['attributes']['status'] ?? 1, // Default to enabled
                'created_at' => $productData['created_at'],
                'updated_at' => $productData['updated_at'],
                'custom_attributes' => collect($productData['attributes'])->except(['name', 'description', 'price', 'status'])->toArray() // Store remaining as JSON
            ];

            Product::updateOrCreate(['magento_id' => $productData['entity_id']], $mappedData);
            $bar->advance();
        }

        $bar->finish();
    }

    protected function importCategories()
    {
        $filePath = storage_path('app/exports/magento_categories_export.json');
        if (!file_exists($filePath)) {
            $this->error("Category export file not found at: {$filePath}");
            return;
        }

        $jsonData = file_get_contents($filePath);
        $categoriesData = json_decode($jsonData, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            $this->error("Error decoding JSON file: " . json_last_error_msg());
            return;
        }

        $this->info("Importing " . count($categoriesData) . " categories...");
        $bar = $this->output->createProgressBar(count($categoriesData));
        $bar->start();

        // First pass: Create all categories without parent relationships
        $categoryMap = []; // To store magento_id => laravel_id mapping
        foreach ($categoriesData as $categoryData) {
            $category = Category::updateOrCreate(
                ['magento_id' => $categoryData['magento_id']],
                [
                    'name' => $categoryData['name'],
                    'parent_magento_id' => $categoryData['parent_magento_id'],
                    'path' => $categoryData['path'],
                    'level' => count(explode('/', $categoryData['path'])) - 1 // Calculate level from path
                ]
            );
            $categoryMap[$categoryData['magento_id']] = $category->id;
            $bar->advance();
        }

        // Second pass: Update parent relationships
        $bar->setMessage("Updating parent relationships...");
        $bar->setProgress(0);
        $bar->setMaxSteps(count($categoriesData));

        foreach ($categoriesData as $categoryData) {
            if ($categoryData['parent_magento_id'] != 0 && isset($categoryMap[$categoryData['parent_magento_id']])) {
                $category = Category::find($categoryMap[$categoryData['magento_id']]);
                $category->update(['parent_id' => $categoryMap[$categoryData['parent_magento_id']]]);
            }
            $bar->advance();
        }

        $bar->finish();
    }

    protected function importCustomers()
    {
        $filePath = storage_path('app/exports/magento_customers_export.json');
        if (!file_exists($filePath)) {
            $this->error("Customer export file not found at: {$filePath}");
            return;
        }

        $jsonData = file_get_contents($filePath);
        $customersData = json_decode($jsonData, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            $this->error("Error decoding JSON file: " . json_last_error_msg());
            return;
        }

        $this->info("Importing " . count($customersData) . " customers...");
        $bar = $this->output->createProgressBar(count($customersData));
        $bar->start();

        foreach ($customersData as $customerData) {
            // IMPORTANT: Magento password hashing needs to be understood.
            // This example assumes a simple scenario or that you'll re-hash.
            // Magento uses various hashing methods (e.g., BCRYPT, SHA256 with salt).
            // You might need a dedicated service to handle password migration.
            $password = Str::random(12); // Generate a random password and force reset

            User::updateOrCreate(
                ['email' => $customerData['email']],
                [
                    'magento_id' => $customerData['magento_id'],
                    'first_name' => $customerData['firstname'],
                    'last_name' => $customerData['lastname'],
                    'password' => Hash::make($password), // Force password reset
                    'created_at' => $customerData['created_at'],
                    'updated_at' => $customerData['updated_at'],
                ]
            );
            // TODO: Implement email notification for password reset
            $bar->advance();
        }

        $bar->finish();
    }
}
?>

Handling Relationships and Foreign Keys

Migrating relationships, such as product-category associations and order items, requires careful sequencing. You must import parent entities before their children or associated entities.

Product-Category Mapping

After importing products and categories, you’ll need to populate the `product_categories` pivot table. This requires joining `catalog_category_product` with your newly created Laravel `Product` and `Category` models.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Product;
use App\Models\Category;
use Illuminate\Support\Facades\DB;

class ImportMagentoRelationships extends Command
{
    protected $signature = 'magento:import-relationships';
    protected $description = 'Imports relationships like product-category associations.';

    public function handle()
    {
        $this->importProductCategories();
        // Add other relationship imports here (e.g., orders, reviews)
        $this->info("Relationship import completed.");
        return 0;
    }

    protected function importProductCategories()
    {
        $this->info("Importing product-category associations...");

        // Fetch mappings from Magento's DB
        $associations = DB::connection('mysql_magento')->table('catalog_category_product')->select('category_id', 'product_id')->get();

        $bar = $this->output->createProgressBar($associations->count());
        $bar->start();

        // Fetch Laravel IDs for quick lookup
        $productLaravelIds = Product::pluck('id', 'magento_id');
        $categoryLaravelIds = Category::pluck('id', 'magento_id');

        $pivotData = [];
        foreach ($associations as $association) {
            if (isset($productLaravelIds[$association->product_id]) && isset($categoryLaravelIds[$association->category_id])) {
                $pivotData[] = [
                    'product_id' => $productLaravelIds[$association->product_id],
                    'category_id' => $categoryLaravelIds[$association->category_id],
                    'created_at' => now(),
                    'updated_at' => now(),
                ];
            } else {
                $this->warn("Skipping association: Magento Product ID {$association->product_id} or Category ID {$association->category_id} not found in Laravel.");
            }
        }

        // Insert in batches for performance
        $chunks = array_chunk($pivotData, 1000);
        foreach ($chunks as $chunk) {
            DB::table('product_categories')->insert($chunk);
        }

        $bar->finish();
        $this->info("Imported " . count($pivotData) . " product-category associations.");
    }
}
?>

Order and Customer Data Migration Considerations

Migrating orders is often the most complex part due to the sheer volume of data and intricate relationships (order items, addresses, payments, shipping). A phased approach is recommended:

  • Phase 1: Historical Orders (Read-Only): Migrate historical orders into a separate, potentially archived, table in Laravel. This table might mirror Magento’s structure closely or be simplified. Focus on data integrity and searchability.
  • Phase 2: Active Orders/New Orders: For ongoing operations, ensure your Laravel application can process new orders seamlessly. This might involve a cutover strategy where Magento stops taking orders, and Laravel takes over.

Customer addresses need to be mapped to your `User` model’s address relationships. Payment and shipping method data might require custom mapping or abstraction, as these are often highly specific to the platform.

Testing and Validation

Thorough testing is paramount. Implement automated tests to validate data integrity:

  • Record Counts: Verify that the number of imported records in Laravel matches the source in Magento for each entity type.
  • Data Spot Checks: Manually compare a sample of records (products, customers, orders) between Magento and Laravel.
  • Relationship Integrity: Ensure that product-category links, order items, and customer addresses are correctly established in Laravel.
  • Functional Testing: Test core e-commerce functionalities in the Laravel application (e.g., product browsing, checkout, order history) using migrated data.
  • Performance Testing: Monitor query performance in Laravel, especially for complex data retrieval, and optimize as needed.

Rollback Strategy

Always have a rollback plan. This typically involves:

  • Full database backups of both Magento and the target Laravel database before migration.
  • A script to truncate or drop tables in the Laravel database if the migration fails.
  • A clear communication plan for stakeholders in case of rollback.

This comprehensive approach, combining deep schema understanding, strategic data extraction and transformation, robust Laravel implementation, and rigorous testing, is essential for a successful Magento 2 to custom Laravel e-commerce database migration.