Adonis' Object-Relational Mapping, or ORM, Lucid comes with two primary ways to communicate with our database, Models and it's Database module. In the last two lessons, we spent time learning how to set up and define our Models. In this lesson, we're going to step through the basics of CRUD (create, read, update, and delete) operations using both the Models we defined and the Database module. Then, in the next few lessons, we'll dig into each CRUD operation individually.

Both Models and the Database module make use of Knexjs' query builder. This builder allows us to chain methods off one another that ultimately end up building SQL statements.

Database Module

One of the two ways we can communicate with our database is the Database module. Using the Database module, we have access to every table within our database.

The Database module is great when you need to perform CRUD operations on a dynamic table, meaning you don't know the exact table that'll be targeted when writing the code.

Interacting with your database using the Database module looks something like this:

import Database from '@ioc:Adonis/Lucid/Database'

const tasks = await Database.from('tasks').where('status_id', 1).select('*')

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we're querying data from our tasks table for all records where the status_id is equal to 1, and we're selecting all columns in the database ('*').

This Database statement is then converted into SQL that looks like this:

SELECT * FROM tasks WHERE status_id = 1

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Lucid Models

The second way we can communicate with our database is via our Models. Unlike the Database module, with Models, we're restricted to working with the specific table the Model represents.

Using Models to communicate with your database is fantastic for almost all use-cases you'd need when performing CRUD operations.

Interacting with your database using Models looks something like this:

import Task from 'App/Models/Task'

const tasks = await Task.query().where('statusId', 1)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we're doing the exact same query as our Database module example, just using our Model. First, we call the query method, which begins the query builder chain, making where and the remainder of KnexJS' query builder statements available to us.

Referencing Properties

There's a small difference here between our Model and Database module where statements. Our Database module statements must use the column names exactly as they're defined in our database. Our Model, on the other hand, can make use of either the column name defined in the database or the property defined for the column in the model itself.

For example, with our Task's status_id database column, when working with the Database module we must always reference this column as status_id. Meanwhile, with the Model query builder, we can either use status_id or it's Model representation, whether that be:

• statusId - A camel-case representation

  export default class Task {
    @column({ columnName: 'status_id' })
    public statusId: State
  }
  
    
  
      
  
        Copied!
        
  
          
  
        
      
    
    
      
      
      
      
      
    
  

• currentStatusRepresentation - Or, a completely different name altogether

  export default class Task {
    @column({ columnName: 'status_id' })
    public currentStatusRepresentation: State
  }
  
    
  
      
  
        Copied!
        
  
          
  
        
      
    
    
      
      
      
      
      
    
  

The main thing here is that we define the underlying column name (columnName) to use for the Model property inside our model.

Creating Data

Before we can read, update, or delete we first need some data! So, let's start by learning how to save some data to our database.

For each of the CRUD operations, we'll introduce how to perform the operation using both the Model and the Database module.

Model

const project = await Project.create({ 
  name: 'My New Project',
  description: 'Fun on a bun'
})

const project = new Project()
project.name = 'My New Project'
project.description = 'Fun on a bun'
await project.save()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

We can create a new record using our Model in two different ways. First, we can use the create method. This accepts an object containing all the properties we want to save to our database record.

Alternatively, we can create a new instance of our Model, then manually set the properties we want to save to our database record. With this approach, once we're ready to persist our record to the database, we need to call the save method on our Model instance.

Database

