Schema migration with Neon Postgres and Laravel
Set up Neon Postgres and run migrations for your Laravel project
Laravel is a popular PHP web application framework that provides an expressive and elegant syntax for building web applications. It includes an ORM (Object-Relational Mapping) called Eloquent, which allows you to interact with databases using a fluent API. Laravel also provides a powerful migration system to manage database schema changes over time.
This guide demonstrates how to use Laravel with the Neon Postgres database. We'll create a simple Laravel application and walk through the process of setting up the database, defining models, and generating and running migrations to manage schema changes.
Prerequisites
To follow along with this guide, you will need:
- A Neon account. If you do not have one, sign up at Neon. Your Neon project comes with a ready-to-use Postgres database named
neondb
. We'll use this database in the following examples. - PHP installed on your local machine. This guide uses PHP 8.1, but you can use any recent version compatible with Laravel.
- Composer installed on your local machine for managing PHP dependencies.
Setting up your Neon database
Initialize a new project
- Log in to the Neon Console and navigate to the Projects section.
- Select a project or click the New Project button to create a new one.
Retrieve your Neon database connection string
On the Neon project dashboard, navigate to the Connection Details widget to find your database connection string. It should look similar to this:
note
Neon supports both direct and pooled database connection strings, which can be copied from the Connection Details widget on your Neon Project Dashboard. A pooled connection string connects your application to the database via a PgBouncer connection pool, allowing for a higher number of concurrent connections. However, using a pooled connection string for migrations can be prone to errors. For this reason, we recommend using a direct (non-pooled) connection when performing migrations. For more information about direct and pooled connections, see Connection pooling.
Keep your connection string handy for later use.
Setting up the Laravel project
Create a new Laravel project
Open your terminal and navigate to the directory where you want to create your Laravel project. Run the following command to create a new Laravel project:
This command creates a new Laravel project named guide-neon-laravel
in the current directory.
Set up the Database configuration
Open the .env
file in the project root directory and update the following database connection variables:
Replace NEON_POSTGRES_CONNECTION_STRING
with the connection string you retrieved from the Neon Console earlier. The DB_CONNECTION
should be set to pgsql
to indicate that we are using a Postgres database.
Defining data models and running migrations
Specify the data model
Data models are defined using the Elquent
ORM in Laravel. Our application is a simple catalog of authors and books, where each author can have multiple books. We'll create two models, Author
and Book
, to represent the data.
Create a new file Author.php
in the app/Models
directory with the following code:
Create another file Book.php
in the app/Models
directory with the following code:
The Author
model represents an author with fields for name and bio. The Book
model represents a book with fields for title and author (as a foreign key to the Author
model). Laravel automatically creates an id
field for each model as the primary key and manages the created_at
and updated_at
timestamps.
Generate migration files
To generate migration files for creating the authors
and books
tables, run the following commands in the terminal:
These commands generate empty migration files in the database/migrations
directory. Unlike frameworks such as Django, Laravel does not generate the schema automatically based on the model definitions. Instead, you define the schema in the migration files.
Open the create_authors_table
migration file and update the up
method to define the table schema:
Similarly, open the create_books_table
migration file and update the up
method:
Apply the migration
To apply the migration and create the corresponding tables in the Neon Postgres database, run the following command:
This command executes the migration files and creates the authors
and books
tables in the database.
Seed the database
To populate the database with some initial data, we use Laravel's database seeding feature. Open the file DatabaseSeeder.php
in the database/seeders
directory and replace its contents with the following code:
This seeder creates three authors and associates them with their corresponding books. To run this script and populate the database, run the following command in the terminal:
Implement the application
Create routes and controllers
We'll create two routes and corresponding controllers to display the authors and books in our application.
Open the routes/web.php
file and add the following routes:
We define two routes: /authors
to list all authors and /books/{author}
to list books by a specific author.
Now, create a new file AuthorController.php
in the app/Http/Controllers
directory with the following code:
Similarly, create another file BookController.php
in the app/Http/Controllers
directory with the following code:
These controllers define the index
action to retrieve all authors and books by a specific author, respectively. The data is returned as JSON responses.
Run the Laravel development server
To start the Laravel development server and test the application, run the following command:
Navigate to the url http://localhost:8000/authors
in your browser to view the list of authors. You can also view the books by a specific author by visiting http://localhost:8000/books/{author_id}
.
Applying schema changes
We will demonstrate how to handle schema changes by adding a new field country
to the Author
model, which will store the author's country of origin.
Update the data model
Open the Author.php
file in the app/Models
directory and add the country
field to the $fillable
property:
Generate and run the migration
To generate a new migration file for the schema change, run the following command:
This command generates a new migration file in the database/migrations
directory.
Open the generated migration file and update the up
method to add the new country
column:
Now, to apply the migration, run the following command:
Test the schema change
Restart the Laravel development server:
Navigate to the url http://localhost:8000/authors
to view the list of authors. Each author entry now includes the country
field set to null
, reflecting the schema change.
Conclusion
In this guide, we demonstrated how to set up a Laravel project with Neon
Postgres, define database models using Eloquent, generate migrations, and run them. Laravel's Eloquent ORM and migration system make it easy to interact with the database and manage schema evolution over time.
Source code
You can find the source code for the application described in this guide on GitHub.
Resources
For more information on the tools and concepts used in this guide, refer to the following resources:
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.