Laravel101 : How to Connect to SQLite and Use Eloquent Models

Hey there! When it comes to building a website, one crucial aspect is the ability to handle data effectively. Typically, websites accomplish this by using a database. In this article, we'll explore how you can connect Laravel to an SQLite database, and we'll also cover how to create a model that can store your data in the database. Sound good? Let's get started!

Frameworks typically offer the flexibility to connect to various databases, and Laravel is no exception. In fact, you can easily connect to different databases depending on your needs. For instance, you might want to use MySQL for your production environment, but for development purposes, you could use SQLite.

In this tutorial, we’ll show you how to connect to SQLite, which is a lightweight, file-based database that’s incredibly easy to work with and set up.

Configuration Database driver

To use SQLite, we need to install the appropriate package:

sudo apt install sqlite3
sudo apt install php-sqlite3 -y

Then, open up the .env file located in the main directory of your project. This file contains important information such as the connection settings for your email service, database, and any other sensitive data. It’s actually a good thing that this file is included in gitignore to maintain the confidentiality of your information.

By default, the .env file defines the connection to the MySQL, Let’s change it to use sqlite driver:

DB_CONNECTION=sqlite

Well, the next step is to create an sqlite file inside our project to store information on it. I did this inside the database folder in the main directory of the project, because by default, Laravel searches the SQLite file at that address:

touch database/database.sqlite

Easy peasy! Our Laravel project is now connected to the SQLite database!

Migrations

Next, I want to introduce a new concept in Laravel called migration. It’s a great tool for managing tables in your database! Each migration file allows you to create new table, modify existing ones by adding or removing columns, and make any other changes you need. While it is possible to rollback changes, it’s important to keep in mind that the migration process is primarily a forward process. So once your data is in the final product database, it’s not easy to rollback changes since you may lost you data. However, during development, you can always rollback changes if needed.

Here’s a tip: I recommend you design the database and tables you want first, and then create a new migration file whenever you need to make changes. This approach is preferable to constantly rolling back changes. *So if you need to make changes, just create a new migration file instead of going back and forth.
*

As you can see in the image above, there are already a bunch of files in the migration path. Don’t worry about them for now, later we can work on it.

To create a migration file in Laravel, you can use the artisan command line tool:

php artisan make:migration <name_of_migration>

This would generate a new migration file in the database/migrations directory. Once you have created your migration file, you can run it using the php artisan migrate command. This will apply any pending migrations to your database:

I’ve made some decision to the project we’re working on in this training series to make it even better. We’re now creating a Reminder Web App where users can add their own tasks.

During this training, we’ll be working on completing this product together, and I’ll guide you through all the necessary steps. By the end of it, you’ll have a solid understanding of how to create a fully functioning notebook website using Laravel. Let’s get started!

Get started with Eloquent model!

Well, The first thing a reminder app needs is a task entity, which is created by a user.

We just talked about migrations, which are responsible for creating and modifying tables in a database. However, Laravel has another powerful feature called Eloquent models that can help us work with data more efficiently.

With Eloquent models, you can create, update, delete, and query data in tables, as well as define complex relationships between data models. In this series, I’ll cover all the useful features of Eloquent models and show you how to harness their power to build a robust reminder app.

To create the task entity, once again we return to the artisan command:

php artisan help make:model

If you enter the above command into your terminal, you’ll see that there are some really helpful option you can use with it

For example, you can use the -m option to create a migration file for the model at the same time. Or, you can use the -c option to create a controller for the model at the same time.

Since we need both a controller and a migration file, we should use the following command instead:

php artisan make:model Task -mc

Well as you can find out from above pic a migration, a controller and a model is created!

Now it’s time to go back to the task migration file and define the task table in the database.

In this class, we’ll see two main functions: up and down. The up function is used for migration and the down function is used for the rollback operation. We can use a builder called schema to translate our description of the table into the appropriate database service we’re using. This description is created using a class called Blueprint.

Don’t worry if these concepts seem a bit confusing right now. All the thing that you need to know is some pre-defined functions to define attributes of our data model

For now, let’s focus on adding just the title attribute to the task table to keep things simple:

public function up(): void
{
    Schema::create('tasks', function (Blueprint $table) {
        $table->id();
        $table->string('title');
        $table->timestamps();
    });
}

In the code, you might have noticed two other helper functions called id and timestamps.

The id function creates an auto-incrementing index in our database. Meanwhile, the timestamps function creates two columns: created_at and updated_at.

The created_at column gets the value when you create the model, while the updated_at column gets the value when you update the model. Laravel handles this automatically, so you don't have to worry about it. Pretty cool, right?

Now, with php artisan migrate our table will be created in the database.

Awesome work! You’ve just completed one of the most important parts of Laravel — connecting to the database, creating a table, and a model connected to it.

If you want to see the database you created, and you’re using an DataGrip, you can simply open the database file and install the required driver if necessary. Alternatively, you can use other apps like SQLiteBrowser.

It’s worth noting that we’ve only created the tasks table here. The rest of the tables are included by default in every Laravel project. And by installing a package, you can also install the required tables for that package.

Now, let’s generate some data in the table we just created for testing. To do this, we can use an interesting tool called Tinker. Tinker is a live environment for executing PHP commands. To access it, simply run the following command:

php artisan tinker

Let’s see the first useful function inside eloquent models:

Great job! The all function displays all the records in the table. Since we didn't have any data in the table at this point, there was no output.

Next, let’s save a record to the table. To do this, we’ll create an object from the Task class, set the value of the title attribute we defined earlier in the model, and then call the save function to save the record to the table.

Now, if we execute the previous command again, we’ll see that our table is no longer empty!

As you noticed other attributes as I told you earlier are being handled by Eloquent.

Let’s go back to the controller we created earlier. We’ll define a function similar to the friends function we created in the previous tutorial. The only difference this time is that the data will be retrieved from the database.

class TaskController extends Controller
{
    public function index()
    {
        return view('home', ['tasks' => Task::all()]);
    }
}

If everything went smoothly, your output should look something like this.

Yeah, That’s it!

Now If you want to display only the title of each record, simply call it. To show what I mean I add some styles as you see in the following pic which only show the title of each record!

I just use a tailwind classes and use icons from this package you can simply install it with composer in your project. Also, you can to find out this series tutorial project here.

In this tutorial, you learned how to connect a database to your project and create a table in the database. You also learned how to connect a model with the table and use it. In the next tutorial, we’ll add records to the database through a form.