Download the PHP package mreschke/dbal without Composer

mreschke/dbal

• Introduction
• Collection Info
• Connection and Instantiation
• Entity based Integration
• Adhoc Query Usage

Introduction

A Laravel based dbal helper library and query builder with easy class based entity integration.

This was built before I used Laravel Query Builder or Eloquent and before I built the far more robust and feature rich https://github.com/mreschke/repository

This still has a some advantages like:

• Mssql binary GUID conversions
• Mssql int or decimal to int in PHP return.
  • In MySQL PDO setting PDO::ATTR_EMULATE_PREPARES => false and PDO::ATTR_STRINGIFY_FETCHES => false will return true integers, but does not work for MSSQL dblib connections.
• Cross/linked server support
• Easy and clean entity class integration (but NOT entity mapping)

but is mostly for legacy use now that I built mreschke/repository. It was built primarily for our MSSQL farm, but uses PDO and has MySQL support as well.

This dbal does have a "basic" query builder, but was also designed to throw large RAW queries...much like you would in your SQL IDE. So it can handle lots of full and partial RAW SQL queries (most of which are NOT escaped, be careful).

If starting a new project just use Laravel Query Builder or Eloquent...or better still, https://github.com/mreschke/repository ... seriously, use mreschke/repository if you want anything advanced with a consistent and entity mapped API. See Entity based Integration below.

Collection Info

All multiple returns like ->get(), ->all(), getArray(), getAssoc()...will return as a Laravel Illuminate\Support\Collection. This means you can perform many similar looking methods AFTER dbal has returned...don't confuse these with dbal methods.

Examples of collection usage

$customers = $this->db->query("SELECT * FROM customers")->all();
$customer->count(); //collection level count
$customer->first(); //collection level first
etc...

See https://laravel.com/api/master/Illuminate/Support/Collection.html for more

Connection and Instantiation

Mreschke/dbal is a Laravel library so it utilizes the existing database configuration arrays found in Laravel config/database.php. By default, if you use Mreschke\Dbal\Mssql it will use the sqlsrv connection array and if you use Mreschke\Dbal\Mysql it will use the mysql connection array.

To change the connection, much like Laravel query builder, use

$this->db->connection('othercon')->query(...)->get()

Mreschke/dbal comes with 2 facades, Mysql and Mssql. So you can simply use

Mssql::query('SELECT * FROM customers')->get()

But of course the preferred method is through dependency injection of Mreschke\Dbal\Mssql into your constructor.

Entity based Integration

You can use mreschke/dbal for Adhoc Query Usage. Adhoc would be synonymous with Laravel query builder (not eloquent). Just use it on-the-fly whenever needed.

But an interesting integration comes about when you tie the mreschke/dbal query builder with an individual class.

Think of the class your "entity", like Sso\User.php for example. And that User.php class can of course have its own methods and properties, but it can also be backed by mreschke/dbal tied to a database backend. You can integrate this class with mreschke/dbal by extending the Builder class to add a fluent entity API like:

$sso->user->find(45); // provided by dbal
$sso->user->where('email', '[email protected]'); // provided by dbal
$sso->user->byServer(1); // custom method in our User.php class, but it uses dbal behind the scenes

So this turns a basic PHP class or "entity" object into a fluent model. Something like Eloquent does.

NOTICE: I firmly believe that an entity should be treated like an API and therefore be consistent. If you change the database column names, the return of the entity should not change. With this mreschke\dbal, the results are tied directly to the database which is an issue for consistency. This is why I created https://github.com/mreschke/repository

Mreschke/repository is a much more advanced entity system than simply integrating a database with an entity class. Mreschke/repository adds active records, column and entity mapping abstractions and repository style swappable backends. It is highly recommended to use mreschke/repository if you need this type of entity integration.

The prefered system is Laravel Query Builder (not eloquent) for adhoc queries and mreschke/repository for entity management and APIs.

This is how we would implement a SSO\User.php entity class backed by mreschke/dbal

Because this class extends Builder, you get all the ->find(), ->where(), ->select(), ->orderBy...methods plus the ability to add your own that utilize the builder.. like the byEmail() or serviceManager() methods above

Adhoc Query Usage

Mreschke/dbal also works great for adhoc queries.