Database

Phenomine provides a convenient way to running database queries. It can be used to perform most database operations in your application. For the present time, Phenomine is only capable of supporting SQL databases including MySQL, MariaDB, PostgreSQL, SQLite, and SQL Server.

The Phenomine database class uses PDO parameter binding to protect your application against SQL injection attacks.

Database Configuration

Before you perform a query to the database, you must first set up the database. Phenomine provides a simpler way to configure your database. To configure the database, go to the config/database.php file and set the database connection credentials.

<?php

return [

    /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for database operations. This is
    | the connection which will be utilized unless another connection
    | is explicitly specified when you execute a query / statement.
    |
    */

    'default' => env('DB_CONNECTION', 'mysql'),


    /*
    |--------------------------------------------------------------------------
    | Database Connections
    |--------------------------------------------------------------------------
    |
    | Below are all of the database connections defined for your application.
    | An example configuration is provided for each database system which
    | is supported by Phenomine. You're free to add / remove connections.
    |
    */

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
        ],

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'phenomine'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4')
        ],

        'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'phenomine'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => env('DB_CHARSET', 'utf8'),
        ],

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'phenomine'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'options' => []
        ],
    ],

    'migration_table' => 'migrations'
];

SQLite Configuration

SQLite databases are contained within a single file on your filesystem. You can create a new SQLite database using the touch command in your terminal: touch database/database.sqlite. After the database has been created, you may easily configure your environment variables to point to this database by placing the absolute path to the database in the DB_DATABASE environment variable:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Running Queries

Once you have configured your database connection, you may run queries using the DB class. The DB class provides methods for each type of query: select, update, insert, delete, and statement.

Running a Select Query

To run a basic query, you may use the select method on the DB class. The select only building the query and not executing it. To execute the query, you must call the get method.

DB::table('products')->select('sku', 'name')->get();

Fetch All Rows

To fetch an entire row from the database, simply use the all() method.

DB::table('products')->all();

Inserting Row To A Table

To insert row, you may use the insert method.

DB::table('products')->insert([
    'sku' => $sku,
    'name' => 'Product 1',
    'price' => 1000
]);

Updating Data

To update data, you may use the update method.

DB::table('products')->update([
    'price' => 2000
])->where('sku', '=', 1234);

Deleting Data

To delete data, you may use the delete method.

DB::table('products')
    ->where('sku', '=', 1234)
    ->delete();

Using Multiple Database Connections

When using multiple database connections, you may access each connection via the connection method on the DB class. The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file.

DB::connection('pgsql')->table('products')->select('sku', 'name')->get();

Database Transactions

You may use the transaction method provided by the DB class to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back and the exception is re-thrown. If the closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

DB::transaction(function () {
    DB::table('products')->insert([
        'sku' => $sku,
        'name' => 'Product 1',
        'price' => 1000
    ]);

    DB::table('products')->update([
        'price' => 2000
    ])->where('sku', '=', 1234);
});

Manual Transactions

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method provided by the DB class:

DB::beginTransaction();

You can rollback the transaction via the rollBack method:

DB::rollBack();

Finally, you can commit the transaction via the commit method:

DB::commit();

Credits

Phenomine database class uses the doctrine/dbal package to provide a simple database abstraction layer. With our own implementation, we have made it easier to use and more convenient for Phenomine users.