In this lesson, we are going to create column definitions in our migration and model files for our Contact model. Thereafter, we will create a new route and add logic into the controller for create new contacts. We will use Postman for sending the request. In the last lesson, we have created three files for the Contact model:

database\migrations\xxxxxxxxxxx_contacts.ts
app\Controllers\Http\ContactsController.ts
app\Models\Contact.ts

We will be working on these three files. In additional to the api/start/routes.ts file.

Let's start by creating a new branch for our repo:

# Make sure you are within your project
git checkout -b 12-create-column-definitions-for-contact-model

Adding Column Definitions in the app\Models\Contact.ts File

Open app\Models\Contact.ts. Refer to this snapshot of the file. I will encourage you to type out the content of the snapshot into the app\Models\Contact.ts file. Alternatively, copy and paste the content of the snapshot file into the app\Models\Contact.ts file. Let's discuss what's going on in the file.

1. At Line 3, we import the cuid function from the @ioc:Adonis/Core/Helpers package. The function will be used for creating unique, collision-resistant IDs for our contacts.
2. At Line 6, we add a static property selfAssignPrimaryKey and assign a value of true to it. The selfAssignPrimaryKey informs the model that we want to generate our own primary key per contact instead of the default auto-increment ID from the database engine.

3. The rest of the file contains instance properties representing the columns for our contact. You will notice that each property as a notation like @column() above them. This is called a decorator in JavaScript. A decorator is a special function used to modify the behaviour of properties/classes/functions it is attached to. In this case, we are informing the model that the properties decorated with @column() are columns for our model. AdonisJS will inject special attributes to the properties via the decorator.

   At Line 8, the @column() decorator takes an object { isPrimary: true } as argument. The object specifies that the id column will be considered the primary key for the model.

4. Towards the end of the file, at Lines 63 and 66, we attach the @column.dateTime() decorator which transforms the property into a dateTime column. The @column.dateTime() decorator can take an object { autoCreate: true, autoUpdate: true } which specifies if the values of the columns should be auto-created when inserted for the first time and auto-updated whenever the record is updated. The @column.dateTime() decorator, very importantly, ensures that values assigned to those columns are transformed into JavaScript date types before persistence in the database. Also, when they are retrieved, they will be transformed into Luxon's DateTime instances. Read more about Luxon.

5. At Line 69, we declare a static method generateUUID which generates a random id and assigns the value to the id property before the contact is created. The @beforeCreate decorator is used to run the generateUUID static method only when the contact is being created.

Read more about decorators in AdonisJS.

Save the file.

Adding Column Definitions in the Contact Migration File

The column definitions in the migration files are the ones actually used to create columns within tables in our database.

Open the contact migraton file database\migrations\xxxxxxxxxxxxx_contacts.ts. Refer to this snapshot of the file. I will encourage you to type out the content of the snapshot into your database\migrations\xxxxxxxxxxxxx_contacts.ts file. Alternatively, copy and paste the content of the snapshot file into the database\migrations\xxxxxxxxxxxxx_contacts.ts file. Let's discuss more about the migration file.

1. At Line 4, we declare the property tableName and assign the value contacts. This will be the name of the table for the contact model.
2. Within the handler function of the this.schema.createTable function, we add more column definitions to the file. You will notice that we've change the column type of the id column from increment to string. This is because we want to manually generate the id instead of using auto-increment values. We assign extra modifiers to the id column such as primary(), index(), unique(), and notNullable().
3. You will notice that the column name are in snake case (for example: job_title and phone_number1). However, the corresponding property for the column in the model file is in camel case (e.g. jobTitle and phoneNumber1). This is because, the recommended case for naming columns in SQL is the snake case. AdonisJS has a naming strategy which will convert the camel case of model properties into snake case of our table columns. You will also notice that there is no need to separate numbers at the end of column names with underscores (_). That is, phone_number1 instead of phone_number_1.
4. The index() modifier is used to index the values of the columns it is attached to. Indexing speeds up finding of rows and running of searches on the columns they are assigned to. Basically, all columns with the index() modifiers will allow searching in the future. Read more about indexes here.
5. The unique() modifier forces the column to hold only unique values. Values cannot repeat within the column. This is obvious reason it is only attached to the id column.
6. Columns not assigned the notNullable() modifier are nullable by default.
7. You will notice that the notes column has the type text. This allows for very long texts to be entered for the note column.
8. The timestamp type is used to define the created_at and updated_at columns. This creates accurate timestamps of when each row in the table was created or modified. The timestamp method takes a second argument which is used to specify if timezone offset should be added to the timestamp. For example: instead of the timestamp to be store as "2021-10-01T22:08:30.000", it will be store as "2021-10-01T22:08:30.000+01:00" The timezone offset make it easy for the timestamp to be converted to the timezone of the user when it is rendered on the frontend. It is important to use timestamp data type to create timestamps instead of the datetime type. timestamp columns have more functionalities than datetime column especially automatic timezone conversion. Read more that datetime and timezone data types here.

