Build an expense tracker app with Symfony and MySQL
Learn how to build a use MySQL with Symfony by building an expense tracking application using a PlanetScale MySQL database.
In this tutorial, you will learn how to build an expense tracker app with Symfony — a high-performance, open-source PHP framework that can help developers build robust and scalable web applications. You will use PlanetScale for the database. PlanetScale is a MySQL-based database platform that not only simplifies the development of databases, but also goes to great lengths to provide scalability and hassle-free migrations.
In this article, you'll use the PlanetScale CLI to interact with the database, so be sure to have that installed before proceeding. You can find the instructions relevant to your operating system in the PlanetScale environment setup documentation.
Finally, you will use the Symfony Local Web Server to run your applications. To do this, you will need to have the Symfony CLI installed. Ensure that you have preferably PHP version 8.1 or above installed.
Set up the database
If you haven't already done so, create a PlanetScale account now. Next, authenticate to the CLI using the following command:
pscale auth login
A confirmation page will be opened in your browser. Confirm that the displayed code matches the one displayed in your terminal by clicking Confirm code.
Next, create a new database named expense-tracker using the following command.
pscale db create expense-tracker
Before generating database credentials, let’s explore another interesting aspect of PlanetScale, which is branching and promotion. Branching allows you to create various branches of your database, just as you would on a code repository. This serves the same benefit of isolating your changes and minimizing interference (with team members) while you modify the database schema.
Create a new branch named dev for your database using the following command:
pscale branch create expense-tracker dev
You can view the new database in your PlanetScale dashboard.
Next, promote your main branch to production using the following command:
pscale branch promote expense-tracker main
Promoting a database to production makes it highly available. Additionally, if you have safe migrations enabled, you cannot run direct DDL on your production branch. This is an additional protection that PlanetScale offers to help prevent mistakes on your production database.
Finally, set up database credentials for the dev branch using the following command:
pscale password create expense-tracker dev symfony-app
A table with the following values will be displayed once the process is completed:
NAME BRANCH ID USERNAME ACCESS HOST URL ROLE ROLE DESCRIPTION PASSWORD
Make a note of these values as they are only shown once. For the Symfony application, you will need USERNAME, PASSWORD, and ACCESS HOST URL.
Set up the Symfony application
Create a new project using the following command:
composer create-project symfony/skeleton expense_tracker_app
Next, navigate into the project’s folder and add the dependencies we’ll need for the database, template rendering, and form management.
cd expense_tracker_app
composer require doctrine form twig validator
This article does not involve the use of Docker so answer no to the prompt for including Docker configuration.
Connect the Symfony app to your MySQL database
Create a local environment variable. This prevents you from accidentally leaking application secrets in version control. You can do this with the following command:
cp .env .env.local
Next, open the newly created .env.local file and add the following values:
DATABASE_URL='mysql://<USERNAME>:<PASSWORD>@<ACCESS_HOST_URL>:3306/expense-tracker?ssl-mode=required&serverVersion=8.0'
MYSQL_ATTR_SSL_CA=/etc/ssl/cert.pem
Be sure to delete the previous value for DATABASE_URL. Also, replace the placeholder USERNAME, PASSWORD and ACCESS_HOST_URL with the appropriate values as obtained from PlanetScale.
The value for MYSQL_ATTR_SSL_CA will depend on your system. You can find more information on our Secure Connections page.
Next, make the path to your system CA available to doctrine. Open config/packages/doctrine.yaml, and add the following to the dbal configuration:
options: !php/const:PDO::MYSQL_ATTR_SSL_CA: '%env(resolve:MYSQL_ATTR_SSL_CA)%'
It should be similar to this:
doctrine:
dbal:
url: '%env(resolve:DATABASE_URL)%'
options: !php/const:PDO::MYSQL_ATTR_SSL_CA: '%env(resolve:MYSQL_ATTR_SSL_CA)%'
Add dev dependencies
To simplify the development process, add some packages that make it easy to create the sub-components of your application (entities, fixtures, forms, etc). Do this with the following command:
composer require --dev maker orm-fixtures
Add entities
With the database connection in place, add the entities for the application. This application will have two entities — ExpenseCategory and Expense. Start by creating ExpenseCategory.
php bin/console make:entity ExpenseCategory
Press the Enter key to skip adding properties via the CLI.
Open src/Entity/ExpenseCategory.php and update its content to match the following:
<?php
namespace App\Entity;
use App\Repository\ExpenseCategoryRepository;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity(repositoryClass: ExpenseCategoryRepository::class)]
class ExpenseCategory {
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private ?int $id = null;
#[ORM\Column]
private string $name;
public function __construct(string $name) {
$this->name = $name;
}
public function getId()
: ?int {
return $this->id;
}
public function getName()
: string {
return $this->name;
}
}
Next, open src/Repository/ExpenseCategoryRepository.php and add the following function inside the ExpenseCategoryRepository class:
public function sortedCategories(): QueryBuilder {
return $this->createQueryBuilder('e')->orderBy('e.name', 'ASC');
}
This function will be used to return categories in ascending order when the dropdown is created in the Add expense form.
Don’t forget to import the QueryBuilder class.
use Doctrine\ORM\QueryBuilder;
Next, create the Expense entity using the following command:
php bin/console make:entity Expense
Press the Enter key to skip adding properties via the CLI.
Next, open src/Entity/Expense.php and update the code to match the following.
<?php
namespace App\Entity;
use App\Repository\ExpenseRepository;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity(repositoryClass: ExpenseRepository::class)]
class Expense {
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private ?int $id = null;
#[ORM\ManyToOne(targetEntity: ExpenseCategory::class)]
#[ORM\JoinColumn(nullable: true)]
private ExpenseCategory $category;
#[ORM\Column]
private float $amount;
public function getCategory()
: ExpenseCategory {
return $this->category;
}
public function setCategory(ExpenseCategory $category)
: void {
$this->category = $category;
}
public function getAmount()
: float {
return $this->amount;
}
public function setAmount(float $amount)
: void {
$this->amount = $amount;
}
}
Next, add the following function to src/Repository/ExpenseRepository.php:
public function groupedExpenses(): array {
return $this->createQueryBuilder('expense')
->select('category.name, sum(expense.amount) as amount')
->join('expense.category', 'category')
->groupBy('category')
->orderBy('amount', 'DESC')
->getQuery()
->getArrayResult();
}
This function is used to get the total expenses per category as an array from the database. The information will be rendered in a chart on the index page.
Update database schema
PlanetScale now supports foreign key constraints. Head to your database settings page to enable foreign key constraint support for the database you are using in this tutorial before running the database migration command.
Next, create a database migration using the following command:
php bin/console make:migration
This command creates a migration file containing the SQL commands which need to be executed for the created migration. The migration filename is a timestamp (the creation time of the file) prepended by Version.
Provided that your PlanetScale credentials are correct, this will create 3 tables in the dev branch of your database:
Add seed data
You’ll want the application to be seeded with default expense categories. To do this, create a fixture using the following command.
php bin/console make:fixture ExpenseCategoryFixtures
Open the newly created src/DataFixtures/ExpenseCategoryFixtures.php file and update the code to match the following.
<?php
namespace App\DataFixtures;
use App\Entity\ExpenseCategory;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;
class ExpenseCategoryFixtures extends Fixture {
public function load(ObjectManager $manager): void {
$names = [
'Emergency',
'Entertainment',
'Essential',
'Family',
'Feeding',
'Utility',
];
foreach ($names as $name) {
$manager->persist(new ExpenseCategory($name));
}
$manager->flush();
}
}
Next, load the fixtures using the following command.
php bin/console doctrine:fixtures:load -n
You can confirm that your fixtures were loaded successfully using the following command.
php bin/console dbal:run-sql "select * from expense_category;"
You will see the following output:
---- ---------------
id name
---- ---------------
1 Emergency
2 Entertainment
3 Essential
4 Family
5 Feeding
6 Utility
---- ---------------
Create a Symfony form for adding a new expense
Symfony forms will be used to generate the form for adding expenses. Create one using the following command:
php bin/console make:form ExpenseType Expense
Update the newly created src/Form/ExpenseType.php file to match the following:
<?php
namespace App\Form;
use App\Entity\Expense;
use App\Entity\ExpenseCategory;
use App\Repository\ExpenseCategoryRepository;
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\SubmitType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
class ExpenseType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options): void
{
$builder->add('amount')
->add('category', EntityType::class, [
'class' => ExpenseCategory::class,
'choice_value' => 'id',
'choice_label' => fn(ExpenseCategory $category) => $category->getName(),
'query_builder' => fn(ExpenseCategoryRepository $repository) => $repository->sortedCategories(),
])
->add('save', SubmitType::class);
}
public function configureOptions(OptionsResolver $resolver): void
{
$resolver->setDefaults([
'data_class' => Expense::class,
]);
}
}
Create a controller for expense-related actions
Next, create a controller to handle expense-related actions using the following command.
php bin/console make:controller Expense
Open the newly created src/Controller/ExpenseController.php file and update the code as follows:
<?php
namespace App\Controller;
use App\Entity\Expense;
use App\Form\ExpenseType;
use App\Repository\ExpenseRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
#[Route('/expense/', name: 'app_expense_')]
class ExpenseController extends AbstractController {
#[Route('', name: 'index', methods: ['GET'])]
public function index(ExpenseRepository $expenseRepository)
: Response {
return $this->render('expense/index.html.twig', [
'expenses' => $expenseRepository->groupedExpenses(),
]);
}
#[Route('new', name: 'new', methods: ['GET', 'POST'])]
public function new(Request $request, ExpenseRepository $expenseRepository)
: Response {
$form = $this->createForm(ExpenseType::class, new Expense);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$expense = $form->getData();
$expenseRepository->save($expense, true);
return $this->redirectToRoute('app_expense_index');
}
return $this->render('expense/new.html.twig', ['form' => $form]);
}
#[Route('all', name: 'all', methods: ['GET'])]
public function all(ExpenseRepository $expenseRepository)
: Response {
return $this->render('expense/all.html.twig', [
'expenses' => $expenseRepository->findAll(),
]);
}
}
The index function retrieves the grouped expenses from the ExpenseRepository and renders the response using the expense/index.html.twig template.
The new function is used to render the expense form and handle the form submission. When a form containing valid information is submitted, a new Expense is created and saved to the database after which the user is redirected to the index page.
The all function retrieves all the expenses in the database and renders the response using the expense/all.html.twig template.
Add configuration for index route
Optionally, you can render the grouped expenses for the index page (which at the moment renders the default Symfony page). Do this by adding the following key to config/routes.yaml
app_index:
path: /
controller: App\Controller\ExpenseController::index
methods: GET
Configuring the views
At this point, you have a functional application although the user can’t see/interact with anything yet. The next step is to add/update the views for the application. Bulma will be used to style the application while the grouped expenses will be rendered as a pie chart using Chart.js.
Update base template
Start with the base template. Open templates/base.html.twig and update the content to match the following.
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>{% block title %}Expense tracker{% endblock %}</title>
<link rel="icon" href="https://planetscale.com/favicon.svg" />
<link rel="icon" href="https://planetscale.com/favicon.ico" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.9.4/css/bulma.min.css" />
{% block stylesheets %} {{ encore_entry_link_tags('app') }} {% endblock %} {% block javascripts %} {{
encore_entry_script_tags('app') }} {% endblock %}
</head>
<body>
<div style="margin: 2%">{% include 'navigation.html.twig' %} {% block body %}{% endblock %}</div>
</body>
</html>
Next, add the navigation template. Create a new file named navigation.html.twig in the templates folder and add the following code to it.
<nav class="navbar" role="navigation" aria-label="main navigation">
<div id="navbarBasicExample" class="navbar-menu">
<div class="navbar-start">
<a class="navbar-item" href="{{ path('app_index') }}"> Home </a>
<div class="navbar-item has-dropdown is-hoverable">
<a class="navbar-link"> Expenses </a>
<div class="navbar-dropdown">
<a class="navbar-item" href="{{ path('app_expense_new') }}"> Create </a>
<a class="navbar-item" href="{{ path('app_expense_all') }}"> View all </a>
</div>
</div>
</div>
</div>
</nav>
Update index view
Next, update templates/expense/index.html.twig to match the following:
{% extends 'base.html.twig' %} {% block title %}Expense Tracker{% endblock %} {% block body %}
<div style="width: 700px; margin: auto">
<canvas id="expenseChart"></canvas>
</div>
<script>
const context = document.getElementById('expenseChart');
new Chart(context, {
type: 'doughnut',
data: {
labels: {{ expenses|column('name')|json_encode|raw }},
datasets: [{
label: 'Amount',
data: {{ expenses|column('amount')|json_encode|raw }},
borderWidth: 1,
hoverOffset: 4
}]
}
});
</script>
{% endblock %} {% block javascripts %}
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
{% endblock %}
Add view for new expense form
In the templates/expense folder, create a new file named new.html.twig and add the following code to it.
{% extends 'base.html.twig' %} {% block title %}Add New Expense{% endblock %} {% block body %}
<div style="width:60%; margin:auto">
<h1 class="title">Add New Expense</h1>
{% form_theme form 'expense/errors.html.twig' %} {{ form_start(form) }}
<div class="field is-horizontal">
<div class="field-label">{{ form_label(form.amount, null, {'label_attr': {'class': 'label'}}) }}</div>
<div class="field-body">
<div class="field">
<div class="control">{{ form_widget(form.amount, {'attr': {'class': 'input'}}) }}</div>
</div>
{{ form_errors(form.amount ) }}
</div>
</div>
<div class="field is-horizontal">
<div class="field-label">{{ form_label(form.category, null, {'label_attr': {'class': 'label'}}) }}</div>
<div class="field-body">
<div class="field">
<div class="control">
<div class="select">{{ form_widget(form.category) }}</div>
</div>
</div>
</div>
</div>
<div class="field is-horizontal">
<div class="field-label">
<!-- Left empty for spacing -->
</div>
<div class="field-body">
<div class="field">
<div class="control">{{ form_widget(form.save, {'attr': {'class': 'button is-info'}}) }}</div>
</div>
</div>
</div>
{{ form_end(form) }}
</div>
{% endblock %}
Here, we render the form widgets for the ExpenseType individually and specify appropriate Bulma classes to make for a more aesthetic form. However, this template requires some additional theming for the errors which will be rendered in the form. To handle this, create a new file named errors.html.twig In the templates/expense and add the following code to it.
{% block form_errors %} {% if errors.count > 0 %}
<p class="help is-danger">{{ errors|first.message }}</p>
{% endif %} {% endblock %}
Add a view to show all expenses
In the templates/expense folder, create a new file named all.html.twig and add the following code to it.
{% extends 'base.html.twig' %} {% block title %}All Expenses{% endblock %} {% block body %}
<div style="width:60%; margin:auto">
<h1 class="title">All Expenses</h1>
<table class="is-bordered is-striped is-narrow is-hoverable is-fullwidth table">
<thead>
<tr class="has-background-primary-light">
<th>S/N</th>
<th>Amount</th>
<th>Category</th>
</tr>
</thead>
<tfoot>
<tr class="has-background-primary-light">
<th>S/N</th>
<th>Amount</th>
<th>Category</th>
</tr>
</tfoot>
<tbody>
{% for expense in expenses %}
<tr>
<td>{{ loop.index }}</td>
<td><b>{{ expense.amount | number_format(2, '.', ',') }}</b></td>
<td>{{ expense.category.name }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
{% endblock %}
Run your Symfony application
Serve your application using the following command:
symfony serve
By default, your application will run on port 8000. Open http://127.0.0.1:8000/ in your browser to start adding expenses and reviewing your guilty pleasures.
Creating an expense:
Review the list of expenses:
And finally, the chart for your expenses:
And just like that, you have a fully functional Symfony app powered by PlanetScale. The entire codebase is available on GitHub. Happy Coding!