Database Abstraction Layer, Schema Introspection, Schema Generation, Query Builders

Overview

Cycle DBAL

Latest Stable Version Build Status Codecov

Secure, multiple SQL dialects (MySQL, PostgreSQL, SQLite, SQLServer), schema introspection, schema declaration, smart identifier wrappers, database partitions, query builders, nested queries.

Documentation

Requirements

Make sure that your server is configured with following PHP version and extensions:

  • PHP 8.0+
  • PDO Extension with desired database drivers

Installation

To install the component:

$ composer require cycle/database

Example

Given example demonstrates the connection to SQLite database, creation of table schema, data insertion and selection:

<?php
declare(strict_types=1);

require_once "vendor/autoload.php";

use Cycle\Database\Config;
use Cycle\Database\DatabaseManager;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases'   => [
        'default' => ['connection' => 'sqlite'],
    ],
    'connections' => [
        'sqlite' => new Config\SQLiteDriverConfig(
            connection: new Config\SQLite\FileConnectionConfig(
                database: 'runtime/database.db'
            ),
        ),
    ],
]));

$users = $dbm->database('default')->table('users');

// create or update table schema
$schema = $users->getSchema();
$schema->primary('id');
$schema->string('name');
$schema->datetime('created_at');
$schema->datetime('updated_at');
$schema->save();

// insert data
$users->insertOne([
    'name'       => 'test',
    'created_at' => new DateTimeImmutable(),
    'updated_at' => new DateTimeImmutable(),  
]);

// select data
foreach ($users->select()->where(['name' => 'test']) as $u) {
    print_r($u);
}

License:

MIT License (MIT). Please see LICENSE for more information. Maintained by Spiral Scout.