Read more about data types in MySQL.

Save the file.

Let's migrate our database so that contacts table is created with the defined columns.

Make sure that you cross-check the syntaxes and name of the columns before migration to avoid errors.

# Ensure that you are in the `api` directory
node ace migration:run

This will migrate the database and create the contacts table with the columns which were specified. Inspect this new contacts table within MySQL Workspace. If you encounter errors during migration. Check the console for the emitted error. Also check/refresh the adonis_schema table to see if that file has been added to the table. If it wasn't added to the adonis_schema table, make the corrections, save the file, and attempt the migration again. If the file was added to the adonis_schema table, run:

node ace migration:rollback

The above command rolls back the migration by exactly one step. It runs the down method in the migration file which instructs the migration file to drop the contacts table completely. After the rollback, run node ace migration:run again to migrate the table.

Adding the /contacts route

Open the api/start/routes.ts and add these line to the end of the file:

Route.post('/contacts', 'ContactsController.store')

Refer to this snapshot. Save the file.

Here, we add a route with a POST method. We define /contacts as the path for the route and assign the store method in the ContactsController file/class as the handler for the route. This means that ContactsController.store() method will be called when the /contacts route is requested.

Why did we use /contacts and not /contacts/new as the route path? This follows the convention for creating API endpoints. Read more about it here. Take time to study it. It is very important for building API servers.

Let's add this route to Postman. Open Postman. Create a new collection named: CRUD. Within the collection, add a new request. For the new request:

1. Enter Create Contact as the request name,
2. Enter {{baseURL}}/contacts as the request path,
3. Change the request method from GET to POST,

4. Below the request path, switch to the Body tab. The Body section is used to define the payload which will be sent in the body of the request. Click raw and paste the following JSON data:

   {
    "firstName": "Hammad",
    "surname": "Pulham",
    "email1": "hpulham0@si.edu",
    "phoneNumber1": "+420 (767) 548-7576",
    "phoneNumber2": "+86 (442) 396-1670",
    "birthday": "1970-03-13",
    "website": "http://boston.com"
   }
   

   Make sure that the type of raw body is JSON. See the end of the line.

   

   The JSON data contains the details of the contact we want to create.

5. Click the Save button and then click the Send button. The request goes through but does nothing because there is nothing defined within the store method of the ContactsController class. We'll return to this after working on the controller file.

Working on the store method within the ContactsController file.

Open app\Controllers\Http\ContactsController.ts. Refer to this snapshot.

First, let's remove some methods we will never use. Remove create and edit methods. These methods are usually used for server-side rendered views. For our API mode, we will use store instead of create method, and update instead of edit method.

I encourage you to type out the content of the snapshot file into your app\Controllers\Http\ContactsController.ts file.

Let's discuss more about the store method within the controller file.

