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 for this module unless you want to use Leaf Auth. If you choose to use Leaf DB in your MVC application, we have already set up everything for you. All you need to do is to head over to your .env file and set up your database connection details. 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

Remember to head over to public/index.php and uncomment the line that says \Leaf\Database::initDb();. This will automatically connect to your database using the details in your environment file.

You can safely skip the "Connecting to a database" section.

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',
]);

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();