PHPackages                             efureev/laravel-support-db - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Utility & Helpers](/categories/utility)
4. /
5. efureev/laravel-support-db

ActiveLibrary[Utility & Helpers](/categories/utility)

efureev/laravel-support-db
==========================

PHP Support Package for Laravel DB

v4.0.0(1mo ago)36.7kMITPHPPHP >=8.4CI passing

Since Jan 27Pushed 1mo ago1 watchersCompare

[ Source](https://github.com/efureev/laravel-support-db)[ Packagist](https://packagist.org/packages/efureev/laravel-support-db)[ RSS](/packages/efureev-laravel-support-db/feed)WikiDiscussions master Synced 3w ago

READMEChangelog (10)Dependencies (12)Versions (29)Used By (0)

PHP Laravel Database Support
============================

[](#php-laravel-database-support)

[![](https://camo.githubusercontent.com/a0394ec19dc313b661f9c8356ab64ae772669def64dc3dc0527b5e1df4aead15/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7068702d2533453d382e342d626c75652e737667)](https://camo.githubusercontent.com/a0394ec19dc313b661f9c8356ab64ae772669def64dc3dc0527b5e1df4aead15/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7068702d2533453d382e342d626c75652e737667)[![](https://camo.githubusercontent.com/41a8d42a22455bd3be91abd4cb3f178a64733118b5e0778b123d29236a73e06c/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c61726176656c2d2533453d31332e302d7265642e737667)](https://camo.githubusercontent.com/41a8d42a22455bd3be91abd4cb3f178a64733118b5e0778b123d29236a73e06c/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c61726176656c2d2533453d31332e302d7265642e737667)[![Codacy Badge](https://camo.githubusercontent.com/f3727b0001be8b5f57165ad3320e204cc35ac841b1f8a24e8d13fe9ae17454ba/68747470733a2f2f6170692e636f646163792e636f6d2f70726f6a6563742f62616467652f47726164652f3563386239653835383937663463363562356130313764313666366166366362)](https://app.codacy.com/manual/efureev/laravel-support-db)[![PHP Database Laravel Package](https://github.com/efureev/laravel-support-db/workflows/PHP%20Database%20Laravel%20Package/badge.svg)](https://github.com/efureev/laravel-support-db/workflows/PHP%20Database%20Laravel%20Package/badge.svg)[![Latest Stable Version](https://camo.githubusercontent.com/9afa5346f89befd0cdaf63cf6a41c4cf8108dfdfba34871009c58cdfff652721/68747470733a2f2f706f7365722e707567782e6f72672f656675726565762f6c61726176656c2d737570706f72742d64622f762f737461626c653f666f726d61743d666c6174)](https://packagist.org/packages/efureev/laravel-support-db)[![Total Downloads](https://camo.githubusercontent.com/65f2ce8fa6042f3066bcc754dc4b22b23a8df18d59516eda1391a38c85b34c1b/68747470733a2f2f706f7365722e707567782e6f72672f656675726565762f6c61726176656c2d737570706f72742d64622f646f776e6c6f616473)](https://packagist.org/packages/efureev/laravel-support-db)

Description
-----------

[](#description)

Install
-------

[](#install)

```
composer require efureev/laravel-support-db "^4.0"
```

Contents
--------

[](#contents)

- [Ext Column Types](#ext-column-types)
    - [Bit](#bit)
    - [GeoPoint](#geo-point)
    - [GeoPath](#geo-path)
    - [IP Network](#ip-network)
    - [Ranges](#ranges)
    - [UUID](#uuid)
    - [XML](#xml)
    - [Array of UUID](#array-of-uuid)
    - [Array of Integer](#array-of-integer)
    - [Array of Text](#array-of-text)
- [Column Options](#column-options)
    - [Compression](#compression)
- [Views](#views)
- [Indexes](#indexes)
    - [Partial indexes](#partial-indexes)
    - [Unique Partial indexes](#unique-partial-indexes)
- [Extended Schema](#extended-schema)
    - [Create like another table](#create-like-another-table)
    - [Create as another table with full data](#create-as-another-table-with-full-data)
    - [Create as another table with data from select query](#create-as-another-table-with-data-from-select-query)
    - [Drop Cascade If Exists](#drop-cascade-if-exists)
- [Extended Query Builder](#extended-query-builder)
    - [Update records and return deleted records` columns](#update-records-and-return-updated-records-columns)
    - [Delete records and return deleted records` columns](#delete-records-and-return-deleted-records-columns)
- [Extensions](#extensions)

### Ext Column Types

[](#ext-column-types)

#### Bit

[](#bit)

Bit String. [Doc](https://www.postgresql.org/docs/current/datatype-bit.html).

```
$table->bit(string $column, int $length = 1);
```

#### Geo Point

[](#geo-point)

Points are the fundamental two-dimensional building block for geometric types. [Doc](https://www.postgresql.org/docs/current/datatype-geometric.html#id-1.5.7.16.5).

```
$table->geoPoint(string $column);
```

#### Geo Path

[](#geo-path)

Paths are represented by lists of connected points. [Doc](https://www.postgresql.org/docs/current/datatype-geometric.html#id-1.5.7.16.9).

```
$table->geoPoint(string $column);
```

#### IP Network

[](#ip-network)

The IP network datatype stores an IP network in CIDR notation. [Doc](https://www.postgresql.org/docs/current/datatype-net-types.html).

IPv4 = 7 bytes
IPv6 = 19 bytes

```
$table->ipNetwork(string $column);
```

#### Ranges

[](#ranges)

The range data types store a range of values with optional start and end values. They can be used e.g. to describe the duration a meeting room is booked. [Doc](https://www.postgresql.org/docs/current/rangetypes.html).

```
$table->dateRange(string $column);
$table->tsRange(string $column);
$table->timestampRange(string $column);
```

#### UUID

[](#uuid)

The `primaryUUID` can be used to store UUID-type as primary key.

```
$table->primaryUUID(); // create PK UUID-column with name `id`
$table->primaryUUID('custom_name'); // create PK UUID-column with name `custom_name`
```

The `generateUUID` can be used to store UUID-type with/without index (or FK).

On a row creating generates a value with the native `gen_random_uuid()` function (PostgreSQL >= 13, no extension required).

```
// create UUID-column with name `id`. Generate UUID-value by DB (gen_random_uuid()).
$table->generateUUID();

// create UUID-column with name `cid`. Generate UUID-value by DB.
$table->generateUUID('cid');

// create UUID-column with name `cid`. NOT generate UUID-value by DB. Set `nullable`. Default value: `NULL`.
$table->generateUUID('id', null);

// create UUID-column with name `cid`. NOT generate UUID-value by DB. Set `nullable`. Default value: `NULL`. Create Index by this column.
$table->generateUUID('fk_id', null)->index();

 // create UUID-column with name `fk_id`. NOT generate UUID-value by DB.
$table->generateUUID('fk_id', false);

// create UUID-column with name `fk_id`. Generate UUID-value by DB with custom value.
$table->generateUUID('fk_id', fn($column)=>'uuid_generate_v5()');

// create UUID-column with name `fk_id`. Generate UUID-value by DB with custom value.
$table->generateUUID('fk_id', new Expression('uuid_generate_v2()'));
```

#### XML

[](#xml)

The xml data type can be used to store an XML document. [Doc](https://www.postgresql.org/docs/current/datatype-xml.html).

```
$table->xml(string $column);
```

#### Array of UUID

[](#array-of-uuid)

The array of UUID data type can be used to store an array of IDs (uuid type).

```
$table->uuidArray(string $column);
```

#### Array of Integer

[](#array-of-integer)

The array of integer data type can be used to store a list of integers.

```
$table->intArray(string $column);
```

#### Array of Text

[](#array-of-text)

The array of text data type can be used to store a list of string.

```
$table->textArray(string $column);
```

### Column Options

[](#column-options)

#### Compression

[](#compression)

PostgreSQL 14 introduced the possibility to specify the compression method for toast-able data types. You can choose between the default method `pglz`, the recently added `lz4` algorithm and the value `default` to use the server default setting. [Doc](https://www.postgresql.org/docs/current/storage-toast.html).

```
$table->string('col')->compression('lz4');
```

### Views

[](#views)

#### Create views

[](#create-views)

```
// Facade methods:
Schema::createView('active_users', "SELECT * FROM users WHERE active = 1");
Schema::createView('active_users', "SELECT * FROM users WHERE active = 1", true) ;
Schema::createViewOrReplace('active_users', "SELECT * FROM users WHERE active = 1");

// Schema methods:
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint;

Schema::create('users', function (Blueprint $table) {
    $table
        ->createView('active_users', "SELECT * FROM users WHERE active = 1")
        ->materialize();
});
```

#### Dropping views

[](#dropping-views)

```
// Facade methods:
Schema::dropView('active_users');
Schema::dropViewIfExists('active_users');
```

### Indexes

[](#indexes)

#### Partial indexes

[](#partial-indexes)

See:

Example:

```
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint;
Schema::create('table', static function (Blueprint $table) {
    $table->string('code');
    $table->softDeletes();
    $table
        ->partial('code')
        ->whereNull('deleted_at');
});
```

If you want to delete partial index, use this method:

```
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint;

Schema::create('table', static function (Blueprint $table) {
    $table->dropPartial(['code']);
});
```

#### Unique Partial indexes

[](#unique-partial-indexes)

Example:

```
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint;
Schema::create('table', static function (Blueprint $table) {
    $table->string('code');
    $table->softDeletes();
    $table
        ->uniquePartial('code')
        ->whereNull('deleted_at');
});
```

If you want to delete partial unique index, use this method:

```
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint;

Schema::create('table', static function (Blueprint $table) {
    $table->dropUniquePartial(['code']);
});
```

`$table->dropUnique()` doesn't work for Partial Unique Indexes, because PostgreSQL doesn't define a partial (ie conditional) UNIQUE constraint. If you try to delete such a Partial Unique Index you will get an error.

```
CREATE UNIQUE INDEX CONCURRENTLY examples_new_col_idx ON examples (new_col);
ALTER TABLE examples
    ADD CONSTRAINT examples_unique_constraint USING INDEX examples_new_col_idx;
```

When you create a unique index without conditions, PostgresSQL will create Unique Constraint automatically for you, and when you try to delete such an index, Constraint will be deleted first, then Unique Index.

### Extended Schema

[](#extended-schema)

#### Create like another table

[](#create-like-another-table)

Create a table from a source-table. Creates a structure only.
`includingAll` copies all dependencies from source-table.

Creating will be without a data.

```
Schema::create('target_table', function (Blueprint $table) {
    $table->like('source_table')->includingAll();
    $table->ifNotExists();
});
```

#### Create as another table with full data

[](#create-as-another-table-with-full-data)

Copy a table from a source-table. Copy only columns and a data. Without indexes and so on...

```
Schema::create('target_table', function (Blueprint $table) {
    $table->fromTable('source_table');
});
```

#### Create as another table with data from select query

[](#create-as-another-table-with-data-from-select-query)

Create a table from a select query. Copy only columns and a data. Without indexes and so on...

```
Schema::create('target_table', function (Blueprint $table) {
    $table->fromSelect('select id, name from source_table');
});

// or

Schema::create('target_table', function (Blueprint $table) {
    $table->fromSelect(
        'select t1.id, t2.enabled, t2.extra from source_table t1 ' .
        'join source_table_2 t2 on t1.id = t2.src_id ' .
        'where t2.enabled = true'
    );
});

// or

$tbl = 'source_table';
Schema::create(
    $tbl,
    static function (Blueprint $table) {
        $table->string('key', 16)->primary();
        $table->string('title');
        $table->integer('sort')->index();
    }
);

// or

Schema::create(self::TGT_TABLE, function (Blueprint $table) use ($tbl) {
    $table->fromSelect(
        'select gen_random_uuid() as id, key, title, sort from ' . $tbl
    );
});

// or

Schema::create(self::TGT_TABLE, function (Blueprint $table) use ($tbl) {
    $table->fromSelect(
        'select gen_random_uuid() as id, * ' . $tbl
    );
});
```

#### Drop Cascade If Exists

[](#drop-cascade-if-exists)

Automatically drop objects that depend on the table (such as views, indexes, seqs), and in turn all objects that depend on those objects.

```
Schema::dropIfExistsCascade('table');
```

### Extended Query Builder

[](#extended-query-builder)

#### Update records and return updated records` columns

[](#update-records-and-return-updated-records-columns)

```
$list = Model::toBase()->updateAndReturn(['deleted_at' => now()], 'id', 'name');
```

```
$list = Model::where(['enabled' => true])->updateAndReturn(['enabled' => false], 'id');
```

#### Delete records and return deleted records` columns

[](#delete-records-and-return-deleted-records-columns)

```
$list = Model::toBase()->deleteAndReturn('id', 'name');
```

```
$list = Model::where(['enabled' => true])->deleteAndReturn('id');
```

### Extensions

[](#extensions)

#### Create Extensions

[](#create-extensions)

The Schema facade supports the creation of extensions with the `createExtension` and `createExtensionIfNotExists`methods:

```
Schema::createExtension('uuid-ossp');
Schema::createExtensionIfNotExists('uuid-ossp');
```

#### Dropping Extensions

[](#dropping-extensions)

To remove extensions, you may use the `dropExtensionIfExists` methods provided by the Schema facade:

```
Schema::dropExtensionIfExists('tablefunc');
```

You may drop many extensions at once by passing multiple extension names:

```
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');
```

---

Usage
-----

[](#usage)

### Simple example

[](#simple-example)

```