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

Models are a powerful way to interact with your database using an object-oriented approach, which also makes your code more readable and maintainable.

Check out models

You can use Leaf DB to build and run queries that don't fit into a model. Everything has been configured to work out of the box, so you can start querying your database right away.

Skip to builder

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

Leaf DB will not connect to your database until you run a query. This means that you can pass in your database connection at the beginning of your application and only connect when you need to run a query which is a great way to save resources.

Multi-DB Connections NEW

Some applications may need to connect to multiple databases for things like queues and logs, and Leaf DB allows you to keep multiple connections open and query them independently. Here's how you can connect to multiple databases:

db()->addConnections([
  'conn1' => [
    'dbtype' => '...',
    ...
  ],
  'conn2' => [
    'dbtype' => '...',
    ...
  ],
], 'conn1');

The addConnections() method takes an array of connection details for your databases as its first argument and the default connection name as its second argument. You can then switch between connections using the useConnection() method:

db('conn2')->select('users')->all();

If no connection name is provided, Leaf DB will use the default connection.

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