const project = await Database.insertQuery<Project>().table('projects').insert({
  name: 'My New Project',
  description: 'Fun on a bun'
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

While inserting using the Database module isn't as pretty as using the Model, it has its use-cases. To insert using the Database module we must:

1. Call the insertQuery method. Here, <Project> is used to define a return type. If you don't need any data back from your insert query, you can leave this off. So, in this example, we'll get back our newly inserted record with a type of Project.

2. Then we define which table we'll be inserting into using table('projects'). In this example, we're inserting into our projects table.

3. Lastly, we pass an object with the property values we want to insert for our record.

Reading Data

Now that we have a couple of records in our database, let's learn about a couple of the many options we have to query data.

Model

const projects = await Project.query().where('statusId', 1)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Just like with our initial example, we can use the query builder to get back multiple records by calling Project.query().

Our example above can be read as:

1. Find all projects with a status_id of 1, or return an empty array.

const project = await Project.query().where('id', 1).first()
const project = await Project.query().where('id', 1).firstOrFail()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

If we need just a single record, however, we can chain to the end of our query builder statement the method first or firstOrFail.

Both of these will return back the first record that matches the query builder's criteria. The difference is how it handles the use-case where it cannot find any records matching the query builder statement.

• first, will return back null when it cannot find any record.

• firstOrFail, will throw a 404 error.

Our above examples can be read as:

1. Find the first project with an id of 1, or return null. Since this value can be null, the type returned from this call will be nullable (Project?)

2. Find the first project with an id of 1, or throw a 404.

const project = await Project.find(1)
const project = await Project.findOrFail(1)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Another option we have for querying a single record is by call find or findOrFail depending on whether you want the request to 404 if a record cannot be found. Both these methods accept the id, or primary key, of the record we want to grab.

Our above examples can be read as:

1. Find the first project with the id of 1, or return null. Since this value can be null, the type returned from this call will be nullable (Project?)

2. Find the first project with the id of 1, or throw a 404.

const project = await Project.findBy('statusId', 1)
const project = await Project.findByOrFail('id', 1)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

The last approach we're going to cover is findBy and findByOrFail. Both of these accept a where statement and the query will return back the first record that matches our criteria.

Our above examples can be read as:

1. Find the first project with a status_id of 1, or return null. Since this value can be null, the type returned from this call will be nullable (Project?)

2. Find the first project with an id of `, or throw a 404.

Database

const projects = await Database.from('projects').where('status_id', 1)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Most of our Database module examples will include some variation of this statement. Here we're grabbing all records from our projects table with a status_id of 1.

const projects = await Database.from('projects').where('id', 1).first()
const projects = await Database.from('projects').where('id', 1).firstOrFail()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we're using the query builder to grab just the first record in our database with an id of 1.

1. first will attempt to find the first record matching our query criteria. If it cannot find any records it will return null. Since this value can be null, the type returned from this call will be nullable (Project?)

2. firstOrFail will, again, attempt to find the first record matching our query criteria However, if it cannot find any records it will throw a 404 error.

Updating Data

Second to last, we need to cover how to go about updating records that already exist within our database.

Model

const project = await Project.findOrFail(1)
project.name = 'My Test Project';
await project.save()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

To update a record using the Model, we must first query for the record so that we have an instance of the record to update. Here we're using our findOrFail method to provide an id to query a record with.

Once we have a record queried, all we need to do is change the properties we need to update, then call the save method to persist those changes to the database. So, in our example, we're updating our project's name from My New Project to My Test Project.

const project = await Project.findOrFail(1)
project.merge({
  name: 'My Test Project',
  description: 'Tacos are fun too'
})
await project.save()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Alternatively, instead of manually updating each property on our record, we can call merge and pass an object of all the new properties we'd like to update. merge will not alter any properties not defined on the object we pass into it. So, here in our example we're only updating our name and description. Nothing else on our project will be touched.

Database

const project = await Database.from('projects').where('id', 1).update({ 
  name: 'My Test Project'
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Using the database module, we can query for the record or records we want to update, then chain the update method to the end of our query builder statement. So, here we're querying for the project with an id of 1, then updating its name to My Test Project. Like merge with our Model update, the update method will only touch properties we include in our object. So, in our example, only the name will be updated.

const project = await Project.query().where('id', 1).update({
  name: 'My Test Project'
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

We can also use this same query builder approach to update one or more records using our Model by calling query off our model.

Deleting Data

Lastly, let's learn how to remove records from our database by deleting them.

Model

const project = await Project.find(1)
await project.delete();

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Using our Model, we again, first need to get an instance of our database record by querying for the record. Once we have our record, we can then call the delete method which will delete that particular record out of our database.

Database

await Database.from('projects').where('id', 1).delete()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Using the database module, we can query for the record or records we want to delete, then chain the delete method to the end of our query builder statement. So, here we're querying for the project with an id of 1, then deleting it from our database.

const project = await Project.query().where('id', 1).delete()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

We can, again, also use this same query builder approach to update one or more records using our Model by calling query off our model.

Usage In Route Handler

So far we've talked about our CRUD operations outside the context of a route handler/controller method. So, before we wrap up this lesson let's do exactly that. Since we've been working with our Project model and table throughout this lesson, let's jump into our ProjectsController.

Here we have our ProjectsController stubbed with our resource route handlers:

Controller

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class ProjectsController {
  public async index({ }: HttpContextContract) {
  }

  public async create({ }: HttpContextContract) {
  }

  public async store({ }: HttpContextContract) {
  }

  public async show({ }: HttpContextContract) {
  }

  public async edit({ }: HttpContextContract) {
  }

  public async update({ }: HttpContextContract) {
  }

  public async destroy({ }: HttpContextContract) {
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

• index - typically used to display or return a list of the controller model's item. So, here we'd use this method to return a list of all our projects.

• create - typically used to display a creation page. So, here we'd use create to display a create page for our project, however, we haven't covered views or Adonis' template engine yet, so we'll skip this.

• store - typically handles a POST request to store a new record or records. So, here we'd use store to store a new project into our database.

• show - typically used to display or return a single item. So, here we'd use show to return a single project based on the route param we define for this route. Resources will default to using an id.

• edit - typically used to display an edit page. So, here we'd use edit to display an edit page for our project. Again, we'd select the specific project to edit by the route param provided for the route.

• update - typically handles PUT or PATCH requests to update an existing database record or records. So, here we'd use update to update an existing project within our database. Again, we'd select the specific project to update based on the route param provided for the route.

• destroy - typically handles DELETE requests to delete an existing record or records from our database. So, here we'd use destroy to delete an existing project from our database. Again, this too would typically use the route param defined for the route to determine what to delete.

You'll notice I used the word "typically" for all of those methods. That's because although they have typical usages, you're free to use them in any way you'd like. You could even delete them and use completely different method names. Just remember to reference those names in your route definitions.

Since we have our route handlers defined via our ProjectsController, all we need to do now is define the routes. We can quickly do this by using a resource route definition to define all of these routes in one line.

import Route from '@ioc:Adonis/Core/Route'

Route.resource('projects', 'ProjectsController')

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
start
routes.ts

Remember, you can get a list of all the routes defined within your project at any time by opening your terminal at your project's location and running $ node ace list:routes.

Storing A Record

First, let's cover storing a record inside our database using our controller. Since we're storing a new record, we'll want to use the store method inside our ProjectsController. Next, we'll want to replicate our creation example from earlier in the lesson.

Controller

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Project from 'App/Models/Project'

export default class ProjectsController {
  public async store({ }: HttpContextContract) {
    const project = await Project.create({ 
      name: 'My New Project',
      description: 'Fun on a bun'
    })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

So here we've imported our Project model from 'App/Models/Project', then we call our Project model's create method, passing it an object of the record we want to create and persist to our database.

Typically, you're not going to want to hard-code the values used to create your record. So far, we're hard-coding both the name and description by providing it static strings. In most cases, you'll want to grab values off your request that are provided either by a form submission or an API request's body.

To fix this, we can extract our request out of our HttpContextContract. Our request contains three methods we can use to grab values sent up with our request via the request body.

1. all
   Example: const data = request.all()
   Description: This will return back all items sent on the request body in addition to any query string the URL may have. Be aware of security concerns here, since we're grabbing all items sent with the body, the user could tack items into the request we don't want to be changed on their record (like making themselves an admin).

2. only
   Example: const data = request.only(['name', 'description'])
   Description: This will allow us to provide an array of properties we want to grab off the request. This will then return back an object containing only those properties, all others sent with the body will be left off.

3. input
   Example: const name = request.input('name')
   Description: This allows us to grab just a single property value from our request body. This will return the value directly back to us.

Since we have more than one property we want to save to our project's record, we'll make use of only within our example. We can also extract our response from our HttpContextContract so we can return back a simple JSON response until we cover views and Adonis' template engine.

Controller

export default class ProjectsController {
  public async store({ request, response }: HttpContextContract) {
    const data = request.only(['name', 'description'])

    const project = await Project.create(data)

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

Then, we could use a REST client, like Insomnia, to test our project's creation.

Updating A Record

For the most part, updating a record will be very similar to creating a record. We'll want to extract our request and response from our HttpContextContract. We'll want to grab the data we want to update off our request body. Update the record, and then return a JSON response for now.

However, we're also going to want to extract params from our HttpContextContract as well. This way we can grab our update route's identifier, which for resources defaults to using the record's id. We can then use this identifier to query the specific record within our projects table we want to update.

So, inside our update method, let's do the following.

Controller

export default class ProjectsController {
  public async update({ request, response, params }: HttpContextContract) {
    const data = request.only(['name', 'description'])

    const project = await Project.findOrFail(params.id)
    project.merge(data)
    await project.save()

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

Querying & Deleting A Record

Lastly, when it comes to querying and deleting a record inside our controller, we've already covered the basics with our update method.

For querying a list, we can just apply any of the list queries we covered earlier and return the results.

Controller

export default class ProjectsController {
  public async index({ response, params }: HttpContextContract) {
    const projects = await Project.query().where('statusId', 1).orderBy('createdAt', 'desc')

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

Here the orderBy('createdAt', 'desc') will order our queried projects by their creation date, newest first.

For querying single records, we'll want to make use of our params identifier. Again, since we're using a resource to define our routes this will be the default of id.

Controller

export default class ProjectsController {
  public async show({ response, params }: HttpContextContract) {
    const project = await Project.findOrFail(params.id)

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

Lastly, for deleting a record let's assume we only need to delete a single record when called. In which case, it'll be identical to finding a single record, except before returning we'll call our project's delete method.

Controller

export default class ProjectsController {
  public async show({ response, params }: HttpContextContract) {
    const project = await Project.findOrFail(params.id)
    await project.delete()

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
Projects

Next Up

So, now that we have a general understanding of how to approach basic CRUD operations within Adonis using Lucid, we're ready to move into more complex operations. We'll spend the next few lessons digging deeper into CRUD operations starting with creating.