Documentation

1.1 Connection overview

Patabase provides two main class to connect to a datasource:

• the Database class represents a connection to a database. This class allows to connect to a given database and perform queries on that database and its tables (get the tables list, create a table, select, delete, insert or update data, ...).
• the Server class represents a connection to a database server. This class allows to connect to a database server and perform queries like create users and databases, rename or drop a database, ....

Create a connection consists in create an instance of Database class or Server class. Those class inherits from Datasource object. The class constructor takes as unique argument a key/value pair array containing the connection settings. Connection settings must specify the driver and other parameters that represent the DSN (Data Source Name), and may contain others optional parameters.

The supported drivers are listed below:

Each driver define the minimal required settings to connect to a datasource. The required and optional parameters for each driver are listed below:

1. In case of sqlite, the database attribute represents the full path to database. The database attribute can be ':memory:' to create an in memory database connection.
2. Required in case of Database connection, N/A in case of Server connection.
3. Default is UTF-8.

The code that creates a connection should be placed in a try{} catch{} block. In addition to PDOException create a connection with invalid or missing settings parameters will raise an exception (Patabase\Exception\InvalidArgException or Patabase\Exception\MissingArgException).

Here are examples of connection settings:

// sqlite database settings
$settings = array(
    'driver'    => 'sqlite', 
    'database'  => 'YOUR_FILE_PATH'
);

// postgres server settings with port
$settings = array(
        'driver'    => 'pgsql', 
        'hostname'  => 'localhost', 
        'username'  => 'YOUR_USER_NAME',
        'password'  => 'YOUR_PASSORD'
        'port'      => 'YOUR_PORT'
);

// mysql database settings
$settings = array(
        'driver'    => 'mysql', 
        'hostname'  => 'localhost', 
        'database'  => 'MyDatabase',
        'username'  => 'YOUR_USER_NAME',
        'password'  => 'YOUR_PASSORD'
);

1.2 Connect to a database

To connect to a database, you use the Database class. Database class must be instanciated by passing connection settings in constructor. Patabase may contains driver specific derived class, like SqliteDatabase. Those class can also be instanciated by passing settings as parameters, but they may contains static method to help define the settings. For example, the Driver\Sqlite\SqliteDatabase class contains a build in static method createInstance($fileName) that requires file path as unique parameter.

The following examples show how to create Databaseinstance with minimal configuration. See connection overview section to know the optional parameters for each driver.

Connect to sqlite using Database class and given settings:

use Kristuff\Patabase\Database;
$db = new Database(array('driver' => 'sqlite', 'database' => 'YOUR_FILE_PATH')); 

Connect to sqlite using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and given settings:

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = new SqliteDatabase(array('driver' => 'sqlite', 'database' => 'YOUR_FILE_PATH')); 

Connect to sqlite using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and build in createInstance() static method (required only file path as parameter):

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = SqliteDatabase::createInstance('YOUR_FILE_PATH');

Create a sqlite memory instance using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and build in createMemoryInstance() static method (no parameter):

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = SqliteDatabase::createMemoryInstance('YOUR_FILE_PATH');

In Sqlite, if the specified file doesn't exists, a file is automatically created in case of successfull connection.

Connect to Mysql database:

$db = new Kristuff\Patabase\Database(array(
        'driver' => 'mysql', 
        'username' => 'YOUR_USERNAME', 
        'password' => 'YOUR_PASSWORD', 
        'database' => 'YOUR_DATABASE'
)); 

Connect to Posgres database:

$db = new Kristuff\Patabase\Database(array(
        'driver' => 'pgsql', 
        'username' => 'YOUR_USERNAME', 
        'password' => 'YOUR_PASSWORD', 
        'database' => 'YOUR_DATABASE'
)); 

1.3 Connect to a database server

To connect to a database server, you use the Server class. Server class must be instanciated by passing connection settings in constructor, like Database class. in case of database server connection, the username and password refer to server (when it refers to database in case of database connection).

