Working with data in web frameworks

At AlgoTech Solutions, we are always interested in modern technologies and what makes them unique. Previously we’ve covered why converting to another web framework is not such a difficult task. However, the previous article only scraped the tip of the iceberg, covering topics such as installing and bootstrapping a project in Symfony and Django and some basics of route generation, handling and templating. But it’s not a web application yet. For a proper web application, we need to delve into the data models and how they are used to manipulate the underlying databases. So let’s see how that goes.

Connecting to a database

Modern web frameworks use a single connection to the database, which is injected wherever the developer needs to use it. The configuration is usually straightforward, by defining the database driver, name, password (and sometimes host and port, in case you’re not using the defaults). As covered before, web frameworks usually have one or more settings files where you can specify these details. In our example, we will hook both the Symfony app and the Django app to mysql databases running with default parameters (host 127.0.0.1, port 3306, user “root” without a password).
In Symfony, we edit the parameters file in app/config/parameters.yml  and add the database parameters as following:

parameters:
    database_driver: pdo_mysql
    database_host: 127.0.0.1
    database_port: null
    database_name: jobeet
    database_user: root
    database_password: null

By running the comand php app/console doctrine:database:create , you can directly create an empty database called jobeet  from your Symfony project.
In Django, use the settings file corresponding to your app and add the connection details there. In our case, the app name is jobeet_py , so the settings file is in jobeet_py/settings.py. We can define more database connections, but for our app a single one will do. We call it the default connection. Besides the Python syntax as opposed to Symfony’s YAML, there is also a semantic difference, with “drivers” being called “engines”.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'jobeet_2',
        'USER': 'root'
    }
}

Another difference between the two is that, in Django, the database can not be created through a Django command, but only from MySQL separately. Run the following:

mysql -uroot
mysql> CREATE schema jobeet_2;

and exit the MySQL console using Ctrl+D.
Now our empty database schemas were created and our frameworks are properly connected to an underlying database. But surely we will not run raw SQL queries on these databases, since we can use the powerful modelling tools that web frameworks provide, which hydrate our database rows into objects. This is called an Object Relational Mapper (ORM). ORMs are found in almost every Object-oriented programming language and web framework, which means if you have any experience with Hibernate (Java), ActiveRecord (Ruby) or any other ORM, you will find it easy to understand data modelling in any framework. For the following part of this article I will assume you are familiar with Object Modelling and all its intricacies such as types, abstraction, encapsulation, database relationship, keys etc..

Models

Now that we have a running connection to a database, we can start designing our models. The correspondence between objects in our application and rows in our database tables will be based on specific rules. Each class corresponds to a table in our database, while fields usually correspond to the properties (even if the string representing the column name and the one representing the property do sometimes suffer some transformations to make them compliant to coding standards in each programming language).

Models in Symfony

In the Symfony web framework, we can define our models in various ways, the most common being:

• annotated PHP models
• separate PHP and YAML models

While previous versions of Symfony favoured the former, the recommended approach is now to have a YAML file to define the model, which will later be transferred to the database. In addition to that, a PHP model represents the object itself. You might think that there is a lot of mindless code to be written, with getters, setters and property names, but don’t worry. You will see that there are automatic generators for parts of this code.
So, let’s start by creating our YML models. In /src/Ens/JobeetBundle/Resources/config/doctrine/ , define yml files related to each of your models. For example, in Category.orm.yml  we will write:

Ens\JobeetBundle\Entity\Category:
  type: entity
  table: category
  id:
    id:
      type: integer
      generator: { strategy: AUTO }
  fields:
    name:
      type: string
      length: 255
      unique: true
  oneToMany:
    jobs:
      targetEntity: Job
      mappedBy: category
    category_affiliates:
      targetEntity: CategoryAffiliate
      mappedBy: category

The first line represents the namespace of the PHP model we will eventually generate. The table key defines the name of the database table where the objects will be stored. We create an auto-generating id, specific fields related to our object and details about relationships with other objects. In Symfony, we need to define both ends of a relationship between two models, so that means in  Job.orm.yml we will have a portion related to categories:

Ens\JobeetBundle\Entity\Job:
  [...]
  manyToOne:
    category:
      targetEntity: Category
      inversedBy: jobs
      joinColumn:
        name: category_id
        referencedColumnName: id
  [...]

For the sake of space in this article, I will not include all the models defined, but you can find them on my github page and the Jobeet Day 3 tutorial. After adding all your YML entities, the next command will create the PHP models automatically, but you can add custom handling which will not be overwritten next time the command runs.

php app/console doctrine:generate:entities EnsJobeetBundle

To reflect the changes in your app in the database as well, run:

php app/console doctrine:schema:update --force

Models in Django

It is somewhat simpler to create the models in Django, since Python’s lack of getters and setters makes for more concise models, which can be located in a single file. In models.py, add the model classes separated by two blank lines. For example, the Category class will look like this:

class Category(models.Model):
    name = models.CharField(max_length=255)

In Django, we need not define the relationships at both ends. This means that the one-to-many relationship between Categories and Jobs can be defined simply by adding a ForeignKey in the Job class:

class Job(models.Model):
    [...]
    category = models.ForeignKey(Category, related_name='jobs')

You can find the complete models.py  file for this article on my github page. To reflect the changes in your database, you need to run an initial migration, by running the following commands:

python manage.py makemigrations
python manage.py migrate

