Use SQLite Database in Laravel project

A quick tutorial showcasing how to set up a local SQLite database for a Laravel / Lumen project within PHPStorm and VSCode.

1. Follow the laravel docs to set up a project.
2. Once the project is set up:
   • Create a file called database.sqlite under /database
   • within the .env file, comment out

# DB_CONNECTION=mysql
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=laravel
# DB_USERNAME=root
# DB_PASSWORD=

and add

DB_CONNECTION=sqlite
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=/home/doru/Documents/Work/oferte-ro/database/database.sqlite # Absolute Path - REMEMBER to create this file for local development

Please note that DB_DATABASE must be set to the absolute path to the database.sqlite file you have created earlier. In PHPStorm this can be done by right-clicking on the file and selecting Copy Path -> Absolute Path.

That’s it. Your project is now set up to use the SQLite database instead of the default MySQL one.

You may now run php artisan migrate to run your migrations (if you have got any).

In PHPStorm, you may connect to your database by:

• Opening the Data Sources and Drivers.

• In the left menu, under Drivers select SQLite and download the driver if required.
• When setting up this drivers, for the File click the three dots button and select the database.sqlite file you have just created.

• Click Test Connection. Everything should connect fine.
• Apply the changes and you’re ready to see your database.

If you are using a different IDE (code editor) than PHPStorm, you will still be able to see your database within the IDE or using a different apps.

For example, in VSCode you can install a plugin like SQLite to see your database.

HOW TO: Create Lumen API – Basic

Conclusion

Using SQLite for a project enables faster development in some cases. As you could see, setting it up only takes a couple of minutes and you get more time to focus on your application’s logic rather than on database set ups and configurations.

However, it is generally recommended to decide on the database your are going to use before starting development and stick with that throughout all your environments. This guaranties both consistency and security through your project and team. As you might be aware, SQLite is lighter than MySQL or PostgreSQL for a reason, the reason being that there are some key differences between them. These differences might affect the behaviour of your project and thus slow down development.