Learn Elixir: Writing and Composing Queries

Learn Elixir In 12 Weeks: https://learn-elixir.dev/

Level 4.3: Writing and Composing Queries; write ecto queries which allow us to communicate with an SQL database such as Postgres or MySQL.


Transcript:
Hello and welcome to learn elixir. Week four module three. In this video we're going to go over writing ecto queries which allow us to communicate with an SQL database such as Postgres or my SQL. These ecto queries come from a DSL that ecto has created for itself that allow us to write syntax that's very similar to post guests curious themselves, but ultimately it lets us compose them together, which have some longterm benefits such as being able to reuse portions of a query in different queries. Various enact though are actually a bit safer than running direct manual queries with strings embedded into the query. This is because if you have an input that's going into your query, you can have a possible injection attack where someone actually puts a query that's malicious inside of that string input field save to delete your tables and you could lose your data or have sensitive data leaked out. This is what echo helps prevent because all the input inside of the fields that are going into SQL are being sanitized for those types of attacks. Using ecto also allows us to eventually have a composable API where we're able to have our functions encapsulating specific features of a query and that way we can compose our longer queries together using the different encapsulated parts we've created before. Queries allow us to also use where join are or where just like they would in Postgres or my ask you up this syntax, it looks very similar to actually writing my SQL itself and if this syntax isn't enough, we can always drop down into fragments and actually write the SQL manually. Let's look at what the syntax looks like and how we can use it to query a database. For example, we're going to keep using the same ecto example, that house that we created in the previous video. So we're going to go load everything up in IEX and we'll simply type import, echo dot query so that we can start writing queries. We're also going to alias our echo example that house as well as we go and you'll see the syntax here where we're using the same syntax that the shell accepts in order to be able to specify multiple endings. Here, we shortened it to separating inside of the curly braces by comma, which allowed us to specify two modules under ecto example that house. Now with all these things aliased we can go ahead and start writing our first query. Our first query is just going to replicate what repo dot all does. So if we remember with report at all, we're allowed to specify a specific schema module and get all that queries from the database that schema represents. So in our case, we ask for all of the houses and we get back all of the houses that are returned by our post guests period in our debug lock. So we can recreate this query by typing in from and then specifying H in house. And that is basically the same as just specifying the schema module itself. So if we actually pass this to replay at all, we'll get back the exact same result. However, because we specified it in from, we're now able to specify a query that looks very similar to SQL syntax itself. So we could specify from H in house where the house price is less than 20 and now we'll only get back one result because we're looking for the house price of less than 20 we can see that our call essentially got converted from our query that we wrote inside of ecto into it SQL query itself. We can go further into this and create an oral clause where we say or H. dot. Name is equal to pest house and now when we say that we get both of them back because we're specifying an oral query, we're the name is test house. We can also separate her or queries and an queries out by calling the ware or or multiple times. For example, if we wanted to get and name, we could just specify another where, and you can see here we did aware prices less than 20 as well as a where the name of that house is test house and so we got back zero results. We could specify or queries by typing in or where. When we do that we see that our query gets changed to an or query. So this is how we can specify queries as individual parts of a whole decade combine together at the end in order to form our final queries to our SQL server. This makes it a lot simpler to figure out the logic of a query when we can separate it out by specifying one per clause with our from query, we're also able to specify a select and can select different fields off of our data.....