Database

Up until now you've learned the basics about configuring and modularizing your application.

Now you will learn how to read and write data from databases.

But first, let's configure our connection.

Connections

In Aurora you access a database through a connection, and to do so you must configure your connections first. So head to the settings directory and look at the database.php file:

return [
    'connections' => [
        'mysql' => [
            'adapter' => Aurora\Database\Adapter\MySQLAdapter::class,
            'schema' => Aurora\Database\Schema\Builder\MySQLBuilder::class,
            'query' => Aurora\Database\Query\Builder\MySQLBuilder::class,
            'options' => [
                'host' => env('MYSQL_HOST'),
                'name' => env('MYSQL_NAME'),
                'user' => env('MYSQL_USER'),
                'password' => env('MYSQL_PASSWORD')
            ]
        ],
        'sqlite' => [
            'adapter' => Aurora\Database\Adapter\SQLiteAdapter::class,
            'schema' => Aurora\Database\Schema\Builder\SQLiteBuilder::class,
            'query' => Aurora\Database\Query\Builder\SQLiteBuilder::class,
            'options' => [
                'file' => env('SQLITE_FILE')
            ]
        ]
    ],
    'stack' => [
        'mysql',
        'sqlite'
    ],
    'default' => 'mysql'
];

As you can see, there is a connections array with the details of each connection, like the class names of its adapter, schema and query builders. Also you'll notice that it extracts some variables from the .env file, like the MySQL credentials or the SQLite schema file.

Then there's the stack, which defines the connections available to the application in order of priority.

And finally there's the default conenction name.

Go ahead, edit your .env file and set your database credentials, leave the default to mysql.

The database service

Aurora bundles a simple database abstraction object which uses adapters to perform operations on different database servers, for example MySQL or SQLite.

A very simple query could be the following:

declare(strict_types = 1);

namespace App\Http;

use Aurora\Database\Database;
use Aurora\Database\Connection;
use Aurora\Helpers\Utilities;

class App extends WebApp {

    function start() {
        $database = resolve(Database::class);
        $ret = $database->query("SELECT * FROM posts");
    }
}

You can execute also queries with parameters:

$database = resolve(Database::class);
$ret = $database->query("SELECT * FROM posts WHERE id = ? AND status = ?", [165, 'Published']);

Insert rows:

$database = resolve(Database::class);
$database->query("INSERT INTO posts (id, title, status) DATA (?, ?, ?)", [0, 'Lorem ipsum', 'Draft']);
$id = $database->lastInsertId();

And also run transactions:

$database = resolve(Database::class);
$database->transaction(function(Connection $connection) {
    $connection->query("INSERT INTO posts (id, title, status) DATA (?, ?, ?)", [0, 'Lorem ipsum', 'Draft']);
    $connection->query("INSERT INTO posts (id, title, status) DATA (?, ?, ?)", [0, 'Dolor sit amet', 'Draft']);
});
$ret = $database->query("SELECT * FROM posts WHERE id = ? AND status = ?", [165, 'Published']);

The transaction will be commited if there is are no errors, otherwise it will be rolled-back.

Notice how in the transaction example you get a Connection object and how you run the query through that object. Internally the other two methods do the same, they get a Connection and call query and lastInsertId on it. The Connection object in this case represents the default connection defined on your database.php settings file.

Using connections

You may use another connection if you like, given that it is available in the stack of connections defined in the settings file.

For example, to use the sqlite connection:

$database = resolve(Database::class);
$database->connection('sqlite', function(Connection $connection) {
    $ret = $connection->query("SELECT * FROM test");
    Utilities::debug($ret);
});

That query will be executed on the sqlite connection.

In the following topic we will learn about a powerful tool for crafting complex queries easily.

Next up, the Query builder.