The following examples show how to create Serverinstance with minimal configuration. See connection overview section to know the optional parameters for each driver.

The following example shows how to connect to a Mysql Server.

$settings = array('driver'=> 'mysql', 
                    'hostname'=> 'localhost', 
                    'username'=> 'USER_NAME',
                    'password' => 'PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
// Do something with server

The following example shows how to connect to a Postgres Server.

$settings = array('driver'=> 'pgsql', 
                    'hostname'=> 'localhost', 
                    'username'=> 'USER_NAME',
                    'password' => 'PASSWORD');
$pgsql = new Kristuff\Patabase\Server($settings);
// Do something with server

1.4 Close a connection

Each of the Database and Server class contains a closeConnection() method that releases the current connection. This method releases the connection and settings, that means when you call the closeConnection() method, you cannot connect again to the datasource using the same Datasource instance.

Generally, you wont need to call the closeConnection() method. Just set the Datasource class (Database or Server) to null : the destructor of the class will internally call the closeConnection() method.

The following examples show how to close a database connection:

$db = new Kristuff\Patabase\Database( ... );
(...)
$db = null;

The following examples show how to close a server connection:

$srv = new Kristuff\Patabase\Server( ... ) ; 
(...)
$srv = null;

2.1 Database class overview

The Database class inherits from Datasource and contains the following methods:

2.2 Transactions

Database queries could be executed in a transaction. Use the Database::beginTransaction(), Database::commitTransaction() to start a transaction and commit changes. Use the Database::rollback() to cancel changes.

You can also check if the driver is in transaction with Database::inTransaction() method.

The way Patabase handles Exception changes when using transaction. A transaction must be placed in a try{} catch{} block. See Handle query errors.

try {
    // start transaction
    $database->beginTransaction();
    (...)
    // commit changes
    $database->commit();
}
catch(SqlException $e) {
    // rollback changes if an error occured
    $database->rollback();
    // you may also want to check error with: 
    //  $e->getCode() and $e->getMessage()
}

2.3 Get all tables in current database

Use the Database::tables() method to get the list of tables in current database. The Database::tables() method returns an arrayarray with the name of each table.

$tables = $db->getTables();
print_r($tables);

array (size=2)
  0 => string 'user_role' (length=9)
  1 => string 'user' (length=4)

2.4 Get a table object

Use Database::table($name) method to get a new Table instance. You must specify the name of the table. The method does not throw any error when the given table does not exist, but the queries executed with this object will failed.

For example, to access a Table named 'user' from a Database object, you write:

$table = $database->Table('user');

You generally don't need to get an instance of Table to perform query on a table. All Table methods are alias for Database methods. It could be useful to use a Table to perform multiple queries on the same table.

2.5 Enable/Disable foreign keys

Foreign keys are enabled by default. You can use the Database::disableForeignKeys() and Database::enableForeignKeys() to disable or enable foreign key.

$database->disableForeignKeys();
(...)
$database->enableForeignKeys(); 

The enableForeignKeys() and disableForeignKeys() have no effect on Postgres.

2.6 Create a foreign keys

You can use the Database::addForeignKey($fkName, $srcTable, $srcColumn, $refTable, $refColumn) to add a foreign key. You must specify the constraint name, and sources and references tables and columns. This method returns true if the foreign key has been created, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors

The following example show how to create a foreign key on table 'user' when the column 'user_role' with reference to column 'role_id' on table 'role':

$db->addForeignKey('fk_user_role', 'user', 'user_role', 'role', 'role_id'));

The dropForeignKeys() and addForeignKeys() methods are not supported on Sqlite and return false. Constraints must be created when creating the table on Sqlite and can't be deleted.

2.7 Drop a foreign keys

You can use the Database::dropForeignKeys($fkName, $tableName) to drop a foreign key. You must specify the constraint name and table name. This method returns true if the foreign key has been dropped, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors

The following example show how to drop a foreign key named 'fk_user_role' on table 'user':

