Skip to content
Search

Last updated

Import a CSV file into MySQL or PostgreSQL in PHP

Let's say a CSV file with orders lands on our server every night, and we need to put it into the orders table. Some of the orders are new, and some of them changed since yesterday.

The scripts that talk to a database don't run in the playground. Run them on your machine.

The file

First we need a file to import, so let's generate one. Set $rows to 100_000 to get the version we measure below.

<?php

declare(strict_types=1);

$rows = 1_000;
$file = fopen(__DIR__ . '/orders.csv', 'w');

fputcsv($file, ['id', 'email', 'country', 'amount', 'created_at'], escape: '');

for ($id = 1; $id <= $rows; $id++) {
    fputcsv($file, [
        $id,
        "customer{$id}@example.com",
        ['PL', 'US', 'DE', 'FR', 'GB'][$id % 5],
        number_format(($id % 1000) / 10, 2, '.', ''),
        gmdate('Y-m-d H:i:s', 1_700_000_000 + $id * 60),
    ], escape: '');
}

fclose($file);

echo "orders.csv: {$rows} rows\n";
orders.csv: 1000 rows

The table

MySQL

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    country VARCHAR(2) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    created_at DATETIME NOT NULL
);

PostgreSQL

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL,
    country TEXT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Flow PostgreSQL

<?php

declare(strict_types=1);

use function Flow\PostgreSql\DSL\{
    column,
    column_type_bigint,
    column_type_numeric,
    column_type_text,
    column_type_timestamp,
    create,
    pgsql_client,
    pgsql_connection_dsn,
};

require __DIR__ . '/vendor/autoload.php';

$client = pgsql_client(pgsql_connection_dsn('postgres://shop:secret@127.0.0.1:5432/shop'));

$client->execute(
    create()->table('orders')
        ->ifNotExists()
        ->column(column('id', column_type_bigint())->primaryKey())
        ->column(column('email', column_type_text())->notNull())
        ->column(column('country', column_type_text())->notNull())
        ->column(column('amount', column_type_numeric(10, 2))->notNull())
        ->column(column('created_at', column_type_timestamp())->notNull())
);

The third tab creates the same PostgreSQL table with flow-php/postgresql, Flow's own PostgreSQL library. It has a query builder, so there is no SQL string in the script.

Import a CSV file with one INSERT per row

The import everybody writes first: read a row, insert a row.

MySQL

<?php

declare(strict_types=1);