Comments
  • Try to install cycle 2.0

    Try to install cycle 2.0

    Updating dependencies Your requirements could not be resolved to an installable set of packages.

    Problem 1 - cycle/database 2.0.0 requires spiral/core ^2.9 -> found spiral/core[dev-master, 2.9.x-dev (alias of dev-master)] but it does not match your minimum-stability. - cycle/orm v2.0.0 requires cycle/database ^2.0 -> satisfiable by cycle/database[2.0.0]. - Root composer.json requires cycle/orm ^2.0 -> satisfiable by cycle/orm[v2.0.0].

    You can also try re-running composer require with an explicit version constraint, e.g. "composer require cycle/orm:*" to figure out if any version is installable, or "composer require cycle/orm:^2.1" if you know which you need.

    type:bug status:ready for adoption 
    opened by sergey-telpuk 6
  • Add psr/log dependency

    Add psr/log dependency

    Steps to reproduce

    1. composer install --no-dev
    2. Running the below example code will complain

    example.php

    <?php
    
    require_once "vendor/autoload.php";
    
    use Cycle\Database\DatabaseManager;
    use Cycle\Database\Driver;      // unused import here
    use Cycle\Database\Config;
    
    $dbal = new DatabaseManager(new Config\DatabaseConfig([
        'databases' => [
            'default' => [
                'driver' => 'runtime'
            ],
        ],
        'connections' => [
            'runtime' => new Config\SQLiteDriverConfig(
                connection: new Config\SQLite\FileConnectionConfig(
                    database: __DIR__ . './runtime/database.sqlite'
                ),
                queryCache: true,
            ),
        ],
    ]));
    
    

    Error message

    PHP Warning:  Uncaught Error: Interface "Psr\Log\LoggerAwareInterface" not found in ../cycle/database/src/Driver/Driver.php:36
    

    Another way to reproduce is to require cycle/orm and do not require cycle/annotated and try to setup as per above example file.

    opened by thgs 3
  • Issue with where('ball', '<',  'water')πŸ›

    Issue with where('ball', '<', 'water')πŸ›

    No duplicates πŸ₯².

    • [X] I have searched for a similar issue in our bug tracker and didn't find any solutions.

    Database

    MySQL

    What happened?

    When I put a where-statement like where('ball', '<', 'water'), I'm being presented with an error like:

    Type: TypeError Code: 0 Message: strtoupper(): Argument #1 ($string) must be of type string, int given File: D:\websites\slim.local\vendor\cycle\database\src\Query\Traits\TokenTrait.php Line: 171

    I have the impression it has something to do with the operator value of '<'. But that isn't an int, is it?

    Obviously I didn't expect an error. Or am I doing something wrong?

    Version

    database 2.0
    PHP 8.1
    
    type:bug status:to be verified 
    opened by sjaakp 2
  • PHP Deprecated

    PHP Deprecated

    PHP8.1.1 Cycle 1.8

    PHP Deprecated: Return type of Spiral\Core\InjectableConfig::getIterator() should either be compatible with IteratorAggregate::getIterator(): Traversable, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 108 PHP Deprecated: Return type of Spiral\Core\InjectableConfig::offsetExists($offset) should either be compatible with ArrayAccess::offsetExists(mixed $offset): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 64 PHP Deprecated: Return type of Spiral\Core\InjectableConfig::offsetGet($offset) should either be compatible with ArrayAccess::offsetGet(mixed $offset): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 72

    opened by sergey-telpuk 2
  • Added support for postgres schemas

    Added support for postgres schemas

    Now you can set specific schemas as string or array in PostgresDriver.

    new PostgresDriver([
        'connection' => 'pgsql:host=127.0.0.1;port=15432;dbname=spiral',
        'username'   => 'postgres',
        'password'   => 'postgres',
        'schema'     => 'private'
    ]);
    // or
    new PostgresDriver([
        'connection' => 'pgsql:host=127.0.0.1;port=15432;dbname=spiral',
        'username'   => 'postgres',
        'password'   => 'postgres',
        'schema'     => ['private', 'public']
    ]);
    

    see https://github.com/cycle/schema-builder/issues/12

    opened by butschster 2
  • Add supporting for array values in the `IN` and `NOT IN` operators using nested syntax

    Add supporting for array values in the `IN` and `NOT IN` operators using nested syntax

    Now you don't need to wrap an array value in the Parameter injection.

    $database->select()
        ->from(['users'])
        ->where([
            'status' => [
                'IN' => ['active', 'blocked']
            ],
            'name' => [
                'NOT IN' => ['foo', 'bar']
            ],
        ]);
    
    type:feature 
    opened by roxblnfk 1
  • Add supporting for array values in the `NOT IN` operator

    Add supporting for array values in the `NOT IN` operator

    Now you don't need to wrap an array value in the Parameter injection.

    $database->select()
        ->from(['users'])
        ->where('status', 'NOT IN', ['active', 'blocked']);
    
    type:feature 
    opened by roxblnfk 1
  • Add supporting for array values in the `IN` operator

    Add supporting for array values in the `IN` operator

    Now you don't need to wrap an array value in the Parameter injection.

    $database->select()
        ->from(['users'])
        ->where('status', 'IN', ['active', 'blocked']);
    
    type:feature 
    opened by roxblnfk 1
  • πŸ’‘ Feature request: __set_state for driver configs

    πŸ’‘ Feature request: __set_state for driver configs

    I have an idea!

    There is some config:

    new DatabaseConfig([
            'default' => 'default',
    
            'databases' => [
                'default' => [
                    'connection' => 'mysql',
                ],
            ],
    
            'connections' => [
                'sqlite' => new \Cycle\Database\Config\SQLiteDriverConfig(
                    connection: new \Cycle\Database\Config\SQLite\MemoryConnectionConfig(),
                    driver: \Cycle\Database\Driver\SQLite\SQLiteDriver::class,
                    queryCache: true,
                ),
    
                'mysql' => new \Cycle\Database\Config\MySQLDriverConfig(
                    connection: new \Cycle\Database\Config\MySQL\TcpConnectionConfig(
                        database: 'project_name',
                        host: '127.0.0.1',
                        port: 3306,
                        user: 'test',
                        password: 'secret',
                    ),
                    queryCache: true,
                ),
            ],
        ]),
    

    It would be good if these settings will be cached. It works well with var_export. But it cannot be recovered because driver configs don't have __set_state methods.

    I have a suggestion to add default method __set_state to \Cycle\Database\Config\DriverConfig and override in another configs when it's required. I can create pull request if this idea will be accepted.

    type:feature 
    opened by wakebit 1
  • Columns use quote parsing to cause SQL error

    Columns use quote parsing to cause SQL error

    No duplicates πŸ₯².

    • [X] I have searched for a similar issue in our bug tracker and didn't find any solutions.

    Database

    MySQL

    What happened?

    I have the query columns a bug happened!

    $result = $database
        ->select([
            'table1.id as t.id'
        ])
        ->from('table1')
        ->fetchAll();
    

    I need this result

    select `table1`.`id` as `t.id` from `table1`;
    

    The actual result is

    select `table1`.`id` as `t`.`id` from `table1`;
    

    Result in query error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

    Version

    database 2.0
    PHP 8.1
    
    type:bug status:to be verified 
    opened by zaishui15 0
  • πŸ› Server closed connection unexpectedly

    πŸ› Server closed connection unexpectedly

    No duplicates πŸ₯².

    • [X] I have searched for a similar issue in our bug tracker and didn't find any solutions.

    Database

    PostgreSQL

    What happened?

    A bug happened!

    Sometimes this error occurred:

    General error: 7 server closed the connection unexpectedly \tThis probably means the server terminated abnormally \tbefore or while processing the request
    

    Perhaps this error should be mapped as ConnectionException and statement must be retried.

    Version

    database 14.2
    PHP 8.1
    
    type:bug status:to be verified 
    opened by kafkiansky 1
  • πŸ’‘ Subquery column alias injection

    πŸ’‘ Subquery column alias injection

    I have an idea!

    I have the query with subquery columns. I need to make aliases for subquery columns for having;

    $result = $database
        ->select([
            'table1.id',
            $database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')), // as cnt1
        ])
        ->from('table1')
        ->fetchAll();
    

    now I need to do weird things like this

    $result = $database
        ->select([
            'table1.id',
            new Fragment('('.$database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')).') as cnt1'),
        ])
        ->from('table1')
        ->having('cnt1', 1)
        ->fetchAll();
    

    We need something like ColumnAliasInjection to make it prettier

    $result = $database
        ->select([
            'table1.id',
            new ColumnAliasInjection(
                $database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')),
                'cnt1'
            )
        ])
        ->from('table1')
        ->having('cnt1', 1)
        ->fetchAll();
    
    type:feature 
    opened by gam6itko 0
  • πŸ›  Interpolator optimization

    πŸ› Interpolator optimization

    No duplicates πŸ₯².

    • [X] I have searched for a similar issue in our bug tracker and didn't find any solutions.

    Database

    No matter.

    What happened?

    I insert 1000-5000 line batches and the default php.ini settings with memory_limit: 256M, then I periodically get allowed memory size of bytes exhausted. Worst of all, this error happens after the insertion, which is why my application can not correctly count the number of inserted batches. The error occurs on this line: https://github.com/cycle/database/blob/bd48dec079d7666dd125e1299be031b123685684/src/Driver/Driver.php#L462 I hope this helps.

    Version

    database 2.0
    PHP 8.1
    
    type:bug status:ready for adoption 
    opened by kafkiansky 1
