Laravel Loadfile Laravel Package

Getting Started

ellgreen/laravel-loadfile wraps the native bulk-load statements of MySQL (LOAD DATA [LOCAL] INFILE) and PostgreSQL (COPY ... FROM STDIN) behind a fluent Laravel facade. It exists for the case where Model::insert() / upsert() in chunks is too slow: hundreds of thousands to millions of rows landing in a table in one statement instead of thousands of round trips.

Install and first run

composer require ellgreen/laravel-loadfile

The service provider is auto-discovered. Publishing config is optional — do it the first time you need to change a driver default:

php artisan vendor:publish --tag=loadfile-config

Minimal job — a CSV into an existing table:

use Ellgreen\LaravelLoadFile\Facades\LoadFile;

LoadFile::load(storage_path('imports/users.csv'))
    ->into('users')
    ->withHeaders()
    ->fieldsTerminatedBy(',')
    ->run();

Where to look first, in order

1. README.md — the fluent API is the whole package; the option list (fieldsTerminatedBy, fieldsEnclosedBy, ignoreLines, replace, ignore, local, columns, charset) maps 1:1 to the underlying SQL, and the method names track that closely.
2. config/loadfile.php — driver selection and defaults. This is the only file most teams ever edit.
3. src/Drivers/ (MySQL / PostgreSQL implementations) — read these when a fluent call appears to do nothing; the abstraction is thin and each driver supports a slightly different subset.
4. src/Concerns/ or the builder class — this is where into() and intoModel() live, and it is the class you extend if you need a custom option.

Best first use case: a one-off backfill or a seeder you currently run with ->chunk(500) inserts. Swap it for a single load, measure, and you will immediately see whether the package is worth the operational caveats in the last section. As a rule of thumb, if your import is under ~10k rows, chunked inserts are fast enough and much easier to validate — reach for this package when it hurts.

Implementation Patterns

1. Controller/upload → staging table → transform

Do not load user-uploaded files straight into a live table. Load into a thin staging table with all-text columns, then move data with SQL. This gives you validation and a rollback story, and it sidesteps the fact that the load bypasses every Eloquent cast and validator.

LoadFile::load($request->file('csv')->getRealPath())
    ->into('user_imports_staging')
    ->withHeaders()
    ->fieldsTerminatedBy(',')
    ->fieldsEnclosedBy('"')
    ->run();

DB::transaction(function () {
    DB::statement('INSERT INTO users (email, name) SELECT email, name FROM user_imports_staging WHERE email IS NOT NULL');
    DB::statement('TRUNCATE user_imports_staging');
});

2. Model-targeted loads

->intoModel(User::class) resolves table name and, in the MySQL/Postgres drivers, maps columns from the model's getAttributes()/schema so you can pass a subset of columns without spelling out the table name. Use it when the file already matches your schema; use ->into('table') when you want the staging pattern above. Verify the exact column-mapping behaviour against the driver source in your version before relying on it for generated columns or JSON casts — those are not Eloquent-aware.

3. Queue it, don't inline it

A 2M-row load is a multi-second to multi-minute blocking call holding a DB connection. Put it behind a queued job with $timeout raised, a WithoutOverlapping middleware keyed on the target table, and failed() cleanup that drops/truncates the staging table. Notify via a broadcast event or a nullable imports.completed_at column rather than keeping the HTTP request open.

4. Idempotency and re-runs

• ->replace() (MySQL REPLACE) deletes then inserts on unique-key conflicts — it fires delete triggers and changes surrogate IDs. Prefer loading to staging and doing INSERT ... ON CONFLICT DO UPDATE yourself.
• ->ignore() (IGNORE) silently drops offending rows, including truncation errors. Only use it when you genuinely want "best effort", and log the row-count delta (SHOW WARNINGS / SELECT count(*)) so you know what was lost.
• For cron-driven syncs, load into a per-run staging table (staging_{uuid}), then swap or merge. Truncate-and-reload on a live table will produce a visible empty window.

5. Delimiters, headers, and line endings

Always set these explicitly; the defaults differ between MySQL and Postgres and between package versions.

