Model vs Database Query Builder
In this lesson, we'll take a look at the differences between the model and database query builders.
- Author
- Tom Gobich
- Published
- Dec 28, 23
- Duration
- 5m 58s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Model vs Database Query Builder
-
(upbeat music)
-
So we've been taking a look so far
-
at the Model Query Builder.
-
The Model Query Builder runs directly off of our models.
-
We can use the actual column names
-
that we've defined on the models within our queries itself
-
to make things a little bit more developer friendly.
-
And you'll also notice that all of the responses
-
have been of type our model itself.
-
So whenever you're working with the Model Query Builder,
-
it's always going to return back a type
-
of your model itself.
-
So it's always going to be the same structure
-
as your models defined.
-
And that's regardless of whether or not
-
you're specifying any select statements
-
or anything of the sort.
-
So for example, if we specify that we just want back
-
the ID for our topics here and we give this a save,
-
we're still getting back a type of our topic array.
-
And whenever we actually send out a request
-
to this endpoint to get back our results,
-
you'll see that it's not just an array
-
of the underlying IDs.
-
Instead, it's an object with the ID inside of it,
-
still keeping true that it's returning back
-
some reference to our model itself.
-
Now, if we did truly just want this to be an array of IDs,
-
we could wrap our await statement here in parentheses,
-
map over the results, do topic, topic.id
-
to just return back the ID and send this out.
-
And now we just get back an array of the underlying IDs,
-
not wrapped in any sort of object.
-
Now, in addition to the Model Query Builder,
-
which is what we've been taking a look at here so far,
-
we also have access to the Database Query Builder.
-
And this is more of a raw ConnectJS Query Builder.
-
Let's take a look at const db equals await.
-
We can import the Database Query Builder
-
via IOC Adonis Lucid Database.
-
We can specify the table that we want to run this query
-
against by doing dot from.
-
This will be our topics table.
-
And you'll notice that we're using the underlying table name
-
from the database itself.
-
None of the names that we have defined inside our model
-
will work whenever we're working
-
with the Database Query Builder.
-
The key difference here between these two
-
is that the Model Query Builder runs through our models
-
and we can use our model names, relationships, et cetera,
-
with the Model Query Builder.
-
And it will always return back a reference to our model,
-
regardless of what we're selecting or joining in.
-
The Database Query Builder
-
runs directly through our database.
-
It doesn't touch any of our models.
-
It doesn't run any hooks.
-
It just goes directly to the database.
-
Because of that, we don't have any actual types
-
running through this database.
-
So if we take a look at the type of a response,
-
currently it's just an array of any.
-
So if we go ahead and add DB into our JSON response here,
-
I'm gonna get rid of the select
-
on our Model Query Builder here temporarily.
-
Now our two queries here match
-
in terms of what we would expect.
-
We're just getting back all of our topics
-
from our Topic Model Query Builder.
-
And we're just getting back all of our topics
-
from our Database Query Builder.
-
So let's give this a save, give this a run.
-
Here is our Topics Query Builder result.
-
You'll see that we get back exactly
-
what we've been working with here so far.
-
For our Database Query Builder,
-
we get back the exact same result.
-
And that's because we're looking
-
at the serialized response of our topics
-
and the serialized result of our database,
-
which at the end of the day will match.
-
Now let's take a second to take a look
-
at the code implementation here.
-
So it's console.log, our topics and our DB.
-
And then let's open up our terminal here.
-
I'm gonna open this up all the way
-
and let's send the request out.
-
Okay, same serialized results that we got before.
-
But if we jump back into our console,
-
our database results match that,
-
that we're getting with our serialized results
-
for the Database Query Builder.
-
But our Model Query Builder you'll see
-
is turning back actual instances of our model itself.
-
So through the Model Query Builder,
-
we can still access all the information
-
in terms of our model itself,
-
including the underlying data that we would then get back.
-
So the key difference between the Model Query Builder
-
that we're taking a look at the results here for
-
and the Database Query Builder
-
is what it's referencing and going through.
-
The Model Query Builder will always go through the model.
-
The Database Query Builder just goes directly
-
to the database and will return back
-
whatever the database returns.
-
Let's go ahead and collapse this down here for a second
-
and let's add these selects back.
-
For our Database Query Builder,
-
we can do the exact same thing, select.
-
You'll also notice that the where statements
-
and everything that we've worked with
-
in the prior two lessons is applicable here.
-
We'll take a look at the main difference here
-
between that next.
-
Okay, let's give this a save.
-
I'm gonna scroll all the way down on our terminal here,
-
jump back into Insomnia, send this off again.
-
Again, the underlying serialized results match here.
-
But if we take a look at the console,
-
we're still getting back a reference of our topic.
-
It looks the exact same as the results before.
-
The only difference is going to be the underlying attributes
-
that we have populated.
-
In terms of our database results, again,
-
it matches exactly what we have for the serialized results.
-
I'm gonna go ahead and collapse our terminal away,
-
get rid of our console log.
-
And now one last thing to take a look at
-
with the Database Query Builder
-
is the difference between our column references.
-
So previously, whenever we sorted our Model Query Builder,
-
we did an order by, and the only one that we have
-
that's camel-cased is our createdAt and updatedAt.
-
So we'll go ahead and do that here,
-
and we'll do it in descending order.
-
So for the order by here, we're using the column name
-
as it's defined in our model,
-
which is camel-cased createdAt.
-
For our Database Query Builder,
-
whenever we do an order by here,
-
we're going to want to reference the column name
-
as it's defined in our database,
-
which is created_at.
-
And again, we can do descending here as well.
-
Give this a save, jump back into Insomnia, send this off.
-
And again, you'll see the underlying
-
serialized results match.
-
The main difference again is the types of the response.
-
For the Model Query Builder,
-
it's going to be a type topic array.
-
And for the Database Query Builder,
-
it's going to be an any array.
-
How we reference the table name,
-
the Model Query Builder will use the table name
-
as it's defined in the model.
-
And the Database Query Builder,
-
we need to actually specify the table name
-
that we want to use as it's defined in the database.
-
And the same goes for the columns.
-
We need to use the column as it's defined in the model
-
for the Model Query Builder,
-
and the column as it's defined in the database
-
for the Database Query Builder.
-
One last notation is that the Model Query Builder
-
is forgiving, so you can use the column names
-
as they're defined in the database as well.
-
So let's switch that to created_at,
-
send this off, and we get back the exact same thing.
-
It will work just fine because at the end of the day,
-
it will double check against the database itself
-
if it cannot find a model reference.
-
Introduction
-
Routing
-
2.0Routing Introduction6m 14s
-
2.1Dynamic Routing with Route Parameters14m 1s
-
2.2Moving & Organizing Routes4m 19s
-
2.3Naming, Grouping, & Prefixing Routes8m 28s
-
2.4Multi-File Route Grouping Strategies5m 31s
-
2.5Generating URLs and Signed URLs13m 50s
-
2.6Extending the Adonis Router and Route Matchers15m 44s
-
2.7The Middleware Mountain12m 44s
-
-
Controllers
-
Service Providers
-
Lucid ORM
-
5.0Introducing, Installing, and Configuring Lucid ORM10m 0s
-
5.1Migrations & Understanding the Flow of Migrations16m 35s
-
5.2What To Know Before Altering Your Database with Migrations10m 26s
-
5.3What Is A Model?12m 44s
-
5.4Database Schema to Migration11m 56s
-
5.5Database Migrations To Lucid Models4m 59s
-
5.6Defining Model Relationships13m 4s
-
5.7Creating Records with Lucid ORM12m 38s
-
5.8Password Hashing & Model Hook Overview8m 42s
-
5.9Easy Querying with Static Model Query Methods7m 41s
-
-
Query Builder
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!