$database->dropForeignKeys('fk_user_role', 'user');

The dropForeignKeys() and addForeignKeys() methods are not supported on Sqlite and return false. Constraints must be created when creating the table and can't be deleted on Sqlite.

3.1 Table class overview

The Table class contains the following methods:

3.3 Inserting data (SQL INSERT INTO)

Use the Database::insert() or Table::insert() method to insert data in a table.

In this section

• 3.3.1 Insert class overview
• 3.3.2 Basic usage
• 3.3.3 Prepared query
• 3.4.4 Get affected rows (rowCount)

Database->insert('tablename')->values(...)->execute()

Database->table('tablename')->insert(...)->execute()

$database->insert('user')
         ->values(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42))
         ->execute();

$database->table('user')
         ->insert(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42))
         ->execute();

$query = Database->insert('tablename')->prepare( ... );
$query->values( ... )->execute()

$query = Database->table('tablename')->insert( ... )->prepare( ... );
$query->values( ... )->execute()

// prepare query with desired columns
$query = $database->insert('user')->prepare('id','name','age');

// set values and execute query. Here we use values() method
// so we can set all values in one statement
$query->values(array('id' => 4, 'name' => 'Chris', 'age' => 38))
      ->execute();

// set other values and execute query.  Here we use setValue() method
// for each column
$query->setValue('id', 2);
$query->setValue('name', 'Steve');
$query->setValue('age', 32);
$query->execute();

// set other values and execute query (same code like before writted on one call)
$query->setValue('id', 3)
      ->setValue('name', 'John')
      ->setValue('age', 18)
      ->execute();

//define the query
$query =  $database->insert('user')->values(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42));
// execute
$query->execute();
// get rowCount
$rowCount = $query->rowCount();

3.5. Delete data (SQL DELETE)

Use the Database::delete() or Table::delete() method to delete data in a given table.

In this section

• 3.5.1 Delete class overview
• 3.5.2 Basic usage
• 3.5.3 Filter data (SQL WHERE)
• 3.5.4 Get affected rows (rowCount)

Database->delete('tableName')->[parameter_methods]->execute()

Database->table('tableName')->delete()->[parameter_methods]->execute()

$database->delete('user')
         ->execute();
//or
$database->table('user')
         ->delete()
         ->execute();

$database->delete('user')
         ->whereEqual('id', 2)
         ->execute();
//or
$database->table('user')
         ->delete()
         ->whereEqual('id', 2)
         ->execute();

