Models and Database

Database connection
Creating a model
Finding records
Creating records
Updating records
Deleting records
Collections and items
Advanced model queries
Casts, timestamps, and related deletes

Database connection

Lima connects to MySQL through PDO. The required database values live in your .env file:

DB_HOST=localhost
DB_NAME=my_database
DB_USER=root
DB_PASS=

These are loaded when the app starts. For the full list of supported environment variables, see Configuration.

Creating a model

Models extend Lima\Core\Model. At minimum, a model needs a table name.

use Lima\Core\Model;

class Post extends Model
{
    protected $table = 'posts';
}

If you don't set a primary key, Lima will use the table name followed by _id. For the posts table, that means posts_id.

You can set a custom primary key when your table uses a different column.

class Post extends Model
{
    protected $table = 'posts';
    protected $primaryKey = 'post_id';
}

Finding records

The getByID() method returns a single item or null.

$posts = new Post();
$post = $posts->getByID(12);

For custom queries, models also have access to the query builder.

$posts = (new Post())
    ->where('status', 'published')
    ->order('date_created', 'DESC')
    ->limit(10)
    ->getAll();

Use getAll() when you always want a collection, getSingle() when you want the first matching item, and get() when either a single item or collection is acceptable.

Creating records

Use create() to insert a new row.

$post = (new Post())->create([
    'title' => 'Hello Lima',
    'status' => 'draft',
]);

When the insert succeeds, Lima returns the newly created item. If the insert fails, it returns false.

By default, models add date_created and date_updated values during creation. You can change this with the model's $timestamps property.

Updating records

Updates are built with a query condition followed by update().

$updated = (new Post())
    ->where('post_id', 12)
    ->update([
        'status' => 'published',
    ]);

When timestamps are enabled, Lima will update the date_updated column automatically.

Deleting records

Deletes also use the query builder flow.

$deleted = (new Post())
    ->where('post_id', 12)
    ->delete();

Because this is a direct delete, always set the right where() or wheres() condition before calling delete().

Collections and items

getAll() returns a Collection. Collections include a few small helpers:

$posts = (new Post())->getAll();

$posts->items();
$posts->first();
$posts->last();
$posts->count();
$posts->isEmpty();

Individual rows are returned as Item objects. An item behaves like an array and also includes conversion helpers.

echo $post['title'];

$key = $post->getKey();
$array = $post->toArray();
$object = $post->toObject();

Advanced model queries

Every model extends Lima's query builder, so you can compose more specific database queries directly from a model instance.

$items = (new Post())
    ->select(['post_id', 'title'])
    ->where('status', 'published')
    ->order('date_created')
    ->limit(5)
    ->getAll();

Supported query methods include select(), where(), wheres(), limit(), offset(), order(), insert(), update(), delete(), get(), getSingle(), getAll(), and getCount().

Casts, timestamps, and related deletes

Models include a few properties for shaping data behaviour.

class Post extends Model
{
    protected $table = 'posts';
    protected $primaryKey = 'post_id';
    protected $timestamps = ['created', 'updated'];
    protected $casts = [
        'published_at' => 'datetime',
    ];
}

The datetime cast accepts a DateTime object or a date string and stores it in Y-m-d H:i:s format.

You can disable automatic timestamps by setting an empty array:

protected $timestamps = [];

Models also support a $foreignKeys map for deleting related rows when a parent row is deleted.

protected $foreignKeys = [
    'post_id' => [
        [Comment::class, 'post_id'],
    ],
];

This is useful for small projects, but advanced applications may prefer database-level foreign keys and cascading rules so the database remains the source of truth.

The Docs