Leaf DB

A database is an organized storage system for managing data like your users' profiles or product details. Leaf offers a lightweight module that simplifies database interaction and supports five major database systems.

• MariaDB
• MySQL
• PostgreSQL
• SQLite
• SQL Server

You can install the Leaf database module using the following command:

Leaf CLI

leaf install db

Composer

composer require leafs/db

New to databases?

Databases are essential for most applications, as they help you store and retrieve data efficiently. Check out this video from Linux Academy to learn more about databases and the different types available:

Leaf MVC + DB

Leaf MVC comes with built-in support for models which are a way to programmatically represent resources in your database using PHP classes. For that reason, you have no real need to use the db() function unless you want to quickly run a query without creating a model. Still, everything has been set up for you and Leaf DB will use the default database connection details in your .env file.

Here are a few example connections:

MySQL

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=LeafMVC
DB_USERNAME=root
DB_PASSWORD=

PostgreSQL

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=LeafMVC
DB_USERNAME=root
DB_PASSWORD=

SQLite

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

You can skip the DB connection section: Leaf MVC sets up a deferred connection for you. This means that the connection will only be made when you run a query.

Connecting to a database

The first step to using a database is to create a connection. It's like opening a door to the database, allowing you to interact with it. Here's how you can connect to a database using Leaf:

db()->connect([
  'dbtype' => '...',
  'charset' => '...',
  'port' => '...',
  'unixSocket' => '...',
  'host' => '...',
  'username' => '...',
  'password' => '...',
  'dbname' => '...',
]);

The connect() method takes an array of connection details for your database as its argument. Depending on the database system you're using, you'll need to provide different connection details. Here are some examples of how you can connect to different databases:

MySQL

db()->connect([
  'host' => '127.0.0.1',
  'username' => 'root',
  'password' => '',
  'dbname' => 'Leaf',
]);

PostgreSQL

db()->connect([
  'dbtype' => 'pgsql',
  'host' => '127.0.0.1',
  'username' => 'root',
  'password' => '',
  'dbname' => 'Leaf',
  'port' => '5432',
]);

SQLite

db()->connect([
  'dbtype' => 'sqlite',
  'dbname' => 'db.sqlite',
]);

Deferred database connection NEW

In a lot of cases, your application may have other routes that don't need a database connection, but popping up a connection before the route is hit can be a waste of resources. Leaf DB now allows you to defer your database connection until you actually need it. Here's how you can defer your database connection:

db()->load([
  'dbtype' => '...',
  'charset' => '...',
  'port' => '...',
  'unixSocket' => '...',
  'host' => '...',
  'username' => '...',
  'password' => '...',
  'dbname' => '...',
]);

It takes in the same arguments as connect(), but it doesn't connect to the database immediately. It only connects when you run a query.

Writing simple queries

Once you've connected to a database, you can start writing queries to interact with it. Queries are the commands you run on your database to get, insert, update or delete data. Leaf DB provides a simple way to run queries using the query builder, but also allows you to run raw SQL queries.

We can run queries using the query() method. This method takes in a query string and returns a query builder instance. This means that you can run queries like this:

$users = db()->query('SELECT * FROM users')->all();

The query() method takes an SQL query that you want to execute as its argument. You can then use the query builder methods to modify your query. For example, you can bind values to your query using the bind() method:

db()
  ->query('SELECT * FROM users WHERE id = ?')
  ->bind('1')
  ->fetchObj();

This provides a more secure and dynamic way to write SQL if you need to.

Running queries

There are different kinds of database commands: some give you results (like data) and some don’t. Leaf Db makes it easy to handle both types without any hassle.

You can use execute() to run queries that don't return values. This method returns true if the query was successful and false if it wasn't. You can run a query like this:

db()->query('CREATE DATABASE dbname')->execute();

If you want to run a query that returns data, you can use the all() method to get all the results. For example, you can run a query like this:

$users = db()->query('SELECT * FROM users')->all();

This will return an array of all the users in the database that match the query.

If you only want to get one result, you can use the fetchObj() or fetchAssoc() method. For example, you can run a query like this:

$user = db()
  ->query('SELECT * FROM users WHERE id = ?')
  ->bind('1')
  ->fetchObj();

This will return the matched user as an object.

There may be times when you want to get a single value from a query that returns multiple rows. In such cases, you can use the first() method. For example, you can run a query like this:

$user = db()->query('SELECT * FROM users')->first();