//or
$database->table('user')
         ->delete()
         ->where()->equal(('id', 2)
         ->execute();

//define the query
$query =  $database->delete('user');
// execute
$query->execute();
// get rowCount
$rowCount = $query->rowCount();

3.7 Test if a table exists

Use the Database::tableExists($name) method to test if a table exists in current database. You must specify the table name. If you already have an instance of Table, you can also use the Table::exists() method that does not take any parameter. Those methods return true if the table exists, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors

For example, test if a table named 'user' exists in database:

$result = $db->tableExists('NotExistingTable');

$result = $db->table('NotExistingTable')->exists();

3.8 Rename a table

Use the Database::renameTable($name, $newName) method to rename an existing table. You must specify the current name and the new name for the table. If you already have an instance of Table, you can also use the Table::rename($newName) method that does take only the new name as parameter. Those methods return true if the table has been renamed, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors

For example, rename the table named 'user' to 'new_name' in current database:

$db->renameTable('user', 'new_name');

$db->table('user')->rename('new_name');

3.9 Create a table (SQL CREATE TABLE)

Use the Database::createTable($name) or Table::create() method to create a table. Those method return a new Query\CreateTable instance you can configure and execute using CreateTable::execute().

In this section

• 3.9.1 CreateTable class overview
• 3.9.2 Add columns
• 3.9.3 Define column type
• 3.9.4 Define null / not null contraints
• 3.9.5 Define primary key contraints
• 3.9.6 Define unique contraint
• 3.9.7 Define defaults values
• 3.9.8 Define auto increment column
• 3.9.9 Define foreign key contraints
• 3.9.10 Create table if not exists

Database->createTable('tableName')->[parameter_methods]->execute()

Database->table('tableName')->create()->[parameter_methods]->execute()

$database->createTable('user')
         ->column('user_name', 'varchar(50)')
         ->execute();

$database->createTable('user')
         ->column('id',     'int',          'PK')
         ->column('name',   'varchar(50)',  'NOT NULL')
         ->column('email',  'varchar(255)', 'NOT NULL')
         ->execute();

$floatType = $database->getDriverName() === 'mysql' ? 'float' : 'real';
$timestampType = $database->getDriverName() === 'sqlite' ? 'int' : 'timestamp';
             
$database->createTable('song')
         ->column('id',       'int',           'PRIMARY KEY')
         ->column('title',    'varchar(150)',  'NOT NULL')
         ->column('track',    'int',           'NULL') 
         ->column('bitrate',  $floatType,      'NULL')
         ->column('rating',   'smallint',      'NULL') 
         ->column('created',  $timestampType,  'NOT NULL', 'DEFAULT', 'CURRENT_TIMESTAMP')
         ->execute();

$database->createTable('user')
         ->column('id',     'int',         'PK')
         ->column('name',   'varchar(50)', 'NOT NULL')
         ->column('option', 'varchar(50)', 'NULL')
         ->execute();

$database->createTable('user')
         ->column('id', 'int', 'pk')
         ->execute();

$database->createTable('user')
         ->column('id',     'int',          'PK')
         ->column('name',   'varchar(50)',  'NOT NULL')
         ->column('email',  'varchar(255)', 'NOT NULL' , 'UNIQUE')
         ->execute();

$query = $database->createTable('user');
$query->column('id',       'int',         'PK');

// default null is generallay not required for nullable columns:
$query->column('option1',  'varchar(10)', 'NULL',     'DEFAULT', 'NULL');   // similar
$query->column('option2',  'varchar(10)', 'NULL');                          // similar

// int value
$query->column('option3',  'int',         'NOT NULL', 'DEFAULT',  555);

// string value
$query->column('name',     'varchar(50)', 'NOT NULL', 'DEFAULT', 'toto');

// current timestamp
$query->column('created',  'timestamp',   'NOT NULL', 'DEFAULT', 'CURRENT_TIMESTAMP');
$query->execute();

$database->createTable('user')
         ->column('id', 'int', 'pk', 'ai')
         ->execute();

$database->createTable('customer')
          ->column('customerId'     , 'int'         , 'PK')
          ->column('customerName'   , 'varchar(50)' , 'NOT NULL')
          ->execute(); 

$database->createTable('order')
         ->column('orderId'         , 'int'         , 'PK')
         ->column('orderCustomerId' , 'int'         , 'NOT NULL')
         ->column('orderDate'       , 'varchar(10)' , 'NOT NULL')
          // add foreign key
         ->fk('fk_order_customer'   , 'orderCustomerId', 'customer', 'customerId')
         ->execute();

$database->createTable('user')
         ->ifNotExists()
         ->column('id',     'int',         'PK')
         ->column('name',   'varchar(50)', 'NOT NULL')
         ->column('option', 'varchar(50)', 'NULL')
         ->execute();

3.10 Drop a table (SQL DROP TABLE)

Use the Database::dropTable($name) method to drop a table in current database You must specify the table name. If you already have an instance of Table, you can also use the Table::drop() method that does not take any parameter. Those methods return true if the table has been dropped, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors.

The followwing example shows how to drop a table named 'user'in current database:

$database->dropTable('user');

$database->table('user')->drop();

4.1 Server class overview

The Server class inherits from Datasource and contains the following methods:

4.2 Check if a database exists

Use the Server::databaseExists($databaseName) method to check if a database exists in the current server. You must specify the database name. This method returns true if the given database exists, otherwise false.

The following example show how to test if a database named 'mydatabase' exists on the current server.

$server = new Kristuff\Patabase\Server([...]);
$exists = $server->databaseExists('mydatabase');

4.3 Get databases list

Use the Server->getDatabases() method to get the list of databases in the current server. This method returns an array with databases names.

The following example shows how to list the databases in a mysql server:

$settings = array('driver'=> 'mysql', 
    'hostname'=> 'localhost', 
    'username'=> 'YOUR_USER_NAME',
    'password' => 'YOUR_PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
$databases = $mysql->getDatabases();

// print results
print_r(json_encode($databases)); 

// Output
["information_schema","Database1,"Database2","Database3", ...]

4.4 Create a database

Use the Server::createDatabase($databaseName [, $owner = NULL]) method to create a new database on server. You must specify the database name. The $owner argument can be specified for Postgres (default is null).The $owner argument will be ignored on all ohers drivers.

If you want to create a user, a database and grant permission on database to that user, you can also use Server::createDatabaseAndUser($databaseName, $userName, $password), the method internally call the Server::createUser($userName, $password), Server::createDatabase($databaseName), and Server::grantUser(databaseName, $userName).

Those methods return true if the database has been created, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors.

The following example show how to create a database named 'testdb' in a mysql server:

$settings = array('driver'=> 'mysql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabase('testdb');

The following example show how to create a database named 'testdb' with owner'toto' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabase('testdb', 'toto');

The following example show how to create a database named 'testdb' and a user named 'toto' in a mysql server:

$settings = array('driver'=> 'mysql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabaseAndUser('testdb', 'toto', 'totopass');

In Sqlite, there is no server concept, create a Database with wanted filename to create a database. if the specified file doesn't exists, a file is automatically created in case of successfull connection on Sqlite.

4.5 Drop a database

Use the Server::dropDatabase($databaseName [, $ifNotExists = false]) method to drop a database. You must specify the database name. When $ifNotExists is set to false (Its default value), this methods returns true if the database has been dropped, otherwise false. When $ifNotExists is set to true, database is dropped only when exists and method will return true if the query runs without error (database dropped or does not exists), otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to drop a database named 'testdb' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);

// suppose a database 'testdb' that exists, can be dropped and you have permission to drop it

$drop1      = $pgsql->dropDatabase('testdb');           // Drop the database 'testdb' (shoud be OK)
$drop1error = $pgsql->hasError();                       // check for errors after drop 

$drop2      = $pgsql->dropDatabase('testdb', TRUE);     // Drop the database 'testdb' ONLY when exists (shoud be OK)
$drop2error = $pgsql->hasError();                       // check for errors after drop 

$drop3      = $pgsql->dropDatabase('testdb');           // Drop the database 'testdb' (shoud be wrong, it still not exists...))
$drop3error = $pgsql->hasError();                       // check for errors after drop

// the code will produce the following results
// $drop1 => true,  $drop1error  => false
// $drop2 => true,  $drop2error  => false
// $drop3 => false, $drop3error  => true

4.6 Check if a database exists

Use the Server::userExists($userName) methodTo check if a database exists in the current server. You must specify the user name. This method returns true if the given user exists, otherwise false.

The following example show how to test if a user named 'toto' exists on the current server.

$server = new Kristuff\Patabase\Server([...]);
$exists = $server->userExists('toto');

4.7 Get users list

Use the Server::getUsers() method to get the list of users in the current server. This method returns an array with users names.

The following example shows how to list the users in a mysql server:

$settings = array('driver'=> 'mysql', 
    'hostname'=> 'localhost', 
    'username'=> 'YOUR_USER_NAME',
    'password' => 'YOUR_PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
$databases = $mysql->getDatabases();

// print results
print_r(json_encode($databases)); 

// Output
["xxx","xxx,"xxx","xxx", ...]

4.8 Create a user

Use the Server::createUser($userName , $password) method to create a user. You must specify the user name and it's password. The method returns true if the user has been created, otherwise false.

If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to create a user named 'toto' with password '1234' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createUser('toto', '1234');

4.9 Grant a user

Use the Server::grantUser($databaseName, $userName) method to grant user permission on a database. You must specify the database name and user name. This methods returns true if permission have been granted, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to grant permission to a user named 'toto' on database named 'mydatabase' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->grantUser('mydabase', 'toto);

4.10 Drop a user

Use the Server::dropUser($userName [, $ifNotExists = False]) method to drop a user. You must specify the user name. When $ifNotExists is set to False (Its default value), this methods returns true if the user has been dropped, otherwise false. When $ifNotExists is set to true, user is dropped only when exists and method will return true if the query runs without error (user dropped or does not exists), otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to drop a user named 'toto' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'postgres', 'password' => 'XXXXXXXXXXXX');
$pgsql = new Kristuff\Patabase\Server($settings);

// suppose a user 'toto' that exists, can be dropped and 
// you have permission to drop it

$drop1 =        $pgsql->dropUser('toto');       // Drop the user 'toto'(should be OK)
$drop1error =   $pgsql->hasError();             // check for errors after drop 

$drop2 =        $pgsql->dropuser('toto', true); // Drop the user 'toto' only when exists (should be OK)
$drop2error =   $pgsql->hasError();             // check for errors after drop

$drop3 =        $pgsql->dropUser('toto');       // Drop the user 'toto' (should be wrong, it still not exists...)
$drop3error =   $pgsql->hasError();             // check for errors after drop

// the code will produce the following results
// $drop1 => true,  $drop1error  => false
// $drop2 => true,  $drop2error  => false
// $drop3 => false, $drop3error  => true

5.1 Handle errors without transaction

Without transaction, Patabase does not throw any error when a query failed. The query method will return false or an empty result in case of Select query:

• When a method from Datasource failed or has no results, you can check the presence of errors with Datasource::hasError() (return bool) then check the error with Datasource::errorCode() and Datasource::errorMessage() methods.

  Errors are reseted before each query. You need to check error after each query you made.

  The following example show a way to handle error in a Database::renameTable() method:

  // define a wrong query
  $renamed = $database->renameTable('wrongTable', 'newname');
  
  // if the query failed, check error in Database
  if (!$renamed && $database->hasError()) {
      echo $database->errorCode();
      echo $database->errorMessage();
  }

  The following example show a way to handle error in a Server::createDatabase() method.

  // define a wrong query
  $created = $server->createDatabase('AlreadyExistingDatabase');
  
  // if the query failed, check error in Server
  if (!$created && $server->hasError()) {
      echo $server->errorCode();
      echo $server->errorMessage();
  }

• When a method from QueryBuilder (base class for all builded query) failed or has no results, you can check the presence of errors with QueryBuilder::hasError() then check the error with QueryBuilder::errorCode() and QueryBuilder::errorMessage() methods.

  Errors are reseted before each query execution. You need to check error after each query you execute.

  The following example show a way to handle error in a Insert query:

  // define a wrong query
  $insert = $database->insert('wrongTable')->setValue(['wrongcolumn', 1]);
  
  // get the result of execute()
  $inserted = $insert->execute();
  
  // if the query failed, check error in Query
  if (!$inserted && $insert->hasError()) {
      echo $insert->errorCode();
      echo $insert->errorMessage();
  }

5.2 Handle errors in transaction

When using transaction, Patabase may throw SqlException. That way, developpers do not need to check the result of each query inside a transaction block. But that mean the transaction code must be placed in a try{} catch{} block. You can use the SqlException::getCode() and SqlException::getMessage() methods to check the error.

The following example show a way to handle error in trsaction block:

try {
    // start transaction
    $database->beginTransaction();
    (...)
    // commit changes
    $database->commit();
}
catch(SqlException $e) {
    // rollback changes if an error occured
    $database->rollback();

    // you may also want to check error with: 
    //  $e->getCode() and $e->getMessage()
}

See also Transactions