$database = new PDO('mysql:host=127.0.0.1;dbname=shop', 'shop', 'secret', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$insert = $database->prepare('INSERT INTO orders (id, email, country, amount, created_at) VALUES (?, ?, ?, ?, ?)');

$file = fopen(__DIR__ . '/orders.csv', 'r');
fgetcsv($file, escape: '');

while (($row = fgetcsv($file, escape: '')) !== false) {
    $insert->execute($row);
}

fclose($file);

PostgreSQL

<?php

declare(strict_types=1);

$database = new PDO('pgsql:host=127.0.0.1;dbname=shop', 'shop', 'secret', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$insert = $database->prepare('INSERT INTO orders (id, email, country, amount, created_at) VALUES (?, ?, ?, ?, ?)');

$file = fopen(__DIR__ . '/orders.csv', 'r');
fgetcsv($file, escape: '');

while (($row = fgetcsv($file, escape: '')) !== false) {
    $insert->execute($row);
}

fclose($file);

It works. For a hundred thousand orders it takes two minutes in PostgreSQL and more than four minutes in MySQL, because every order is a separate trip to the database.

The second night it ends with a duplicate key error

The next night the file comes again, with the same orders and a few new ones:

MySQL

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'orders.PRIMARY'

PostgreSQL

Fatal error: Uncaught PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(1) already exists.

The first order is already in the table, so the import stops at the first row. What we need is an upsert: insert the order when it's new, update it when it's already there. The SQL for that is different in every database. It's ON DUPLICATE KEY UPDATE in MySQL and ON CONFLICT ... DO UPDATE in PostgreSQL.

Import and upsert a CSV file with Flow

Flow needs PHP 8.3 or newer.

Doctrine MySQL

composer require flow-php/etl flow-php/etl-adapter-csv flow-php/etl-adapter-doctrine

Doctrine PostgreSQL

composer require flow-php/etl flow-php/etl-adapter-csv flow-php/etl-adapter-doctrine

Flow PostgreSQL

composer require flow-php/etl flow-php/etl-adapter-csv flow-php/etl-adapter-postgresql

And this is the whole import:

Doctrine MySQL

<?php

declare(strict_types=1);

use function Flow\ETL\Adapter\CSV\from_csv;
use function Flow\ETL\Adapter\Doctrine\{mysql_insert_options, to_dbal_table_insert};
use function Flow\ETL\DSL\data_frame;
use Doctrine\DBAL\DriverManager;

require __DIR__ . '/vendor/autoload.php';

$database = DriverManager::getConnection([
    'driver' => 'pdo_mysql',
    'host' => '127.0.0.1',
    'dbname' => 'shop',
    'user' => 'shop',
    'password' => 'secret',
]);

data_frame()
    ->read(from_csv(__DIR__ . '/orders.csv'))
    ->write(to_dbal_table_insert($database, 'orders', mysql_insert_options(
        upsert: true,
        update_columns: ['email', 'country', 'amount', 'created_at'],
    )))
    ->run();

Doctrine PostgreSQL

<?php

declare(strict_types=1);

use function Flow\ETL\Adapter\CSV\from_csv;
use function Flow\ETL\Adapter\Doctrine\{postgresql_insert_options, to_dbal_table_insert};
use function Flow\ETL\DSL\data_frame;
use Doctrine\DBAL\DriverManager;

require __DIR__ . '/vendor/autoload.php';

$database = DriverManager::getConnection([
    'driver' => 'pdo_pgsql',
    'host' => '127.0.0.1',
    'dbname' => 'shop',
    'user' => 'shop',
    'password' => 'secret',
]);

data_frame()
    ->read(from_csv(__DIR__ . '/orders.csv'))
    ->write(to_dbal_table_insert($database, 'orders', postgresql_insert_options(
        conflict_columns: ['id'],
        update_columns: ['email', 'country', 'amount', 'created_at'],
    )))
    ->run();

Flow PostgreSQL

<?php

declare(strict_types=1);

use function Flow\ETL\Adapter\CSV\from_csv;
use function Flow\ETL\Adapter\PostgreSql\{pgsql_insert_options, to_pgsql_table};
use function Flow\ETL\DSL\data_frame;
use function Flow\PostgreSql\DSL\{pgsql_client, pgsql_connection_dsn};

require __DIR__ . '/vendor/autoload.php';

$client = pgsql_client(pgsql_connection_dsn('postgres://shop:secret@127.0.0.1:5432/shop'));

data_frame()
    ->read(from_csv(__DIR__ . '/orders.csv'))
    ->write(to_pgsql_table($client, 'orders')->withInsertOptions(pgsql_insert_options(
        conflictColumns: ['id'],
        updateColumns: ['email', 'country', 'amount', 'created_at'],
    )))
    ->run();

Flow doesn't send the orders one by one. It sends them in batches, many orders in one trip to the database.

The upsert is one option. We say which column tells us that the order is already there, and which columns to update, and Flow writes the right SQL for the database we use. We can run the same script every night: new orders are inserted, and the ones that changed are updated.

The third tab doesn't use Doctrine. It writes through flow-php/postgresql, the same library that created the table above.

How long does a CSV import take in PHP

PHP 8.3, a hundred thousand orders with five columns, the databases in Docker:

Code MySQL PostgreSQL
One INSERT per row 264s 120s
Flow with Doctrine 2.3s 2.5s
Flow with Doctrine, the second night 1.9s 2.7s
Flow PostgreSQL - 2.3s
Flow PostgreSQL, the second night - 2.5s

Found a typo or an outdated section? Edit this guide on GitHub

scroll back to top