Releases(2.2.2)
  • 2.2.2(Sep 27, 2022)

    • Fix transaction level changing on disconnect when transaction is starting by @roxblnfk (#76)

    Full Changelog: https://github.com/cycle/database/compare/2.2.1...2.2.2

    Source code(tar.gz)
    Source code(zip)
  • 2.2.1(Jul 2, 2022)

  • 2.2.0(Jun 23, 2022)

    What's Changed

    • Add supporting for PHP 8.1 Enumerations by @roxblnfk (#67)
    • Add supporting for smallint column type by @gam6itko (#58)
    • Fix typo in the \Cycle\Database\Schema\State::forgerForeignKey method by @BeMySlaveDarlin (#53)
    • Fix compatibility with Spiral Framework 3 packages:
      • Remove overriding of the $config property in the DatabaseConfig class by @roxblnfk (#64)
      • Update composer.json dependencies

    New Contributors

    • @gam6itko made their first contribution in https://github.com/cycle/database/pull/58
    • @BeMySlaveDarlin made their first contribution in https://github.com/cycle/database/pull/53

    Full Changelog: https://github.com/cycle/database/compare/2.1.3...2.2.0

    Source code(tar.gz)
    Source code(zip)
  • 2.1.3(May 20, 2022)

    What's Changed

    • Add Stringable interface for Fragment and ActiveQuery by @aquaminer in https://github.com/cycle/database/pull/52
    • Fix query interpolation by @roxblnfk in https://github.com/cycle/database/pull/60

    New Contributors

    • @aquaminer made their first contribution in https://github.com/cycle/database/pull/52

    Full Changelog: https://github.com/cycle/database/compare/2.1.2...2.1.3

    Source code(tar.gz)
    Source code(zip)
  • 2.1.2(Jan 20, 2022)

    What's Changed

    • Fix wrong bind parameter order in a select query with join by @roxblnfk (#48)
    • Fix access to unitialized driver property on ActiveQuery::__debugInfo() call by @roxblnfk (#46)

    Full Changelog: https://github.com/cycle/database/compare/2.1.1...2.1.2

    Source code(tar.gz)
    Source code(zip)
  • 2.1.1(Jan 12, 2022)

    What's Changed

    • Fix phpdoc for the SelectQuery::orderBy method by @butschster (#45)
    • Fix problem with driver cloning by @butschster (#44)
    • Fix Scrutinizer in CI by @msmakouz (#43)

    Full Changelog: https://github.com/cycle/database/compare/2.1.0...2.1.1

    Source code(tar.gz)
    Source code(zip)
  • 2.1.0(Dec 31, 2021)

    What's Changed

    • Add psr/log dependency by @thgs (#35)
    • Add ability to get/set driver name by @butschster (#38)
    • Optimize fetching indexes and primary keys in Postgres driver by @hustlahusky (#37)
    • Fix type casting for column size value in the PG Column class by @rauanmayemir (#34)
    • Downgrade spiral dependencies to stable by @roxblnfk (#40)

    New Contributors

    • @thgs made their first contribution in https://github.com/cycle/database/pull/35
    • @hustlahusky made their first contribution in https://github.com/cycle/database/pull/37
    • @rauanmayemir made their first contribution in https://github.com/cycle/database/pull/34

    Full Changelog: https://github.com/cycle/database/compare/2.0.0...2.1.0

    Source code(tar.gz)
    Source code(zip)
  • 2.0.0(Dec 22, 2021)

    • The package moved from spiral/database to cycle/database
    • Namespace Spiral\Database replaced with Cycle\Database @butschster (#1)
    • Minimal PHP version is 8.0 (#26, #31)
    • Added supporting for postgres schemas @butschster (#2)
    • Added DTO configs @SerafimArts, @msmakouz (#10, #14)
    • Improvements to join methods @butschster
    • Tests have been restructured @butschster (#21)
    • Added method to get transaction level @msmakouz (#23)
    • Added ColumnReturnableInterface for database drivers @butschster (#25)
    Source code(tar.gz)
    Source code(zip)
  • 1.0.3(Nov 15, 2021)

  • 1.0.2(Nov 11, 2021)

  • 1.0.1(Nov 8, 2021)

  • 1.0.0(Oct 4, 2021)

Owner
Cycle ORM
PHP DataMapper ORM and Data Modelling Engine by SpiralScout
Cycle ORM
Spot v2.x DataMapper built on top of Doctrine's Database Abstraction Layer

Spot DataMapper ORM v2.0 Spot v2.x is built on the Doctrine DBAL, and targets PHP 5.4+. The aim of Spot is to be a lightweight DataMapper alternative

Spot ORM 601 Aug 9, 2022
Doctrine Database Abstraction Layer

Doctrine DBAL 4.0-dev 3.0 2.13 N/A N/A Powerful database abstraction layer with many features for database schema introspection, schema management and

Doctrine 8.8k Sep 22, 2022
:gem: Simple MySQLi Abstraction Layer + Doctrine/DBAL support

?? Simple MySQLi Class This is a simple MySQL Abstraction Layer compatible with PHP 7+ that provides a simple and secure interaction with your databas

Lars Moelleken 40 Sep 5, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

null 3 Feb 14, 2022
ORM layer that creates models, config and database on the fly

RedBeanPHP 5 RedBeanPHP is an easy to use ORM tool for PHP. Automatically creates tables and columns as you go No configuration, just fire and forget

Gabor de Mooij 2.2k Sep 28, 2022
Laravel 5 - Repositories to abstract the database layer

Laravel 5 Repositories Laravel 5 Repositories is used to abstract the data layer, making our application more flexible to maintain. See versions: 1.0.

Anderson Andrade 4k Sep 27, 2022
A Redis based, fully automated and scalable database cache layer for Laravel

Lada Cache A Redis based, fully automated and scalable database cache layer for Laravel Contributors wanted! Have a look at the open issues and send m

Matt 497 Aug 29, 2022
Laravel Migrations Generator: Automatically generate your migrations from an existing database schema.

Laravel Migrations Generator Generate Laravel Migrations from an existing database, including indexes and foreign keys! Upgrading to Laravel 5.4 Pleas

Bernhard Breytenbach 3.3k Sep 28, 2022
[Package] Multi-tenant Database Schema Manager for Laravel

Multi-tenant Database Schema Manager for Laravel Tenanti allow you to manage multi-tenant data schema and migration manager for your Laravel applicati

Orchestra Platform 575 Sep 19, 2022
An easy-to-use Artisan command to create a new database schema.

Laravel Create DB This package adds an easy-to-use Artisan command to your application that allows you to create a new database schema. The package is

Aron Rotteveel 10 Jul 9, 2022
The query filter bundle allows you to filter data from QueryBuilder and the Database

The query filter bundle allows you to filter data from QueryBuilder and the Database. you can filter multiple columns at the same time and also you can filter relation fields with two-level deep and without any join in your query builder.

Milad Ghofrani 0 Apr 8, 2022
The query sorting bundle allows you to sort data from QueryBuilder and the Database

The query sorting bundle allows you to sort data from QueryBuilder and the Database. you can sort multiple columns at the same time and also you can sort relation fields with two-level deep and without any join in your query builder.

Bugloos 11 Jun 22, 2022
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.

Please give it a Star if you like the project ?? ❀️ SleekDB - A NoSQL Database made using PHP Full documentation: https://sleekdb.github.io/ SleekDB i

Kazi Mehedi Hasan 710 Sep 26, 2022
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate. NoSQL API

SleekwareDB 10 Sep 18, 2022
A lightweight nearly-zero-configuration object-relational mapper and fluent query builder for PHP5.

Idiorm http://j4mie.github.com/idiormandparis/ Feature/API complete Idiorm is now considered to be feature complete as of version 1.5.0. Whilst it wil

Jamie Matthews 2k Sep 12, 2022
A SQL query builder with zero dependencies

Latitude Query Builder A SQL query builder with zero dependencies. Attempts to be PSR-1, PSR-2, and PSR-4 compliant. Install composer require latitude

Woody Gilk 611 Sep 17, 2022
A MongoDB based Eloquent model and Query builder for Laravel (Moloquent)

Laravel MongoDB This package adds functionalities to the Eloquent model and Query builder for MongoDB, using the original Laravel API. This library ex

Jens Segers 6.2k Sep 22, 2022
SQL to Laravel Query Builder

Marwan - SQL To Laravel Builder SQL to Laravel Query Builder, A Converter written in PHP Features Converts SQL Queries to Laravel Query Builder. Assis

Rexhep Shijaku 152 Sep 9, 2022
Eloquent Filter is a package for filter data of models by the query strings. Easy to use and fully dynamic.

Eloquent Filter Eloquent Filter adds custom filters to your Eloquent Models in Laravel. It's easy to use and fully dynamic. Table of Content Introduct

Mehdi Fathi 306 Sep 30, 2022