1. At Line 2, we have to import the Contact class from App/Models/Contact.
2. The store method has access to the HTTP context of the request lifecycle. Read more about the HTTP context here. The context is destructured to get the request and response objects. We do not need other properties in the context object for now.
3. At Line 8, we call the request.body() method to access the payload we uploaded with the request in Postman.
4. You will notice some comments within the store method from Line 10. Since the keys of the JSON data we sent within the request body matches the properties in the Contact model, we could do this:await Contact.create(payload) to quickly persist the new contact to the database. But it is not recommended, and dangerous. This is because malicious actors (aka hackers) could attempt to directly assign values of columns such as id, created_by, and updated_by from the outside. However, these values should be generated by the server. They might not succeed as the API server will override those sensitive value but there is no need to deliberately create that vulnerability.

5. That is the reason we need to destructure the payload constant and extract the properties we really want to insert into the database. Then at Line 42, we assign those properties to an object which is the only argument to the Contact.create() method which then creates a new row of Contact in our contacts table. We have to await the create() method because we are handing off (async) operation to the database and waiting for the database to complete the task and return a result to us. That result is the values which we inserted into the database. We store the result in the contact constant on Line 42.

   Note that we are using ES6 shorthand for assigning object properties where the key and value of the properties of the object have the same names. That is:

// This:
{
      firstName,
      surname,
      company,
      jobTitle,
      ...
}

// Is the same as:
{
      firstName: firstName,
      surname: surname,
      company: company,
      jobTitle: jobTitle
      ...
}

At Line 67, we refresh the contact which was the result from the create operation. Why?

For this JSON data to be sent as the request body:

{
    "firstName": "Hammad",
    "surname": "Pulham",
    "email1": "hpulham0@si.edu",
    "phoneNumber1": "+420 (767) 548-7576",
    "phoneNumber2": "+86 (442) 396-1670",
    "birthday": "1970-03-13",
    "website": "http://boston.com"
  }

Without the line await contact.refresh(), if we send a request to the POST /contacts route with the JSON data above as the body, we will get only the same values we inserted as the result which looks like this:

{
    "first_name": "Hammad",
    "surname": "Pulham",
    "email1": "hpulham0@si.edu",
    "phone_number1": "+420 (767) 548-7576",
    "phone_number2": "+86 (442) 396-1670",
    "birthday": "1970-03-13",
    "website": "http://boston.com",
    "id": "cku9d6arh0000o4vobq3bh0uq",
    "created_at": "2021-10-02T06:37:56.499+01:00",
    "updated_at": "2021-10-02T06:37:56.499+01:00"
}

Notice that we have id, created_at, and updated_at in the JSON result. Also notice that the id was auto-generated. That's what we achieved in the generateUUID static method in our Contact model file. Other properties of the Contact model which were not inserted are not returned. So, if we need the complete Contact model to be return to the client, we need to refresh the Contact model (stored in the contact constant) by calling await contact.refresh() before returning the result to the client via return response.created(contact). Refreshing the Contact model gives us the complete JSON data like this:

{
    "id": "cku9ddpl30000q4voex97a04d",
    "first_name": "Hammad",
    "surname": "Pulham",
    "company": null,
    "job_title": null,
    "email1": "hpulham0@si.edu",
    "email2": null,
    "phone_number1": "+420 (767) 548-7576",
    "phone_number2": "+86 (442) 396-1670",
    "country": null,
    "street_address_line1": null,
    "street_address_line2": null,
    "city": null,
    "post_code": null,
    "state": null,
    "birthday": "1970-03-13",
    "website": "http://boston.com",
    "notes": null,
    "created_at": "2021-10-02T06:43:42.000+01:00",
    "updated_at": "2021-10-02T06:43:42.000+01:00"
}

Save all files and send the POST /contacts request via Postman. A new record will be created in the contacts table of the database. Modify the values of the properties of the JSON payload in the body of the request. You can add more properties to the JSON body as long as they are defined in the Contact model. Send more request to create more records inside the table. Make reference to the mock data.

Congratulations!!! You have successfully created new database records with AdonisJS. Do you see how easy it was?

In the next lesson, we will learn about request validations used for verifying and sanitising data sent from the client to the backend before storage.

Save all your files, commit and merge with the master branch.

git add .
git commit -m "feat(api): create column definitions and insert new contacts"
git push --set-upstream origin 12-create-column-definitions-for-contact-model
git checkout master
git merge master 12-create-column-definitions-for-contact-model
git push