In the last lesson, we covered the basics of creating, reading, updating, and deleting (CRUD) within Adonis using both our Lucid Models and the Database Module. In this lesson, we're going to dig in a little bit deeper with creating data by learning how to do so through our defined relationships.

Creating Data

To start with, let's quickly summarize the three different methods we covered in the last lesson to create data in our database.

import Project from 'App/Models/Project'
import Database from '@ioc:Adonis/Lucid/Database'

// Method 1: Calling create off our model
const project = await Project.create({ 
  name: 'My New Project',
  description: 'Fun on a bun'
})

// Method 2: Creating a new model instance
const project = new Project()
project.name = 'My New Project'
project.description = 'Fun on a bun'
await project.save()

// Method 3: Using the Database Module
const project = await Database.table('projects').insert({
  name: 'My New Project',
  description: 'Fun on a bun'
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

1. Method 1: We import our model, Project, and call the create method off our model. This method accepts an object containing the properties we wish to create our new database record with.

2. Method 2: We import our model, Project, then we call it as a constructor method; creating a new instance of our model. We set the properties on our model instance we want to be persisted with our database record. Lastly, we call the save method on our model instance.

3. Method 3: We import the database module. On the database module, we call the table method. Passing in the table name we wish to insert a record for, projects in this case. Lastly, we call insert which accepts an object containing the properties we wish to create our new database record with.

First Or Create

In addition to the three methods we covered in the last lesson, we can actually conditionally determine whether we should insert a new record using a method off our model called firstOrCreate.

This method accepts two arguments. The first is a search payload, this is essentially an object-based where statement. The second is a create payload, this is data that would be used to create the record in the database.

Using this method, we can use the search payload to search our database for a particular record. If firstOrCreate can find at least one record with this search payload, it will just return back the first found record.

If firstOrCreate cannot find at least one record in our database matching the search criteria it will use the create payload to create our record in the database.

const searchPayload = {
  name: 'My Example Project'
}

const createPayload = {
  name: 'My Example Project',
  description: 'This is my example project'
}

const project = await Project.firstOrCreate(searchPayload, createPayload)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

So, if our database contains a project with the name 'My Example Project', then firstOrCreate will stop at that first found record and return it back to us, skipping over our createPayload. However, if it does not find a record with the name 'My Example Project', then firstOrCreate will create a new record in our database using our createPayload. It will then return that newly created record back to us.

Update Or Create

A slight alternative to firstOrCreate is updateOrCreate. Here the arguments are the same as firstOrCreate, what's different is the behavior. With updateOrCreate if at least one record is found matching our search criteria, the second argument will be used to update the first found record. If one record cannot be found with our search criteria, the second argument will be used to create a new record. In both cases, the newly updated or newly created record is returned back to us.

const searchPayload = {
  name: 'My Example Project'
}

const updateOrCreatePayload = {
  name: 'My Example Project',
  description: 'This is my example project'
}

const project = await Project.updateOrCreate(searchPayload, updateOrCreatePayload)

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Relationships

A few lessons back we set up our models. Within these models, we defined our relationships. We can use these relationship definitions to create records from related models. Thanks to this, we don't need models for our intermediary tables as their relationships are defined as many-to-many and Lucid supplies us with powerful methods we can use to attach, detach, and sync records to our intermediary tables.

Relationship Correction

Earlier in this series we defined our intermediary tables with plural snake-cased concatenated names, we have project_users and project_tasks with the idea these would be the default names for Adonis. Adonis actually defaults to using the singular form of this snake-cased concatenation instead of the plural form. So, the defaults are actually project_user and project_task.

To remedy this in our project we have two options. First, we could manually define the pivotTable on our relationship definitions. For example, for our project to user relationship we could define the pivotTable in our model like so:

@manyToMany(() => Project, {
  pivotTable: 'project_user',
  pivotColumns: ['role_id']
})
public projects: ManyToMany<typeof Project>

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Alternatively, since we're still in development and don't have any critical data within our database, we could just alter the intermediary table names to match the default Adonis will look for. This is the approach I'll be using for the remainder of the series.

To do this, we'll first need to rollback our database to its default, empty, state.

$ node ace migration:rollback --batch 0

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Then, we'd change the tableName in our project_users migration to project_user and for our project_tasks we'd change it to project_task.

// database/migrations/TIMESTAMP_create_project_users_tables

export default class ProjectUsers extends BaseSchema {
  protected tableName = 'project_user'

  // ...
}

// database/migrations/TIMESTAMP_create_project_tasks_tables.ts

export default class ProjectTasks extends BaseSchema {
  protected tableName = 'project_task'

  // ...
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

We needed to rollback before changing the table names so that Adonis knows the table names to drop. Last, we just need to re-run our migrations to recreate our tables.

$ node ace migration:run

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Now we should be all good to move forward. Apologies about that.

Creating A Test User

Before we begin with creating relationship records, we're going to first need two users to work with. We'll be doing this properly later on via a registration step while covering authentication, but for now quick and dirty will suffice.

So, let's create two simple test users. We can quickly do this by adding two creates in our UsersController.store and adding a route for this method.

import User from 'App/Models/User'

export default UsersController {
  public async store({ response }: HttpContextContract) {
    const user1 = {
      username: 'testuser1',
      email: '[email protected]',
      password: 'test123'
    }

    const user2 = {
      username: 'testuser2',
      email: '[email protected]',
      password: 'test123'
    }

    await User.createMany([user1, user2]);

    return response.json({ success: true })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
UsersController.ts

So here we're creating both users in one go by calling createMany. Not, this is definitely not production-ready user creation, we're just using this to quickly get two users in our database using Lucid as a learning opportunity.

Next, let's register a route, then kick off a request to this route to create our users.

Route.get('/testusers', 'UsersController.store')

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
start
routes.ts

Then, visit your testusers page at http://localhost:3333/testusers to persist these test users into our database. After this is complete, feel free to clear out the UsersController.store method and the testusers route we just created, we won't need them again.

Grabbing Relationships

Step one of working with relationships using Lucid Models is first having an instance of a record. Whether we query for that record or use a newly created record instance doesn't matter.

// query for record instance
const project = await Project.findOrFail(1)

// use newly created instance
const project = await Project.create({ name: 'Test Project' })

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Now that we have this record instance, we'll have a method available to us we can utilize to reach through to our relationships, called related. This accepts the name of the particular relationship we want to grab as it's defined in our model.

const project = await Project.findOrFail(1)

project.related('users')

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

The properties and methods we'll have available to us from the returned related method call vary slightly depending on whether the relationship is dealing with one or many records through the relationship.

Attaching Many-To-Many Records

The first method we'll be covering is attaching new many-to-many records. To do this we'll make use of a method returned back from our related call, called attach. Attach accepts two different types of arguments, an array of ids or an object with the ids as its keys.

Attach will attach two existing records to one another within the defined intermediary table.

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

	const user1 = await User.findOrFail(1)		
    const user2 = await User.findOrFail(2)
		
	const project = await Project.create(data)
		
	await project.related('users').attach([user1.id, user2.id])

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
ProjectsController.ts

Here we're grabbing instances to the two test users we created a little earlier. Then we're creating a new project using the data sent in with our post request. Next, we're calling related, grabbing our users relationship from our project. Then we're calling the attach method.

So when we run attach here it's

1. Grabbing our project's id.

2. Then, it's grabbing the relationship definition from our project model for the provided relationship in our related method call. It will use this definition to determine how to save the records and which table to use.

3. Last, it will create a new record within the intermediary table defined for the relationship for each id provided to the attach method. So, in our example, we'll get two records added to our project_users table. Both will have the same project_id value, whatever our project instance's id is. The first record will have a user_id of 1, and the second a user_id of 2.

Now you might be asking, "this is great, but what if I have additional data on the intermediary table I need to save?", and great question! We actually have this exact scenario with our project_users table. We have an additional column on our project_users table called role_id that we may want to define on create.

To do this, we need to use attach's second argument type. With this argument type, we'll provide a single object, where are user's ids make up the object's keys. Then for each user id, we add a sub-object containing the additional intermediary columns we need defined, role_id in our case.

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

	const user1 = await User.findOrFail(1)
	const user2 = await User.findOrFail(2)
		
	const project = await Project.create(data)
		
	await project.related('users').attach({
      [user1.id]: {
        role_id: 1
      },
      [user2.id]: {
        role_id: 2
      }
    })

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
ProjectsController.ts

So, here we're providing an object, the first key is our first user's id (let's say this is 1). Then for this first user, we're providing an object containing our additional intermediary column role_id, which we're setting to 1.

Then, we're attaching our second user with a role_id of 2. Once this executes, we'll get records in our database looking something like this, let's assume this project's id is 5:

id | project_id | user_id | role_id
 1 |          5 |       1 |       1
 2 |          5 |       2 |       2

Detaching Many-To-Many Records

So, now we've got our users added, but what if we need to remove one or more users from a project? For this, we can make use of the detach method. This will essentially do the inverse of attach, it will remove the intermediary record for the provided ids.

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

	const user2 = await User.findOrFail(2)
		
	const project = await Project.firstOrFail(params.id)
		
	await project.related('users').detach([user2.id])

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
ProjectsController.ts

So here we'd be removing our second user from whatever project instance we have. Here's what the database record this would be REMOVING would look like, let's say this project has an id of 5:

project_id | user_id 
         5 |       2 

Want to remove all users from the project? Easy-peasy, just call detach without any arguments.

await project.related('users').detach()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Syncing Many-To-Many Records

We've covered adding many-to-many and removing many-to-many records, but what if we want to detach all records then reattach only the defined records on our request? For this, we can make use of a method called sync. Sync will detach all records, then reattach only the records we provide it.

Sync accepts the exact same arguments as attach, so either an array of ids or an object with our ids as the object keys.

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

	const user2 = await User.findOrFail(2)
		
	const project = await Project.firstOrFail(params.id)
		
	await project.related('users').sync({
      [user2.id]: {
        role_id: 2
      }
    })

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
ProjectsController.ts

So, let's say we started with users one and two attached to project 5 prior to this update call. Now, whenever we call to update it will first remove both users from the project, then re-add user 2 back to the project with a role_id of 2. Since the relationship is being removed, we do need to re-supply that we want user 2 to have the role_id of 2. This would apply to any additional intermediary table data you may want to persist.

Creating Many-To-Many Records

To round out our many-to-many relationship talks, let's cover how to create a whole new record while also creating the intermediary table record all in one blow. To do this, we can make use of one of the create methods we covered earlier, but called off the context of our related method; create, createMany, firstOrCreate, and updateOrCreate.

So, let's say when we create a new project in addition to attaching the user to the project we also want to create an introductory task for the user as well.

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

	const user1 = await User.findOrFail(1)
		
	const project = await Project.create(data)
		
    // attach the creating user (we'll do this once we cover auth)
	await project.related('users').attach({
      [user1.id]: {
        role_id: 1
      }
    })

    // create an introductory example task
    await project.related('tasks').create({
      name: 'Welcome to your new project!',
      createdBy: user1.id
    })

    return response.json({ project })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
app
Controllers
Http
ProjectsController.ts

Here, we're grabbing our project to tasks relationship, which is many-to-many. Then, we're calling create to create a brand new task using the data we provide in the object argument. Now, in addition to just creating our new task record, since we're calling create through a relationship, Lucid will lend a hand and also create the relationship record for us in our intermediary table.

Creating Many-To-One Records

So far we've been discussing many-to-many records, but what about many-to-one records? In our previous example covering creating many-to-many records, if you inspect closely, we're actually creating a many-to-one record when we create our task through our project, via the createdBy property. We're designating the provided user as the creator by providing their id as the createdBy value. We could also do the exact same thing for our assignedTo property as well.

If we were to do this outside the context of a relationship, it would look a bit like this:

await Task.create({
  name: 'This is an example task',
  createdBy: user1.id,
  assignedTo: user2.id
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Next Up

Now that we've taken a look at some of the power our relationship definitions in our models provide us, we'll be continuing onward by taking a look more in-depth into reading and querying data from our database.