aymanelarian/import-query-generator-pg

An efficient Postgres query generator for mass resource import, distinguishing between new records and records to update.

Maintainers

Details

github.com/AymanElarian/import-query-generator-pg

Source

Installs: 5

Dependents: 0

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 9

v1.3 2020-07-25 12:03 UTC

Requires (Dev)

MIT 463afda8f7ee48962030090d0f3611a415a295c8

  • Ayman Elarian <ayman.woop@aymax.com>

This package is not auto-updated.

Last update: 2024-05-27 06:07:23 UTC

README

An efficient query generator for mass resource import, distinguishing between new records and records to update. This library uses Postgree's ON CONFLICT DO UPDATE feature.

its forked from kfirba/import-query-generator and adjusted for postgree sql.

Preface

I highly recommend you at least skim through my blog about this library to get a better understanding of this library.

Installation

You can add this library as a local, per-project dependency to your project using Composer:

composer require AymanElarian/import-query-generator-pg

Usage

use AymanElarian\QueryGenerator;

$table = 'users';
$data = [
    ['name' => 'John', 'email' => 'john@example.com', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
    ['name' => 'Jane', 'email' => 'jane@example.com', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
    ['name' => 'Susy', 'email' => 'susy@example.com', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
];
$excludedColumnsFromUpdate = ['password', 'created_at'];

$queryObject = (new QueryGenerator)->generate($table, $data, $excludedColumnsFromUpdate);

$queryObject->getQuery();
// -> "insert into `users` (`name`,`email`,`password`,`created_at`,`updated_at`) values (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?) on duplicate key update `name`=VALUES(`name`),`email`=VALUES(`email`),`updated_at`=VALUES(`updated_at`)"

$queryObject->getBindings();
// -> ['John', 'john@example.com', 'hashed_password', '2018-01-12', '2018-01-12', 'Jane', 'jane@example.com', 'hashed_password', '2018-01-12', '2018-01-12', 'Susy', 'Susy@example.com', 'hashed_password', '2018-01-12', '2018-01-12']

As you may have noticed, the generator defaults to column=VALUES(column) since this is usually what we use when we attempt to bulk import some data. Need another behavior? You can submit a PR or just open an issue and we can talk about it 🤓.

License

This package is open-sourced software licensed under the MIT license.