Building Netsoc Admin (#2) – Models and Relationships

Models aren’t just pretty runway strutters with capes draped over one shoulder and a small pomeranian in the other, they’re a vital part of the MVC methodology (it’s in the name after all). Models will be our main way of representing the underlying database and the relationships on top of that.

This is part 2 of a series on Building Netsoc Admin. If you haven’t already, you can read the previous parts over here.

Laravel ORM

Laravel provides a really nice interface for interacting with and exposing models (ooo la la). Laravel uses Eloquent ORM (Object-Relational Mapping) to handle its models and relationships. This will give us a nice way to add to, delete from, query and generally interact with the database. First thing’s first though, we need to architect our database and make sure we have something to interact with in the first place.

Database Migrations

All of our definitions and changes to the database will happen in sequential migrations. Working from scratch, on an empty development database, it may seem like a pain to use migrations but their main benefit comes as you build on top of your software in the future. Each migration script has a specific change and a method to entirely reverse it.

That way, our database changes are almost like version controlled commits. We can easily migrate (commit our change), rollback (reverse a change) and refresh (rollback all changes then migrate all of them again). In this blog post, we’re going to run a series of basic table creations to house all our data.

Making Migrations

To begin with, we’ll login to our vagrant box with vagrant ssh and then cd /var/www to get to the root of our project. To create a migration for a new table, we can run:

1

php artisan make:migrate create_users_table--create=users

If we wanted to just make a change to an already existing table, we can leave out the --create flag and it won’t include the create table script in the new file.

This creates a db-layer relationship between our databases and our users, I’ll explain a little more about relationships a little further down within the models so don’t worry too much about it right now.

Now, though, we have to consider what happens when we rollback a migration. For the first time, we’ve gotta replace the default down() function in order to drop the foreign key as well. It’s fairly straightforward, we simply put in the following:

PHP

47

48

49

50

51

52

53

54

55

publicfunctiondown()

{

Schema::table('mysql_users',function(Blueprint$table){

// There's a predefined format for how laravel names

// foreign key indexes

$table->dropForeign('mysql_databases_user_id_foreign');

});

Schema::drop('mysql_databases');

}

We’re leveraging the table schema to drop the foreign key and then drop the table afterwards.

MySQL Users

Creating mysql_users is the same as above so I’ll simply include the three code segments.

Models

Models are bridges between the database layer and our code. They give us a way to extrapolate relationships and query data in a very easy, object-y way. It’s much more readable as well which is a bonus. To create a user, you create a user object and then the model creates all the relevant information in the database as well so, effectively, we’re dealing with two distinct layers in one singular method.

User Model

I’m going to start off with the “hardest” model simply because it’s the core of our application – the user. Since it’s a User model, we’ll have to use certain contracts to provide Authentication and PasswordResets.

A contract, if you’re unfamiliar, is an agreement a class has to fulfil to ensure it can perform the necessary functions required.

Below is the initial code for the User model we’ll place in app/user.php.

However, the key thing we’re missing here is the relationship between our User model and the MySQL models I’ll be setting up next. We’ll return to this.

MySQLDatabase and MySQLUser

Generally, models are fairly simple. Your database’s column names go in the $fillable array and anything sensitive goes in $hidden. There’s a template for every model. Have a look at the similarities between our code for aPP/Mysqldatabase.php and app/MysqLuser.php below:

MySQLDatabase.php

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

<?php

namespaceApp;

useIlluminate\Database\Eloquent\Model;

classMySQLDatabaseextendsModel

{

/*

* The database table used by the model.

*/

protected$table='mysql_databases';

/*

* The attributes that are mass assignable.

*/

protected$fillable=['user_id','db_name'];

/*

* The attributes excluded from the model's JSON form.

*/

protected$hidden=[];

}

MySQLUser.php

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<?php

namespaceApp;

useIlluminate\Database\Eloquent\Model;

classMySQLUserextendsModel

{

/*

* The database table used by the model.

*/

protected$table='mysql_users';

/*

* The attributes that are mass assignable.

*/

protected$fillable=['user_id','username','password'];

/*

* The attributes excluded from the model's JSON form.

*/

protected$hidden=[];

}

Relationships

User <–> MySQLDatabase

Every user in our application will have a one-to-many relationship with MySQL databases. That is, every user may have 1 or more MySQL databases associated with them. As such, we’d like a way to use one to determine the other. We’ve already set up our foreign keys in the database, so all that’s left is representing the relationship in the Model.

As you can see from the diagram below, the user has a one-to-many relationship with the databases but the databases have a one-to-one relationship with the user. A user can have many databases but each database can only have one user.

Within the User model, we’ll add the following:

PHP

33

34

35

publicfunctiondatabases(){

return$this->hasMany('App\MySQLDatabase');

}

Now, whenever we want a user’s databases, we can use User::find(1)->databases and it’ll return an array of database objects associated with that user.

Conversely, to represent the other side of the relationship, we’ll add code to the MySQLDatabase model.

PHP

25

26

27

publicfunctionuser(){

return$this->hasOne('App\User');

}

Then we can get any databases’ user with Mysqldatabase::find(1)->user.

User <–> MySQLUser

Finally, the netsoc account user and the MySQL user have a one-to-one relationship both ways which make it much easier to understand. This is because we will be using one MySQLUser to give someone database access via management GUI (such as PHPMyAdmin). It allows us to limit their permissions and monitor access.