README

This library extends the SQL library with a wrapper for PDO.

The ORM library extends this library to provide object–relational mapping.

Installation

composer require wtframework/dbal

Documentation

Configuration

MariaDB
MySQL
SQLite
PostgreSQL
SQL Server

Executing an unprepared statement

Use the unprepared method to execute an unprepared statement.

$response = DB::unprepared("SELECT * FROM users");

$response = DB::select()->from("users")->unprepared();

Preparing a statement

Use the prepare method to prepare a statement.

$response = DB::prepare("SELECT * FROM users WHERE user_id = ?");

$response = DB::select()->from("users")->where("user_id", "?")->prepare();

Use the bind method to bind any parameters.

$response->bind(1);

Use the execute method to execute the statement

$response->execute();

Preparing and executing a statement

Use the execute method to prepare and execute a statement.

$response = DB::execute("SELECT * FROM users WHERE user_id = ?", 1);

$response = DB::select()->from("users")->where("user_id", 1)->execute();

When using the statement builder you may also execute the statement by calling it as a function.

DB::insert()->into("users")();

Retrieving a result set

Use the get method to return a single row result set.

DB::get("SELECT * FROM users WHERE user_id = ?", 1);

DB::select()->from("users")->where("user_id", 1)->get();

You may also use the get method on any response.

$response->get();

Use the all method to return the result set as an array.

DB::all("SELECT * FROM users");

DB::select()->from("users")->all();

You may also use the all method on any response.

$response->all();

Miscellaneous

Use the insertID method after executing a statement to return the last insert ID.

DB::insert()->into("users")->execute();

DB::insertID();

Use the affectedRows method after executing a statement to return the number of rows inserted or updated.

$response = DB::update()->table("users")->set('active', 1)->execute();

$response->affectedRows();

Transactions

Use the beginTransaction, commit, and rollback methods to perform transactions.

DB::beginTransaction();
DB::commit();
DB::rollBack();

You may also use the transaction method to automatically begin a transaction that will commit on success or roll back on failure.

DB::transaction(function ()
{

  DB::insert()->into("users")->execute();

  // ...

});

Using a non-default database connection

If you have more than one connection and wish to use a non-default connection then you may use the connection method passing it the connection name. This will return an instance of WTFramework\DBAL\Connection which shares the same methods as those documented above.

$response = DB::connection("mirror")->select()->from("users")->where("user_id", 1)->get();

Statements

Each of these static methods will return a fluent interface for generating SQL statement strings. See the SQL library for further documentation.

use WTFramework\DBAL\DB;

DB::select();
DB::insert();
DB::replace();
DB::update();
DB::delete();
DB::truncate();

DB::create();
DB::alter();
DB::drop();
DB::createIndex($name);
DB::dropIndex($name);

Services

Each of these static methods will return a service class. See the SQL library for further documentation.

DB::bind($value);
DB::column($name);
DB::constraint($name);
DB::cte($name, $stmt);
DB::index($name);
DB::outfile($path);
DB::partition($name);
DB::raw($string);
DB::subpartition($name);
DB::subquery($stmt);
DB::table($name);
DB::upsert();
DB::window($name);

Extending the library

To extend the library you can use the static macro method, passing the new method name and a closure to call. This works for both static and non-static methods. This is available on the DB, Connection, and Response classes.

use WTFramework\DB\DB;

DB::macro('count', function (string $table)
{

  return static::select()
  ->column('COUNT(*) AS counter')
  ->from($table)
  ->get()
  ->counter;

});

DB::count('users');

wtframework / dbal

Maintainers

Details