Leaf DB #

This is Leaf's simple query builder created in Leaf v1 but still maintained till date. This class provides a convenient but usual way to quickly create and run database queries. It can be used to perform most database operations in your app. It currently supports Mysqli and PDO connections, though we still recommend using Mysqli. There's no need to worry about SQL injection as parameter binding is also supported and easy to use😉💪

Installation #

You can quickly install leaf db with composer or the leaf cli.

composer require leafs/db-old

or with leaf cli:

leaf install db-old

Initialising Leaf DB #

Leaf DB has 2 different packages, 1 for mysqli and the other for PDO. So you can import which ever package you wish to use. Leaf recommends using the mysqli package.

use Leaf\Db\Mysqli;

$db = new Mysqli();

use Leaf\Db\PDO;

$db = new PDO();

Both DB:PDO and DB:Mysqli use the same methods, so all the code below works the same for whichever you're using. We'll alert you if something works differently.

DB connection #

The first thing you need to do to use Leaf DB is to connect to your database. This can be achieved with connect()

On the leaf object #

In v2.1, the default $app->db object has been replaced with Leaf\Db, therefore, you have to initialise DB Mysqli to use it's methods.

DB Mysqli #

use Leaf\Db\Mysqli;

$db = new Mysqli();
$db->connect($host, $user, $password, $dbname);

DB #

use Leaf\Db\PDO;

$db = new PDO();
$db->connect($host, $dbname, $user, $password);

This will set the connection for use within Leaf DB.

Both packages now support auto_connect which allows you to connect to your database using variables set in a .env file.

$db->auto_connect();

Queries #

Making simple queries #

Queries with with Leaf DB are much like what you're used to. Though a query builder, we wan't to maintain the flexibility of normal database queries, hence, we provided the query() method to make your normal database queries.

$db->connect($host, $user, $password, $dbname);

$app->get('/users/all', function () use($app) {
  $users = $db->query("SELECT username FROM users")->fetchAll();
  $app->response()->json($users);
});

As normal as this seems, we take it a step further by providing you with a much simpler way to use prepared statements.

$db->connect($host, $user, $password, $dbname);

$app->get('/users/{id}', function ($id) use($app) {
  $user = $db->query("SELECT username FROM users WHERE id = ?", [$id])->fetchObj();
  $app->response()->json($user);
});

We've looked at making queries, but then query() still makes you type out whatever query you need to use. It's certainly easier than raw queries, but it's nothing impressive. Below are some of Leaf DB's handy methods to make queries even easier.💪😉

Retrieving Data #

Read the select docs for all the information on retrieving data from your database.

Inserting Data #

Read the insert docs for all the information on inserting data into your database.

Updating Data #

This operation uses UPDATE. With Leaf DB:

$db->update();

Update #

We use Leaf DB's update method which takes in a "table", a "column-value" to update and "conditions".

$db->update("posts", "title = 'Post 1'", "title = 'Post One'");

This will look for a post with the title of "Post One" and change it to "Post 1". You can also have multiple conditions:

$db->update("posts", "title = 'Post 1' AND author = 'Mychi Darko'", "title = 'Post One'");

With Parameter Binding #

$db->update("posts", "title = ? AND author = ?", "title = ?", ["Post 1", "Mychi Darko", "Post One"]);

Deleting Data #

This operation uses DELETE. With Leaf DB:

$db->delete();

Delete #

We use Leaf DB's delete method which takes in a "table", and "conditions".

$db->delete("posts", "title = 'Post 1'");

This will look for a post with the title of "Post 1" and delete it.

Others #

Row Count #

Get the number of rows from select

$db->select("posts")->count();

Connection Close #

Close the connection

$db->close();