If these commands get you into any trouble, it might be that you don’t have the mysql driver installed, so run pip install pymysql  if you get any errors.
Remember models are highly customisable and you can enhance them by adding signal-based logging, finite state machines for complex transitions and many more useful functionalities to achieve your project’s goal.
Now, you might ask why Symfony updates the models directly (by considering the differences between the existing database and the current models) and Django forces you to use migrations (by checking in the database which migration was run last and running only the subsequent migrations). The answer is complicated. In fact, you should never use the schema update functionality form Symfony in production, but it’s more suitable for a tutorial. In production, Symfony also has migration utilities which can be integrated as libraries. Django forces you to take the moral high ground from the start, even if it is more difficult for beginners to understand migrations. We have previously covered all about using migrations, so make sure you read our article in case your feelings are “clouded” in this debate.

Fixtures

Fixtures are ways to add test data to development environments. We will add some data to our database using Symfony’s Doctrine Fixtures package and Django’s inbuilt fixtures loading functionality.
In Symfony, we first install the package that handles fixtures:

composer require --dev doctrine/doctrine-fixtures-bundle

And we create the fixtures as explained in the same Jobeet Day 3 tutorial. You can also find the complete php fixtures in this folder on my github. I will not go into more depth about fixtures since the Jobeet tutorial does a great job at explaining them and the concept is straightforward over other frameworks, such as Django, as well.
In Django we will create a JSON or YML file with the fixtures and load them using a special command. In jobeet_py/jobeet/fixtures/load_data.json :

[
  {
    "model": "jobeet.Category",
    "pk": 1,
    "fields": {"name": "Design"}
  },
  [...]
]

(full fixtures file on my github page). After adding and editing your fixtures file containing all the objects and their properties, run:
python manage.py loaddata load_data.json
Congratulations! You now have some test data in your database. Keep in mind never to use these commands in a production environment, since it may delete all the “real” data in the system.

CRUD

Now, for the last part of this article, let’s do something interesting with our models. Up until now, we just defined some models, echoed our progress to a database, and added some test data, but now let’s try to add, edit and remove some objects from the apps themselves.
Symfony is a godsent this time. It features a command for generating the entire CRUD process for an entity. Run the command:

php app/console doctrine:generate:crud --entity=EnsJobeetBundle:Job --route-prefix=ens_job --with-write --format=yml

This will automatically generate a JobController which handles CRUD, routes in the src/Ens/JobeetBundle/Recources/config/routing/job.yml file, and the corresponding templates. This means that just by importing the newly-created routes to our main routing file, clearing the cache, and running the application, we will be able to see a full CRUD at http://localhost/job/ . For filling the Category drop-downs, we also need to define the __toString()  method on the Category object to return the name property. So, in src/Ens/JobeetBundle/Resources/config/routing.yml:

EnsJobeetBundle_job:
    resource: "@EnsJobeetBundle/Resources/config/routing/job.yml"
    prefix: /job
[...]

Then run:

php app/console cache:clear --env=prod
php app/console cache:clear --env=dev
php app/console server:run

And in your browser you can admire your work. Try adding, deleting and editing jobs, and all your changes will hold.
In Django, there is no CRUD autogeneration command, but it does provide some cool generic functions which help a lot. As you have sen, url generation is custom in the PHP version as well, so we will write in our jobeet_py/jobeet_urls.py  file:

[...]
url(r'^job/list$', views.JobList.as_view(), name='job_list'),
url(r'^job/new$', views.JobCreate.as_view(), name='job_new'),
url(r'^job/edit/(?P<pk>\d+)$', views.JobUpdate.as_view(), name='job_edit'),
url(
    r'^job/delete/(?P<pk>\d+)$',
    views.JobDelete.as_view(),
    name='job_delete'
),
[...]

Next we make use of generic views which handle CRUD without any extra logic. In views.py :

from django.shortcuts import render
from django.views.generic import ListView
from django.views.generic.edit import CreateView, UpdateView, DeleteView
from django.core.urlresolvers import reverse_lazy
from jobeet.models import Job
[...]
class JobList(ListView):
    model = Job
class JobCreate(CreateView):
    model = Job
    success_url = reverse_lazy('job_list')
class JobUpdate(UpdateView):
    model = Job
    success_url = reverse_lazy('job_list')
class JobDelete(DeleteView):
    model = Job
    success_url = reverse_lazy('job_list')

By inheriting the List, Create, Update and DeleteView, each of our CRUD classes only needs to define the model on which the operation is performed (and sometimes, the route name for the success of the operation), and the classes themselves will know what to do internally. Next we create the similar HTML files for each of these actions. You may think that, compared to the Symfony method, this is where you spend most time coding. However, it is an unfair comparison at this point, since usually such views are customised anyway, so they will neeed more work in both frameworks. For now, you can copy-paste them from my github page. Run the Django server (python manage.py runserver ) and admire your work on http://127.0.0.1/job/list . It looks just like the Symfony experiment we did before.
Remember that the examples presented illustrate local environments. For details on deployment and server choices, you can check out our other articles: “How powerful are AWS t2 instances?”, “How to keep PHP Background Jobs alive” and Oana’s article on why Django migrations are a must on production servers and in the codebase.

Conclusion

While these two frameworks may see more different from the data point of view, the basic concepts remain the same. To recap, we first defined our database connection parameters (in a fairly similar manner), we modelled our objects using modelling best practices and we imprinted the design on the database itself. Here, we investigated two methods for updating the database structure: with direct differential update and migrations. We then added some test data to our databases using fixtures, and generated the CRUD routes, actions and views for the Job objects.
What do you think? Do you have any experience with database and/or object modeling? Which database structure update method do you prefer in your development environments? If you have any improvements, suggestions or ideas for follow-up articles, let us know in the comments section.