Connect a Laravel application to PlanetScale

Introduction

In this tutorial, you'll learn how to connect a Laravel application to a PlanetScale MySQL database using a sample Laravel starter app.

Prerequisites

• PHP — This tutorial uses v8.1
• Composer
• A free PlanetScale account

Set up the Laravel app

This guide will integrate a simple Laravel 9 app with PlanetScale. The application will display a list of stars and what constellation each star is in. The sample repo contains migrations and seed data to create and populate the constellations and stars tables. If you have an existing application, you can also use that.

1. Clone the starter Laravel application:

   git clone https://github.com/planetscale/laravel-example.git
   

2. Enter into the folder and install the dependencies:

   cd laravel-example
   composer install
   

   You may need to run composer update if you haven't updated in a while.

3. Copy the .env.example file into .env:

   cp .env.example .env
   

4. Start the application:

   php artisan serve
   

You can view the application at http://localhost:8000.

Set up the database

Next, you need to set up your PlanetScale database and connect to it in the Laravel application.

If this is your first time in the dashboard, you'll be prompted to go through a database creation walkthrough where you'll create a new database. Otherwise, click "New database" > "Create new database".

• Name — You can use any name with lowercase, alphanumeric characters, or underscores. We also permit dashes, but don't recommend them, as they may need to be escaped in some instances.
• Region — Choose the region closest to you or your application. It's important to note if you intend to make this branch a production branch, you will not be able to change the region later, so choose the region with this in mind.

Finally, click "Create database".

A development branch, main, is automatically created when you create your database. You can use this branch to develop on, and once you're happy with any schema changes, you can promote it to production, where it becomes a highly available, protected database that you can connect your production application to.

That's it! Your database is ready to use. Next, let's connect it to the Laravel application and then add some data.

Connect to the Laravel app

There are two ways to connect to PlanetScale:

• With an auto-generated username and password
• Using the PlanetScale proxy with the CLI

Both options are covered below.

Option 1: Connect with username and password (Recommended)

Next, you need to generate a database username and password so that you can use it to connect to your application.

In your PlanetScale dashboard, select your database, click "Connect", and select "Laravel" from the "Connect with" dropdown.

As long as you're an organization administrator, this will generate a username and password that has administrator privileges to the database.

Copy the contents of the .env tab and paste them into your own .env file in your Laravel application. The structure will look like this:

DB_CONNECTION=mysql
DB_HOST=<ACCESS HOST URL>
DB_PORT=3306
DB_DATABASE=<DATABASE_NAME>
DB_USERNAME=<USERNAME>
DB_PASSWORD=<PASSWORD>
MYSQL_ATTR_SSL_CA=/etc/ssl/cert.pem

The MYSQL_ATTR_SSL_CA value is platform dependent. Please see our documentation around how to connect to PlanetScale securely for the configuration for the platform you're using.

Refresh your Laravel homepage and you should see the message that you're connected to your database!

Option 2: Connect with the PlanetScale proxy

To connect with the PlanetScale proxy, you need to install and use the PlanetScale CLI.

1. Open a connection by running the following:

   pscale connect <DATABASE_NAME> <BRANCH_NAME>
   

   If you're following this guide exactly and haven't created any branches, you can use the default branch, main.

2. A secure connection to your database will be established and you'll see a local address you can use to connect to your application.

3. Open the .env file in your Laravel app and update it as follows:

   DB_CONNECTION=mysql
   DB_HOST=127.0.0.1
   DB_PORT=3306 # Get this from the output of the previous step
   DB_DATABASE=<DATABASE_NAME>
   DB_USERNAME=
   DB_PASSWORD=
   

   The connection uses port 3306 by default, but if that's being used, it will pick a random port. Make sure you paste in whatever port is returned in the terminal. You can leave DB_USERNAME and DB_PASSWORD blank.

Refresh your Laravel homepage and you should see the message that you're connected to your database!

Run migrations and seeder

Now that you're connected, let's add some data to see it in action. The sample application comes with two migration files:

• database/migrations/2021_12_20_194637_create_stars_table.php — Creates a stars table
• database/migrations/2022_07_26_190656_create_constellations_table.php — Creates a constellations table

You can use the foreignId() method to create a relationship between the constellations and stars tables, but you cannot enforce referential integrity with the constrained() method.

For more information, check out our Operating without foreign key constraints documentation.

There are also two seeders, database/seeders/ConstellationSeeder.php and database/seeders/StarSeeder.php, that will add two rows to the each table. Let's run those now.

1. Make sure your database connection has been established. You'll see the message "You are connected to your_database_name" on the Laravel app homepage if everything is configured properly.

2. In your terminal in the root of the Laravel project, run the following to run the migration:

   php artisan migrate
   

   You should get a message that the migration table was successfully created.

3. Next, seed the database by running:

   php artisan db:seed
   

   You should get the message "Database seeding completed successfully".

4. Refresh your Laravel homepage and you'll see a list of stars and their constellations printed out.

The resources/views/home.blade.php file pulls this data from the stars table with the help of the app/Http/Controllers/StarController.php file.

Add data manually

If you want to continue to play around with adding data on the fly, you have a few options:

• PlanetScale dashboard console
• Laravel Tinker
• PlanetScale CLI shell
• Your favorite MySQL client (for a list of tested MySQL clients, review our article on how to connect MySQL GUI applications)

The first two options are covered below.

Add data in PlanetScale dashboard console

PlanetScale has a built-in console where you can run MySQL commands against your branches. To access it, click "Branches" > your development branch > "Console".

From here, you can run MySQL queries and DDL against your database branch.

1. Add a record to the constellations table:

   INSERT INTO `constellations` (name)
   VALUES  ('Kaus Media');
   

2. Add a record to the stars table:

   INSERT INTO `stars` (name, constellation_id)
   VALUES  ('Sagittarius', 3);
   

3. Refresh the Laravel homepage to see the new record. You can also verify it was added in the console with:

   SELECT * FROM stars;
   

Add data with Laravel Tinker

Laravel comes with a powerful tool called Tinker that lets you interact with your database from the command line. Let's add some data with it.

1. In your terminal, run the following command:

   php artisan tinker
   

2. Insert a new record into the constellations table with:

   DB::table('constellations')->insert(['name'=>'Kaus Media']);
   

3. Insert a new record into the stars table with:

   DB::table('stars')->insert(['name'=>'Sagitarrius', 'constellation'=>3]);
   

4. Refresh your Laravel application homepage to see your new data. You can also run the following command in Tinker to see all records in the stars table.

   App\Models\Star::all();
   

5. Type exit to exit Tinker.

What's next?

Once you're done with development, you can promote your main branch to production to get a highly available branch protected by direct schema changes.

To learn more about PlanetScale, take a look at the following resources:

• PlanetScale workflow — Quick overview of the PlanetScale workflow: branching, non-blocking schema changes, deploy requests, and reverting a schema change.
• PlanetScale branching — Learn how to utilize branching to ship schema changes with no locking or downtime.
• PlanetScale CLI — Power up your workflow with the PlanetScale CLI. Every single action you just performed in this quickstart (and much more) can also be done with the CLI.