LoadFile::load($path)
    ->into('events')
    ->withHeaders()
    ->fieldsTerminatedBy("\t")
    ->fieldsEnclosedBy('"')
    ->fieldsEscapedBy('\\')
    ->linesTerminatedBy("\n")
    ->charset('utf8mb4')
    ->ignoreLines(0)
    ->run();

If you support both databases, put these in a config array keyed by DB::getDriverName() rather than scattering literals across call sites.

6. Integration with existing pipelines

• Artisan command (php artisan imports:run {file}) for ops-triggered backfills — keep it out of the HTTP layer.
• Scheduled task for nightly vendor-file ingestion.
• Wrap the whole thing in DB::transaction() only if the underlying statement participates in one (Postgres COPY FROM STDIN does; MySQL LOAD DATA also does under InnoDB, but LOCAL file reads plus implicit commits around some DDL-adjacent options can surprise you — test it).
• Feature-test at the command level against a real MySQL/Postgres connection in CI; there is no meaningful SQLite emulation.

Gotchas and Tips

Driver support is narrow. MySQL and PostgreSQL only. There is no SQLite driver, so a package that works in prod will fail in your :memory: test suite. Keep import tests in a separate suite/pipeline stage with a real database service container.

MySQL LOCAL vs server-side. ->local() (client-side read) requires local_infile=1 on the server and PDO::MYSQL_ATTR_LOCAL_INFILE => true on the connection. Without LOCAL, the database server reads the path, so storage_path() is meaningless on a remote DB host and you need FILE privilege. This is the single most common "it works on my machine" failure. Decide which mode you support and assert it in config/database.php.

The load bypasses Eloquent entirely. No model events, no created_at/updated_at, no casts, no mutators, no validation, no observers — you are writing rows. Either your file supplies timestamps and you define them with defaults, or you UPDATE them afterwards. Anything relying on booted()/creating hooks will not fire, which is occasionally a feature and frequently a bug.

NULL vs empty string. MySQL treats \N as NULL; Postgres distinguishes '' from NULL via NULL '' / FORCE_NULL. Files exported from spreadsheets give you '' where you wanted NULL, and a NOT NULL column will reject it. Normalise in the staging table, not in the file.

Encoding and BOM. Excel-prepended UTF-8 BOMs corrupt the first column name when using withHeaders(). Strip it before loading, and set charset explicitly — don't inherit a latin1 server default.

Error visibility is poor by design. A delimiter mismatch typically yields one giant row or zero rows, not an exception. Instrument every load with before/after count(*) and log both; a mismatch caught in the first staging run is cheap, caught in production it is a data incident. Enable ->ignoreErrors()-style options only with that instrumentation in place.

Limits and timeouts. Check max_allowed_packet (MySQL), postgresql.conf limits, PHP memory_limit (the file is streamed, but surrounding code may not be), queue worker timeouts, and any connection-level statement_timeout. Files over a few hundred MB are better split by ignoreLines()/head/split than retried as one statement.

Upgrade notes for 4.1.0. The release is Laravel 13 compatibility plus a dev-tooling cleanup (laravel-shift PR) — no runtime API changes and nothing deprecated. It is a safe minor bump. Concretely: widen your laravel/framework and orchestra/testbench constraints, bump the package in CI, and re-run the import test suite against both MySQL and Postgres. If you pin static-analysis or Pest config from the package's own tooling, the "cleaning up tools" commit may churn dev dependencies — it does not affect published classes.

Extension points. The driver is selected via config/loadfile.php; if you need a bulk-load path the package does not ship (e.g. ClickHouse, or MySQL with custom SET clauses), bind your own driver implementation rather than forking — the fluent builder delegates the actual statement construction to the driver, which keeps the surface you must implement small. For anything more exotic, a raw DB::statement is honestly the lower-friction answer.

When not to use it. Small imports, imports needing per-row validation and rich error reporting, imports into tables with heavy trigger/observer logic, and anything that must be portable across all Laravel-supported databases. The package is a scalpel: excellent at raw throughput, indifferent to correctness guarantees.