Introduction
Laravel is a powerful MVC framework that simplifies the development process through its elegant syntax and rich set of features. One of these features includes easy database connections and migrations. In this tutorial, we’ll be looking at how to set up and connect to an SQLite database in a Laravel application.
Prerequisites
- Basic knowledge of Laravel
- Laravel installed on your local development environment
- SQLite installed on your local system
- Composer, a dependency manager for PHP
Setting Up SQLite
SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. This makes it ideal for small to medium applications, prototyping, and development.
Creating SQLite Database
Create an empty file to serve as your SQLite database. You can create it anywhere in your Laravel project. For this tutorial, the database file will be named database.sqlite and placed in the database directory.
touch database/database.sqlite
Configuring the .env File
Edit your .env file in the root of your Laravel project. Set DB_CONNECTION to sqlite. If there are other database configurations for DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, and DB_PASSWORD, make sure you either update them accordingly or comment them out as they are not necessary for SQLite:
DB_CONNECTION=sqlite
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=laravel
# DB_USERNAME=root
# DB_PASSWORD=
Configuring the Database Config File
In the config/database.php file, ensure that the sqlite array points to the correct database file. It should look like this:
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
Note that the 'database' key is using the database_path() helper method to locate the SQLite file within the project.
Making a Model and Migration
With the connection set up, you can generate models and migrations as you would with any Laravel project:
php artisan make:model Post -m
This command creates a new model named Post and the accompanying migration file.
Editing the Migration
Edit the generated migration file for the Post model to add some columns to the posts table:
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->text('body');
$table->timestamps();
});
}
Running Migrations
To update the SQLite database with our new table structure, we run the migrations:
php artisan migrate
If everything is set up correctly, you will see a message indicating that the migrations have been successfully executed.
Interacting with the Database
Once your database and tables are set up, you can easily interact with them using Eloquent.
Creating a Record
Create a new Post instance and save it to the database:
$post = new App\Models\Post();
$post->title = 'My First Post';
$post->body = 'This is the body of my first post.';
$post->save();
Reading Records
To retrieve the newly created record, you can do the following:
$post = App\Models\Post::find(1);
echo $post->title; // Outputs 'My First Post'
Advanced Usage: Using the Query Builder
Laravel also offers a query builder that lets you interact with the database directly. This is a powerful feature for complex queries and advanced database operations.
Example Query
Here’s an example using the query builder to get the latest 5 posts with a certain condition:
$posts = DB::table('posts')
->where('published', true)
->orderBy('created_at', 'desc')
->take(5)
->get();
Handling Relationships
Defining and using relationships work just like any other Laravel setup:
public function comments()
{
return $this->hasMany('App\Models\Comment');
}
And then retrieving the comments of a post:
$comments = $post->comments;
Conclusion
Laravel’s seamless integration with SQLite provides an approachable entry point for beginners and a quick setup for building and prototyping applications. By following the outlined steps for database creation, configuration, migration, and querying, you have the tools to effectively manage and interact with databases in Laravel.
