 afternoon everybody how was lunch good good awesome so this is the junior developer track and so I'm kind of curious going into this what people's backgrounds are so how many of you came to Rails from some kind of bootcamp okay how long was that bootcamp eight weeks six weeks twelve weeks six months seven months that's a long time that's a degree how many people have a just a traditional CS CS degree okay cool and then how many of you are just kind of learning by doing things watching internet videos stuff like that yeah well that's just so you know that never changes I've been developing for a long time and internet videos and just trying things is pretty much the way you have to roll so behind almost every Rails app and almost every app in general is a powerful language called SQL and I got this the idea for this talk when I was working with a junior developer who was trying to build a dashboard page and try to give some stats on user activity he was using the tools that are built in to Ruby because that's that's what he knew how to use and the problem with that is that when you put that code in production all of a sudden instead of downloading the couple of hundred or couple dozen rows that you have in your development system and doing some calculations on that now you're asking Ruby to pull down millions of rows and do calculations on that this is a situation where clearly the database is the right place to be doing the heavy lifting and so when we started pairing on this he explained the problem and I just immediately jumped into a SQL prompt and started writing a SQL query that gave the answers that we were looking for on the dashboard and the thing that he said to me and this is a key word for me wait how did you do that and I have found over and over again as I've worked with other developers that anytime I say the words wait how did you do that I'm about to learn something and so that was a learning moment for for both of us I learned that you know not everyone has the SQL background and he learned a lot about the tools that are available behind the scenes so we spent the whole hour writing SQL queries and you know I think we both came away from that learning a few things so SQL to put it another way is the hero that Rails deserves something we should probably get out of the way right up front is it SQL is it SQL I go back and forth between both of them it doesn't really matter it's not like we're talking about gifs here SQL is officially the correct way but if you are saying SQL and you know captain actually says well actually it's it's SQL that's totally fine because you can pull out the fact that it used to be SQL and they changed it when they made the standard just because of some trademark disputes or something with an airline or something like that so it's officially SQL but I say I say it both ways all right so today we're going to talk a little bit about the the backstory of SQL we're going to look at some wonderful examples and then talk about where you can go from here a little bit about me my name is Ryan DeLugas I did not sneeze through my name there it's actually pronounced that way DeLugas anyone else here Polish all right we will find some barbecue pierogi after this in this town I've been programming for about 20 years 15 of those professionally I live in Cincinnati Ohio with my family there you can find me on Twitter I'm at Lebowski so I don't need to tell you my favorite movie I'm a consultant which pretty much means I can say yes to things before I actually know how to do them learning as you go is a skill that you'll learn as a developer and recently I have started working as an instructor for Girl Development which is an organization that helps adult women get launched into technology careers so if that's something that's interesting to you talk to me afterwards I'd be happy to talk about it all right so the world before ORMs ORMs that's the the all-encompassing term for things like active record so in the late 90s we were building web applications but we weren't using these these tools like active record we were just putting the the SQL directly in line with our code we were using Pearl we were using cold fusion we were using PHP PHP still is in use today the structure of PHP apps is a lot better today than it used to be though and we would just put these these SQL strings right there in line in the code and it got messy and it was complicated and it was frustrating so let me give you an example of that say we had an application and we wanted to display a list of all the people that are in our system so we would have some SQL that we would write like this a very basic query select the name and some other parameters from our people table okay that's that's great so we've got the query and now we have to execute that query against our database and when we do that we're gonna get back a bunch of data collected into an array of arrays so you have a big array and each element of that array is one row from your database okay well we've got the data but now to use it on our application in our kind of object-oriented way we need to collect we need to build those people and then we need to collect them into a an array in our language so we'll iterate through each row of the results there we'll create a new person and then for every single element of data that came back from our query we need to just manually assign it into the different attributes that our object has and then we have to consider like you see on the last line here things like well in the database it's stored as a zero or a one but our object knows about Booleans and so we need to translate that you have to think about all those kinds of little gotchas there and then finally we'll shove that person on to our array of people rinse and repeat but now you have to do the same sort of thing for every single interaction with the database that your application has it gets very monotonous it gets very error-prone and the code is very fragile I mean if you look at that code there you can imagine if you change something with the structure of that query it's very easy to break that mapping so as a developer I think we all at least I do but I think we all have this proclivity to avoid repetitive tasks and if we see something that we have to type or do more than once we try to find a way to automate it with software and so now is the time where I admit to something that is hilariously dangerous and naive the first application I was ever paid to build again this is late 90s it was a PHP app and it was you know your standard CMSI type of application small business app written in PHP and a lot of the forms look like this we have an HTML form we collect a few fields and we submit it but take a look at that top line there where it's submitting to it's submitting to something called save data dot PHP doesn't seem very specific and it doesn't have to be because my way of getting around the problem was I passed in a hidden field called table name and then on the back end I went ahead and just took that table name and then dynamically built a sequel query to insert data into the database update data delete data whatever needed to be done this is very very unsafe if you don't understand why this is unsafe may I suggest that you attend some of the application security courses that are later on this week I think you will find them very interesting my solution to the problem was you know please don't hack me on the submit now this was behind an admin interface and stuff so I guess you could kind of justify it like that but really this this is no way this is no way to code so fast forward a little bit to the mid-2000s and ORMs are starting to become popular so in the Java space we had hibernate and OJB and in Rails we have active record so with active record you you know you take this giant mess of code that's that's here and you replace it with a one-liner that's great so if active record can replace all that why should I bother to learn SQL well one reason is because SQL is everywhere SQLite which is the database that ships with Rails for development is the most if not top five most popular widely installed software in the world SQLite is installed on every Android phone every iPhone every Mac every Windows 10 PC every flavor of Linux Chrome Safari Firefox it's in a bunch of cars it's in airplanes it's literally everywhere right but so what you can still talk to it with active record and ORM tools why do you need to learn SQL it's because ORMs come and go I've probably used three or four different ones they all have a different API it changes every time and once you finally learn how to do something beyond the basics you're looking it up in the documentation and you don't get to retain any of that so three to four years from now maybe you're using a different language and a different ORM well you're not going to be able to use all those things you learned about with active record there's going to be different structures there's going to be different gotchas there's going to be different things that oh if you do this in hibernate everything goes very badly if you do it in active record it's fine but the thing about SQL is that it's pretty much always the same so apps they come and go frameworks databases to SQL live on if you think this poem is incredibly cheesy you can thank Chris Nelson my friend back in the Cincinnati user group he is at super Chris please tweet at him and tell him how how great his his poetry is you do have to print out SQL the the unofficial way to make the the syllable count work out for the haiku there but so the real point of all of this is that you want to learn the native language wherever you are Richard hip he's the creator of SQLite he was on a podcast recently and he started talking about being a tourist and the thing that he said that really struck me was as a tourist I can get around by pointing at things and I can pick up a few keywords and I can communicate roughly with the people that are there but unless I know the language I can't start a business I can't really succeed on a personal level with people and have have good relationships I have to learn that native language and so by learning SQL which is the native language of any database that's going to help you transition from being a junior tourist programmer to someone who is a seasoned pro who knows when it's the right tool for the job so that's the main point today we'll look at a couple examples in order to do this we need to create a few things basic schema we will create authors and books we'll do this stuff in active record because active record is great for this kind of thing so we have an author which has many books we have a book which belongs to and then here's the migration so you can kind of see the fields we've got obviously an incomplete list from what you'd have in a real application but for authors we have a name a country an email and a birthdate and then for books we've got titles pages a publication date topics an ISBN number and most importantly an author ID so this is how inside the relation of the database the authors or the books are tied back to their corresponding authors so say you're on a new project or maybe it's an existing project and your your boss or your client wants some more information about the data that you have maybe they want to know how many books you have okay obviously very simple there's the SQL for that and likewise there's the very simple active record call that you would use to get the same thing how many authors do we have same thing simple select the count of IDs from the authors table and in active record you would just do author count okay so let's look at how we would type this into our database so if you were at your terminal you would type this depends on your database but if you're using Postgres which I would suggest you do Postgres is a great database but almost all of them have a command line tool like this we would launch it against our database and then we're dumped into yet another prompt where we can type things that are commands directly issued to the database and so we will type in our query and we'll get our results and so we've got 4,000 books likewise we'll do the same thing on our authors we'll type in our SQL query and we'll find out that we've got 500 of those okay that's all very basic but we need to get more in depth here we could have done all of that with just active record so how many books do we have in each topic okay that's getting more interesting we need to use a tool that we haven't used yet called group by so we'll select our topic and the count of those topics from our books and then we'll group it all together by topic and so what this is going to give us is a set of results that just includes one line for each of the topics that are in our database in account of all the books that go along with that topic does that make sense to everyone you could also do that in active record of course it's still simple but these remember are very simple cases as you get more complicated with these these queries and like the one we're going to do in just a minute by the time you figure out how to write the active record code to make this stuff happen you might as well have just done it directly in the database so let's look at something a little more complicated so let's ask how many books do we have in each topic and let's find the minimum maximum and mean page count by topic okay hmm I don't know how we would do that in active record you might have to inline some SQL to be able to do that so we'll start off with our SQL query select topic the count of the topic and we'll give it a familiar name for us using that as keyword and then we'll select the minimum pages this is using minimum which is a function that our database provides to us the database provides lots and lots of these kinds of handy functions that you can use to do things like minimums maximums averages standard deviations all sorts of statistical functions and other calculations summations so then we'll get our max pages and then we'll get our average pages and we'll round it off to two decimal places so you know so it looks reasonable again we'll get it from our books and we'll group it by topic again and when we look at our results you'll see that we've got our topics our topic count that's the number of books for each topic and then we've got the calculations that we did on the pages that are in our books now are you gonna be calculating number of pages and books probably not but you probably have users hitting your systems and you're probably tracking events that those users are creating or you're tracking you know the number of likes a user gives to something and someone might come to you one day and say well hey can you give me some basic statistics on this stuff and that stuff might be scattered into a bunch of different tables that you need to to join together and use these functions and let the database do the do the work for you rather than trying to pull all of that data into your Rails app and do the work in Ruby because again you might be dealing with millions and millions of rows here and it's not feasible to pull all that data down locally so let's talk a little bit about how we would join data together so how can we answer the question which authors have written five or more books on a given topic that's an interesting business question because you might want to contact those authors and say oh hey you're really on the ball cranking out these Ruby books you know do you want to come talk at our Ruby conference or something like that that's an interesting business question and that's where you're gonna have to combine your different data together and this is the sort of thing that's a lot easier to explore and do directly within SQL than it is to do within Rails so to quickly explain joins for those who are unfamiliar if you have authors over here in books over here a join combines those two tables together based on some criteria and in our example the criteria is that the author has an ID the book has an author ID in it and if those fields are equal then that's how we know that those records should be linked together that's the essence of a join okay so how do we realize this in SQL so we have the author's name a topic account of the books because remember we need to figure out how many books has this author cranked out we're gonna select from the authors table and then we need to join it to the books table and that's how we specify our join criteria there so we're gonna say we're gonna select this stuff from the author's table join together with the books table based on this criteria and then we'll group these things by the author's name and then the topic so the same group by concept we had before but now we're just extending it a little bit and using two different criteria two different fields in that join and then we'll say well we want to find just the authors who have written more than four books five or more or four or more is there five or more is what we're looking for so we're gonna use greater than four having is the same as a where clause which I'm sure you're familiar with in a in a very basic query select the user where ID equals blah right so a having clause is the exact same thing as a where only it applies to the things that are grouped together so since the authors are what's being aggregated here we're going to be calculating that we're gonna be using that the having clause to limit our result set of the total number of books for each of those authors and there we get the data so of the you know 500 authors that we have in our in our test data here we only have a handful of them who have written more than four books and there's the topics that they've written things on unfortunately Ruby didn't show up nor did rails but that is the nature of randomness the test data did not have that many authors writing Ruby and rails books at least multiple books back to back so that's pretty cool and obviously you can take that query and you can build it from there you can take these queries and you can nest them together so you can take the results of one and then apply it to the search criteria that another another query is using we could have joined in another table here say it's a table of sales data so instead of just asking how many authors have written five or more books on a given topic how many authors have written five or more books on a given topic and have sold more than a thousand copies of each of those books those are the kind of questions that you can answer and by understanding how to write SQL you can very easily come up with those kinds of those kind of things on the fly so joining data is it's obviously a thing that you're gonna do a lot of we just covered a very basic join there the kind where you have just the records that match up you'll see that there's a lot of other types of joins we're not gonna get into all the details of those but suffice it to say there are ways to say well give me everything that just matches and then there's other ways to say things like okay well give me everything that matches plus everything over here that doesn't match so maybe some of the authors don't have books with specific topics that you're looking for you could also include those authors if you wanted to things like that and that's all a matter of the syntax and the different rules that you apply inside your query okay so let's talk a little bit about a tool called explain aside from doing like ad hoc searches in your database and exploring data the other reason you might want to have some knowledge of how the database is working behind the scenes is that you can figure out why your app is so slow I'm sure we've all run into the situation where you work on something it works great on your laptop and then you deploy it to production and it's not so great it's very slow on your laptop it's fine because for one you probably have an SSD so that's super fast compared to the spinning disc that might be in your database server and on top of that on your day on your laptop you might have a hundred rows and on the production database you might have a hundred thousand that's a totally different ball game when it comes to searching through things in the database so explain is a tool that will let you understand how is the database trying to find this information what's its plan and it's really easy to run explain on literally any query all you have to do is take the query that you want to test and stick the word explain in front of it optionally on some databases you can write explain analyze and that will go the extra step of actually executing the query and giving you a little bit more information on well explain told me what I think I'm gonna do as the database this is my plan for how I'm gonna find this data and this is how I think it's gonna go explain analyze says here's my plan and then I actually executed my plan and this is what really happened so I tend to use the analyze just because it gives you some more interesting information and what you get from explain is output that looks like this which is extremely dense and I do not expect you to walk out if you're understanding how to read except for one key word or two key words I guess sequential scan anywhere you see sequential scan in an explain from your database that's probably a bad thing and you want to avoid at all costs a sequential scan a C sequential scan basically means the database has to look through every single row in the database one by one and see if it finds what you've told it to look for that's very slow that's as if you were trying to find all of the words rails in a given textbook and if you were trying to do that in real life how would you do it you would use the index in the back of the book and if you had the index you could say oh look well rails is mentioned on you know pages x y and z the database needs the same kind of thing you can add an index very easily to your database and that will help to resolve these issues where you have a sequential scan now there is definitely an art to creating indexes that matter and indexes that improve performance we can't get into the details of those today but suffice it to say the first step in all of this is running explain on queries that are slow or any queries for that matter in your database and just looking for those keywords things like sequential scan oh that's that's a hot spot there I probably want to look to into adding an index on that even if it's not slow on my development laptop rails will dump the output it will dump the actual sequel that's being run into the development logs when you're using your application in development mode so you can just go into your log file and anytime you see the word select you can go ahead and copy that stick it into your database console tack the word explain on to the front of it and check out the output it's a great way to learn more about how the database works honestly because you can kind of get into the database's head of like how are you going to find this information so in short run explain on everything so you might have noticed that I didn't include any mention of how do I directly call sequel from my rails application that's because it's dangerous you can definitely do that and you will do that but to get up here and say well this is how you do it and how you do it safely in a you know 30 seconds that we're on the slide would be a little bit reckless I think and irresponsible because there's a lot of there there's a lot of ifs and edge cases there that you could end up causing problems with and I wouldn't want to lead you in that direction but if you're doing reporting type of queries where you're not using data that's provided by the user that's probably fine you can use that in your app if you do need to incorporate some user data like say you want to do a geographic search so you want to find all of the coffee shops that are nearby a certain location and you've decided to implement this query using raw SQL that's fine but make sure that you are sanitizing those inputs before you stick it into your query you don't want to end up like the school where a little Bobby tables went mom gets phone call says hey it's your son's school we're having some computer problems did you name your son Robert quote semicolon dash dash drop tables yeah we call him little Bobby tables so that's why you have to sanitize your user inputs you never know when little Bobby tables is gonna enroll at your school and start using your application so where do you go from here there's obviously a lot to this sequel stuff if you don't have a book on your desk at work or at home that is about databases and SQL you should absolutely pick one up so those are that's not a list of recommendations or anything that is just there are sequel books out there and they are ranked by reviews pick one you will be able to keep it for a very long time because SQL doesn't change that much there is also gonna be good documentation for your specific database where you can learn about things like the aggregation functions like geographic extensions and things like that that might be specific to your particular database and usually the docs are pretty good for those Postgres has great documentation remote pairing not necessarily remote pairing but pairing in general is I think the best way for a junior developer to level up quickly working side-by-side even with other junior developers working together trying to solve a problem but especially if you're working side-by-side with someone more senior that is a fantastic way to learn very quickly things that you might not necessarily pick up in a book because most likely you're gonna be working on a real production system with real production problems and they aren't going to be demonstrations of two tables with you know five attributes in each one it's gonna be real data real problems in real systems and that's when you learn so absolutely pair program as much as you can and if you don't have an opportunity to do that there are a lot of places where you can do that online and and remotely pair with people on things like screen hero and stuff like that and just because I wanted to squeeze in one more Jeff here is a reminder that you should scour your logs for database queries understand what they do run explain on them and figure out how your application is really working behind the scenes so the takeaways the main one is learn the native language whether you're talking about travel or programming it's it's very helpful to know and speak the native language of the database understand what active record is doing for you behind the scenes and by all means pick the right tool for the job I use active record all the time it is a great tool but I know when to jump in and just use the database directly and just because you have an ORM does not mean that it's wrong or bad to use SQL directly you have a lot of tools and you just need to know what they're good at and what they're not good at and when to pick the one for for the job that you have at hand so thank you very much and if you have any questions I'd be happy to take them we have a couple minutes thank you my recommendation is don't do it without knowing what you're doing you mean executing raw SQL from within your Rails app there's a few different ways to do it basically the one way you can do it is you can get a connection the from the from the active record connection base you can get a connection you can execute raw SQL that's very helpful if you're using it inside of a migration say you need to create a geography type of column in a postgres database if if rails doesn't know how to create a geography column it can't use the normal scheme of migration stuff and so you might need to execute SQL directly so that's the time when you'd want to use connection execute the other time you might want to do it or the other way you might want to do it is using find by SQL which is a method on your model this is the one where you need to be very careful you need to be careful anyway but you need to be careful that you aren't just you know throwing user data at this thing but the nice thing about using find by SQL is that it will automatically instantiate objects for you that are of the type that you called it on so if you have authors and you said author dot find by SQL and you provided it with a query then you will get back author objects and they will have for any parameters that you didn't specify or that aren't really parameters on your model naturally things that you might have created in your query by hand there like some of our count columns and stuff those will come back as virtual attributes attached to your model and so you can just make the calls directly on them just like any other attribute on your model so any questions well I don't have a specific example for that for when table rewrites are necessary right well anytime you have a migration like that you need to be careful that you're not going to like take your app down a good example of a migration that you could add directly with SQL would be if in in Postgres you can create indexes concurrently but at least as of recently you might be able to in five but you couldn't just do that as a part of a normal migration and so you need to kind of create your your migration by hand and you need to say okay I'm gonna create this index and you would use all the same things that that Rails is gonna do for you when it's creating the index but you get to tack one keyword onto the query that says create index concurrently and then the rest of the the parameters there and so then when that executes instead of the rails generated SQL to create the index it will use the string that you provided and it will allow you to create an index on a table that doesn't block access to the table like the normal non concurrent create index does so that's when one example of when you might use it in a migration all right this will be the last one it's not necessarily faster so the question is is it faster to write the SQL directly it's not necessarily faster and you can observe this when you're looking at how your application operates all of those things that you ask ActiveRecord to do translate directly into SQL queries that get executed against the database and usually ActiveRecord does the right thing there are some times when you might find a better way to skin the cat for lack of a better word and that's when you might want to step in and use some raw SQL but typically ActiveRecord does the right thing there the key is to just look at what ActiveRecord is doing and try to learn from it all right thanks a lot