 So, welcome to today's PostgreSQL conference webinar, building a PostgreSQL backed website quickly. We're joined by John Ashmeade, database developer and DBA at Nistica, who will discuss using Ruby on Rails to build a PostgreSQL backed website, what to watch out for, which tasks to leave to the framework, what to do on the database side, and the various stages of development. My name is Lindsay Hoover, I'm one of the PostgreSQL conference organizers, and I'll be your moderator for this webinar, a little bit about your speaker. So John's been working with relational databases since the 80s, building and enhancing databases in SQL Server, Oracle, InformX, Ingress, and PostgreSQL. For the last five years, he's been a DBA and database developer at a leading manufacturer of optical switches, and John is in charge of the care and feeding of the manufacturing database. For the last year, he's been maintaining a manufacturing database to a factory in Zuhai, China. Welcome, John. So with that, I'm going to hand it off. Take it away, John. Okay. Hi, guys. I'm looking forward to doing this talk. I learned a lot putting it together, and I hope you'll have some useful takeaways from the talk as we go through it. Our objective is to learn how to build a PostgreSQL-backed website quickly. You might be wanting to get a trial balloon up just to see what happens. You might be at the beginning of some venture and want to get started with your website without doing too much work on that, or you simply might want to know how a client server works soup to nuts, and we should be able to take you off to a good start on all three of these goals. The approach I'm going to use here is to focus on doing this with Ruby on Rails, which I'm expert on and which is an extremely good tool for building a website quickly. It's so helpful. In fact, it's sometimes even too helpful, and we'll discuss when you have to watch that. If you can see the URL on the screen, you can go there, but if you look for getting started with Rails, you'll get to the site as the third item on your first search page. I looked around for good introductions to how to do this, and I found that the best one for our purposes is the tutorial on the website itself. It's simple, it's very well organized, it gets you off to a quick start. But before we look at the tutorial itself, we need to look at what's actually going on here. This is an overview of the talk. We've got five sections to this talk. First, we're going to look at what is model view controller. I'm sure most of you have already at least heard the term, but we're going to explain what this is and why this is critical to building websites quickly. Then we're going to look at a typical workflow, starting with the users and the use cases you might deal with and going through what you do on the database side and on the views and so forth. Then we'll take a quick look at the tutorial. We don't have enough time to do the tutorial. In any case, the tutorial does a great job of doing itself, but what I want to do is show you how to get started with it, how to do your necessary setup, what kind of things to watch out for, and what questions to address as we're working through the tutorial. After we've given you a good foundation for that, I want to pull back and look at the relationship between Ruby on Rails and Postgres SQL. Most of the material on building a website focuses on the framework, which is Ruby on Rails in our case, and they don't necessarily spend a lot of time on the relationship and how to manage the transactions between the framework and the database. We're going to look at that, and then we'll look at best practices and how to get your pyramid up smoothly and correctly. So okay, an overview of model, view, and controller. There are a fair number of pieces here. We have the client side, which is usually just a browser, and we have the server side, which is where the router, controller, model, database live, and where the views are built. We're going to go through each part of this system in enough detail to get you started. But as an overview, you're sitting on a browser, you've been given a URL, like, say, getting started with Rails, and you put the URL on the browser, and it navigates through the internet, and if it hits a Ruby site, it gets handed to a router. The router checks the URL to make sure it understands this. It will send you an error if it doesn't. And then does Ruby calls, Ruby is a scripting language, to something called the controller, which is the main piece of code on the Ruby on Rails framework. The controller uses something called Active Record to talk to models, which are front ends to the database, and the models use SQL to talk to the database itself. The controller is also able to use SQL to talk directly to the database for more complicated tasks and for exceptions. Once the controller has got its data put together and all lined up, it uses a language called embedded Ruby, ERB, to build a view and passes the view, which is a clump of HTML cascading style sheets and JavaScript back to the browser, which then renders it. And that's the basic picture. And this is an extremely typical way to do stuff. We're doing Ruby on Rails here, but a lot of the Python frameworks do something quite similar, and I think Java does that as well. It's a very common way to do it, and there's an excellent reason why this approach is preferred. And that's something called the restful architecture. It's hard to believe that this was invented by one guy, Ray Fielding, I think it was, in the year 2000. And he said, look, most of what we need to do on the web, or certainly a lot of it, has to do with somebody on a client side wanting to get a hold of a resource and somebody on the server side serving it up, serving the resource up to them. So the client might want to get a resource or might want to change the resource with a post or to replace that resource with something else or to update the resource, that's a patch, or to just get rid of the resource. And resource could be anything you like. It's usually, it's often some kind of a web page. It could be a pile of PDFs. It could be on GitHub, a program code. It doesn't really matter that much what the resource is for this model. And the great advantage of a restful architecture is that it creates a clear division of responsibilities between the client, the browser, and the server. So the server can just worry about satisfying all these calls and not have to worry whether the browser is Firefox or Safari or Chrome or what have you, not have to worry even if the client is actually a terminal or another piece of software. It just has to attend to its business and get on with it. That's a major win from a development point of view and why this model has taken over the Internet. Now, when I'm getting started building a website or adding a new feature to our website or whatever, I usually like to start with the use cases. Who's going to be using this thing? What are they going to be doing with it? And usually one of the first things that you think about is just what is my resource, which could be in my case, would be manufacturing specs or a page on how to assemble a optical fiber card or what have you. So the first task, the users will want us to locate the resource and there might be a search page or just an index or whatever. Then they need to do maybe a little bit of adding and editing. They'll have to log in for security reasons. Usually there might be a manager, there might be a programmer, there might be a data scientist, but it'll start off with a bundle of use cases like three or 10 or whatever, not too many or too few. And these serve as the basic checklist at every point. If I have done the right thing for every one of my use cases, then I have a working system if the use cases are complete. Otherwise, some more work still to be done. Often when we're building the website, we focus on the first three, just the regular user, and we forget that we have to manage the interactions. We have to hunt down bugs, which are unavailable, and we may want to use the data for other purposes. So we want to think about at least a little the basic use cases. Now, use cases map very quickly into the development model with Ruby on Rails because for each use case, you imagine you're going to type in an associated URL like an index or whatever, and then the router is going to figure out what to do with this. This is a sample from the tutorial of the four line router for the first set of pages about articles. There are sample databases of articles and comments, and they say that your starting point is index of articles, that's logical, and we want to access articles in a restful way. Basically, the router is using what's called a DSL, a domain specific language. That's a special language built for this one problem. Actually, DSLs are an extraordinarily powerful tool for building software. Instead of writing code to solve the problem, write a language to solve the problem in which the solution of the problem is easy to do. After all, that's what HTML is, DSL, what CSS, what JavaScript, and so forth all are. In the Ruby on Rails router, this is what you get by default on the bottom left, that simple word resources for articles gave you URLs that will find an article, create a new article, edit, that's the post thing, post and get, update an article and delete an article. So you got a ratio of about eight or 10 to one on your code. That's one of the things that frameworks are really good for. You're writing 10 lines of code for one line of your code, which means you're 10 times as fast or whatever. In the router, you can include double checks to make sure that if your articles are identified by ID number, somebody can't put in an article with the name potato, which is not an ID, and fix other things like that. And in my routers, I like to have a cleanup route at the very bottom, which is for exception trapping. And if we don't mention anything else, we'll send them to our home page. The next thing I like to look at when I'm building a site or fixing one are the models. Models are the front end to tables in the database. They are wrappers for tables. So usually the rails will talk to the table, not directly, but via the models. And this way, again, you get this one line of programmer code into about 10 lines of SQL ratio Ruby, Ruby on Rails models, really part of their active record subsystem are pretty easy to put together. This top line in green is the command used to build the comments table in the tutorial. Rails is the starting point. You're going to generate a model called comment. It will have the field commenter, the field body, and it references the article. Comments are child of the article table. The comment.rb model in Ruby on Rails is really short. You've got the class comment, it inherits from application record. Ruby is an object-oriented language, so it does everything by inheritance, same general pattern as, say, Java or Python. Notice how short this file is. You're looking on the Ruby side, you say, how can three lines of code give me a whole table? Well, this is where the cleverness comes in. The first time you try to use the comment model in Ruby on Rails session, active record, ar, goes to PG catalog, which it knows about, and looks up all the characteristics of the comment table. And so it's during that lookup phase that it learns what the fields are in the comment, and that's why it doesn't need them here. And I recommend to you, as you go through the tutorial, keep open a terminal, set your Postgres server to log every SQL and watch the SQL as your various commands are run. And you'll see a lot of really interesting stuff going on. There's an enormous amount of stuff going on under the hood. And when you're getting started, it's useful to learn about this. By the way, active record doesn't do this every time you open up a comment record. It only does it the first time and then it caches it. Once you've built, set up your comment table, you have to run a DB migrate command, which is actually do it. And in the tutorial, you can look at the commands that actually build the table. And when Rails builds the table, it's going to do a lot of extra stuff for you. First off, it gives every table an ID, which will be your primary key. Then you see the two fields that were mentioned. Then you see article ID, which references the article table. And then you see two timestamps, one for when the record is created, another for when it's updated. And Ruby takes care of the ID, created at and updated for you. It adds the primary key here and it adds the foreign key looking into the articles. When I first started out with Ruby on Rails, a much earlier version, it didn't necessarily build all of the structure it needed. It wouldn't build the index that goes along. There should always be an index on the article ID. And that version of Rails didn't do that. And I was pleased to see that the latest Rails does take care of this and a bit more besides. So that's the underlying structure. And I usually like to build my database first and then build stuff on top of it. Now the heart of a Ruby on Rails application is the controller, which you can kind of guess from the name. This is a little bit of controller from the tutorial, just a sample. First off, it's part of the class hierarchy. It starts with application controller and then does stuff specialized for articles. We want to look here at the starting point for a new article. There's a function called new and a function called create. And you think you might need only one function. In fact, the new function sets you up. It doesn't actually talk to the database, which is interesting. If you say I want a new article in this framework, you'll get the view, the stuff you need to set that up, but the database won't hear about this until you hit submit. When you hit submit, the method create will be called. The database will check that the parameters you sent back, this is some useful screening code that makes sure you only set back things that you were allowed to send back. And if it likes everything, it will save the article and then send you to the article's own page, to an edit page for the article. If the save fails, well, what the save does is it does an SQL call into the database with all this extra screening here that will probably work. But if it doesn't, it will just go back to the new page with a few error messages showing up on it. So that's the basic riddle. You get a setup method and an update method. You get a bunch of utility methods to screen it or to help do things. You have mechanisms in Ruby on Rails that let two controllers share methods with each other. You have to plan that out a bit, but it's very powerful and you get helper tools that let the controller share information with the view. OK, so the next big chunk is the view. This is again a whole pile of domain specific languages, including primarily embedded Ruby. If you've worked on PHP, you're familiar with the rhythm of a page like this. This is HTML. You can see that we've got a new article here. Then we have this percent equals. This means that Ruby is being told from the percent equals to the end of the percent equals you do work. And Ruby will call up its form with model using the article model. And this at article is the data structure that was set up in the controller. At is a keyword meaning attribute. So this is all the records on that article, but for a new guy, they won't have anything in them. This is another bit of Ruby. You see the embedded stuff on the left and right. It's going to put the title in here. Just the word title break. Then it's going to put in a text field, which goes to the field title. And then it's got some air handling here. And this is really kind of interesting because you're getting an enormous amount of HTML code being generated for you. This is the second field on the article table, which is called body and any errors about the body are going to show up here. So the errors are right next to the problem that created it, and this will generate a submit call when you hit submit. I'm not going to show you this here, but when you work through the tutorial, be very careful to take a look at the HTML, which is being generated, your browser, no matter what it is, is going to have a developer tools menu on it. Bring up developer tools, look at your real page on the left, and at the developer tools, which will let you look at the HTML, the JavaScript and so forth. And don't just breathe through the tutorial, but like savor it, see what it's doing for you and whether you like what it's doing and whether you understand it. And this will give you a firm foundation for when you have problems or need to add stuff and so forth. So there's like a rhythm to working with a tutorial. You do what it says, and it's very helpful, but you take a look under the hood at every statement. Now, Ruby or Ruby has the Ruby community has a couple of key philosophies which are which show up in views and everywhere. One is convention over configuration. Instead of telling Ruby and Rails how to do stuff, you do it their way and then it knows how to figure things out. Like this article is in the singular because it refers to a single article. If you did a set, you'd see something like articles, and that's really cute. And eventually you get used to that. Furthermore, at every point in Ruby on Rails, you can share code. So controllers have concerns or mix-ins to share with other controllers and views have something called partials, which are like little bits of views. They're like a little square area on the form where two views will share common code and only make daily use of partials. Ruby will also integrate unobtrusive JavaScript. For instance, we've all written code that says you really want to delete that confirm and you'll bring up some JavaScript to do that. Ruby has syntax that lets you put a confirmed question on the page without you doing actual work. It will compile cascading style sheets so you can have a master style sheet for your site and then sub style sheets for each part of it. So you want to internalize that. Don't repeat yourself. DRY, which is a Ruby mantra. OK, so now we've completed a quick review, very quick review of the essential parts of model view controller architecture. Those are the main parts of that original diagram. Now we're going to the center of this talk, getting started with the tutorial. I'm not going to do the tutorial. I looked at it, you know, over the last few weeks, and it's really good. It does things step by step, but it's a little over focused on getting the page up and doesn't give you enough information about the background. And we're going to look at how you set up Ruby, supporting tools, the classic Hello Rails and a bit about workflow and what you do after you've done the tutorial. So you can think of this talk as a kind of client server with respect to the tutorial. This talk is the client. The tutorial is the server. Now, before we can turn on Ruby on Rails, we have to turn on Ruby. I found a very helpful site. I'll see if I can pronounce this correctly. Havier Julio.com R-B-E-N-V. And I suggest going to this and just having a look at it. He gives very good instructions about how to get a tool called Ruby environment or R-B-E-N-V up. There are two major tools for setting up Ruby and Ruby on Rails, R-B-E-N-V or R-V-M. I use R-V-M at work all the time and time and it's you're perfectly good, but I chose R-B-E-N-V and the tutorial chose R-B-E-N-V because it just has a cleaner interface and it's a little easier to deal with. But you can pick either. Julio explains how to clone R-B-E-N-V from GitHub and then to install it, you type in R-B-E-N-V install and I'm recommending Ruby version 274. I'm being quite specific about it because it's the latest really solid Ruby version in the two series. There's a Ruby 3.0 which is out now and I don't know if you've heard the proverb don't use .o of anything. Ruby 3.0 will probably work most of the time, but unless you're an enthusiast, you might want to wait for Mr. 3.1. So 274 is a very specific recommendation here and it will help you have a better time doing this. When R-B-E-N-V installed Ruby, and it doesn't say install Ruby because that's what R-B-E-N-V does unless you say something else, it installed also a program called gem. Gem is a Ruby gem. That's what they call their libraries and it's a gem for installing gem. So it's kind of a meta gem. So you go gem install Rails and that will install Rails 6.1. It will always install the latest solid version unless you say otherwise. Notice that this is Rails 6.1, not 6.0. If you're still on 6.0, I'd be recommending a five something. At this point, we actually have a working set up sitting on your server, whether it's Windows, Linux or Macintosh. So I think this is pretty brilliant. Now you're going to need a few more tools before you get into this. You don't absolutely have to have a software configuration manager here, but you really want to do this. It's a service to yourself. And I've used Git and Subversion. I use Subversion at work and Git for my own project, both our first rate tools. If you're undecided between the two, I'd go with Git because I don't make it easier to push your stuff up to the internet. You need a bunch of internal tools. The instructions for getting them are in the tutorial. Yarn is to work out dependencies. It's like a Ruby centric version of Mac, which figures out what the dependencies are. Node is a JavaScript support tool which is needed for a lot of the JavaScript support tools in Rails. Rails is actually almost a hybrid of Ruby and JavaScript at this point. SQLite is a little baby database, just one table at a time, which is used a lot in cell phones. It's very lightweight, so all sorts of code will use a small SQLite database inside itself. We're doing Postgres here, but you need to be aware that these things are floating around that are often useful. In fact, your own client browser, your Firefox or whatever, is maybe using SQLite to manage internal data stores on your machine. And then you need to talk to Postgres. You can use PgAdmin, which is highly focused on Postgres completely and very powerful to do all kinds of wonderful statistics and monitoring stuff, or Dbeaver, which can talk to Postgres perfectly well, and talk to a SQL server and MySQL and so forth, if you're in a multi-database setup. So now we've done our prep and we are now already at the middle of this talk for the core of this talk. Hello, Rails. You knew this was coming. And we can get to a server running on your server in four lines, which is awesome. First, you do Rails new blog. They use blog for the name of your site. It would be whatever your site's name is. Notice in red, the minus D, Postgres SQL. Do that. You don't want to go, the default is SQLite. Don't do that. Just minus D, Postgres SQL. Then you CD to the blog directory, which is created for you. That directory is full of files and subdirectories. Take a look around. If you become a Rails developer, you'll be spending a lot of time navigating that tree. But in fact, I usually keep a couple of terminal windows open at the same time to parts of that tree. Then you type in bin Rails server. The bin Rails is a little tricky. One of the subdirectories in blog is bin. That has a front end of Rails, which is modified for your particular website. You type in Rails server, and now you're running a web server, believe it or not. That's great. Then you take your browser and you navigate to this location, slash 127.0.0.1 is also called local host and port 3000 is the Rails port by convention. You just did four things. Let's see what happens. You get this wonderful welcoming page. Isn't that cute? We haven't actually done anything specific. You can see at the bottom that I'm running this thing on the Mac from the Darwin and I'm running Rails 614 and so forth. That's very good to show that your web server is up. Now we want to actually get out the traditional Hello Rails. We can do this with a little bit of work. It's detailed in the tutorial. As you might expect, first we have to fix up the router a little bit. I'm not putting the instructions here, but you'll need to set up a route for this. So you can route to your Hello page. Then you have to fix the database configuration. I usually do, I change the username and the database. I'm thinking of building a Postgres links website. So I've created an identity on my Mac PG links. And that's nice because I'm using .pgpass, that's .pgpass, which is a wonderful tool. I don't need to put the password in here. So I avoid that security violation of putting a password in a generally readable file. Now I had a little bit of trouble when I was setting this up because I was on a Mac running Postgres 13. I got a whole spew of error messages when I put my first article in, about 4,000 in a row, which is a lot. And with a little bit of work with a search engine, I used ..go, I found that the problem was that something called GSS, which is a security system, doesn't work well on a Mac between Rails and PG12 and higher. Since I don't need it on my own server, I just disable that. I would say that as you walk through the tutorial, you have a good chance of finding one or more spots where it simply won't work the way you expected. If you have a good understanding of what's going on and just look at the error messages with a search engine and because Rails is a solid product and Postgres is and presumably your platform is as well, it shouldn't take too long to see that error message and then to find out what to do about it. This is pretty much guaranteed to work for the general case. Then after you've done that, you have to generate an articles model. I showed you the generate command earlier for the comments. It's the same thing. You just type in Rails, model, generate, article, a few fields and your dot and you have to migrate the thing so it actually is created. And then the fun bit, you go to a page called app views articles index.html.erb. App is a sub folder of that blog directory where everything lives. That's the application. Views are the views. We're doing model view controller. What else is new? The articles are the articles and index is the index page. Erb means that this is an embedded Ruby file and Rails will take care of stripping off the dot erb and expanding to create the index.html which the client is going to see. That means that to the browser, it doesn't know about erb. It just knows index.html. You're going to create, sorry, one line in that HTML which just says hello Rails and we jumped ahead a little bit and that's what you get back. So that's your basic setup. Hello, type in the hello Rails, get back hello Rails. If you've left that web server running, you just refresh the page and you see this. If not restart the web server, Rails server and then reload the page and you'll see this. The first guy who came up with hello Rails, hello world, a Kernigan and Richie and their wonderful book on C programming said that getting to the hello world stage means that all of your framework is working and you've accomplished a great deal of setup. That means you can now start without going back and forth between is my compiler okay or my assembler or my pass set up. You're ready to do useful work. This is a pretty Spartan page. It needs a wrapper, some CSS, all sorts of real stuff, but it means you basically have stuff working. And that's fantastic. And I love the fact that the control of the tutorial only takes about five edits to get you to this point. On for C code, you have one or two, but for MVC and for a website five is pretty fast. Okay, so I'm not going to carry you through the tutorial. That's the tutorials job, but I'm going to show you what to watch for as you go through the tutorial. First off, you take the sample code. In fact, they make it really easy to cut and paste the stuff. There's something to be said by the way for hand typing the code to make sure you really understand it, but you can use cut and paste. And then if you remember Ronald Reagan's own motto, motto, trust but verify, it works the way the tutorial said, but look under the hood, run PG dump to see what the table actually looks like. Look at the PG log to see what SQL was generated. Look at the HTML and your browser's development tools to see what that's going. And PG admin, you can watch the whole Megulol at once. Rails has its own log, which will show you the SQL. It will show you every view being loaded, all the parameters and give you the times. It's brilliant. And stare at this thing. I often leave a terminal session open doing in Linux terms, a tail minus half to see each new line as it comes in. You can also intersperse debugging messages using the logger object and the info method. Remember object oriented. So this is the object. That's the method. Hey, then the browser development tools. And I'm going to look at these things and they are astonishing that the browser will let you look at the complete structure of the HTML at the JavaScript, the CSS in your JavaScript, you'll probably put in console dot log and console dot air to tell you about key events. Generally, I like the beginning and end of a JavaScript function for targeted error messages. The browser tools will also let you watch the network, watch files being pulled in. And then if you are really hardcore, you can look at every packet. You can actually bring up wire shark on your server and stare at all postgres packets. That's a recognized convention on this. And I mentioned this partly because I spent the last two weeks staring at packets on our work network to deal with some latency problems. That's not fun, but necessary. So you have a lot of extraordinarily powerful tools, and I encourage you to take a relaxed view of the tutorial. Don't rush to the end, but savor it. If you can savor a tutorial, when you get to the end of it, we'll have a perfectly working website with articles and comments on it, but it won't really meet modern standards for a website. What we do nowadays is called single page architecture. You go to a page, you click on stuff, and you don't move to another page. You stay on that page. And secretly Ajax calls go out to the server, you know, over to the server, over to the controller. And then the controller will send you a quick message back with Jason. Jason is like frozen JavaScript. It's a hierarchical another DSL, which can convey a lot of information in an easy to read and compact format. Your web page gets the Jason decodes it and does stuff based on that like do checks, report error messages and additional information. You can frame the page and layout so you can have a common layout for your site. You can build lots of JavaScript, which does useful stuff. By the way, JavaScript is a tricky language to write correct code in. And that would be a whole talk of itself again, simply to get started. There are wonderful libraries like jQuery for moving stuff around and bootstrap for running a database on your client. And there's a tool called JS hint, which will look at your JavaScript and say nasty things to you like you didn't define that variable before you used it. That was sloppy. You can use compiled CSS rails as nuts for automated tests. I use a few of them at work, not as many as I should, quite frankly. And if you want to have actual users, if you're not on an intranet where you control things through a firewall, do not try to write your own user and password modules. The black hats do this stuff full time and will cut right through whatever new be mistakes you make on it. Pull down one of the numerous rails modules on how to handle users and passwords and just install that and obey the orders. Yes, sir. Yes, sir. Don't build password code yourself. It's way harder than you might think versus one of the things that goes wrong is that because rails is doing a lot of logging, it will actually log the user passwords into the rail log. You didn't want that to happen. The ways to avoid that, but you have to know. Furthermore, you need to upload all this to the web, but we haven't covered that again, a whole talk in itself, but you can use either Amazon Cloud Nine or any of the other cloud providers. You can go to a local ISP if you're within an intranet as I am during the workday. And then you probably have some simple procedure. Ruby on rails lets you have development mode and production mode. And I keep one server in development mode. Then when I'm done with the code, I save it into an SVN database and in production mode, I check it out from the SVN database. And then I do a restart. In development mode, rails is nice enough to load every payload, reload every page when you change it. In production mode, it doesn't do that, which is expensive. It does the reload when you tell it to. And that's a major win. So that way you can change five or 10 modules at once and reload them in a single instant rather than giving your unfortunate users a fragmentary view. They hate that and so do you. Okay, that concludes the tutorial section of this talk. Now we're on to a comparison of active record and postgres. What part should be, what things should be handled by the database and what things should be handled by active record? I apologize for the screenshot, but this is from one of my favorite movies, The Golden Voyage of Sinbad. If you notice the statue on the right, this was just lying sitting in an alcove binding its own business into a bunch of application developers showing up on the left here came in to see what was what. Then when they turned rails on, it suddenly came to light. And I think of the models as a table come to light. That when the table when the record is sitting in the database, it's just there quiet, biting its own business, being backed up, being watched over and cared for, but not doing stuff. And then when the call comes in from active record, the data, the database record is pulled into the controller into the view and comes to light. And this the swords could be the methods of your rest for architecture like select, insert, update, delete, and some fifth method, which is useful here. And you have the application programmer against the record come to life. Eventually, you deal with it and put it back to sleep. The dark figure in the back is of course the DBA or the database developer. Anyway, I really enjoyed the movie and you might enjoy it too. So the first thing about the advantage, we want to look at the advantages of using rails with Postgres, the advantages in particular of active record. When I started out seven or eight years ago with rails and Postgres, there wasn't that much support from Rails for Postgres. And the situation has gotten way better since then. And in fact, there's a whole page that you can look up and we'll tell you just how to deal between active record and Postgres. Active record is really good for rapid views. One of the most fun bits about active record is something called method missing. Let's say you have a field commenter and you want to search by the commenter in the comments table. And normally, if you're writing a website from scratch, you have to write the select star from comments where commenter equal or matches the parameter. But you don't have to do that on rails. What you do is write a method called find underscore by underscore commenter and call it. And you didn't write that method. You just wrote it down in accordance with the rules. And when Ruby hits that method or the method hits Ruby, it goes all the way up the object chain to the very object, the initial object at the top of the entire object chain, the egg from which all of all Ruby objects come up. It's called object. And it has a method called method missing. And if it doesn't know how to handle fine by commenter, it will go to active record and say, what do I do with this? An active record will say, aha, I know you're working on the comments table. I know the comments table has a field called commenter. I will create a search method on the commenter field right on the fly. And the next time somebody does this, I'll have that method ready, and you won't go all the way up to method missing. So this means that you can have hundreds and thousands of fields in your database, and you don't have to write search scripts for all of them. If you need more complicated searches, two fields at once and so forth, there's syntax to let you put them together with a little more trouble, but not much more. Furthermore, if you want to know what are my fields or what methods are currently available, Ruby is nuts for introspection, and there are functions that let you say what are all the functions, all the methods that I can use at this instant. Now active record now provides reasonable support for postgres. As I say, not always true. On the left, you'll see a list of all the data types supported as of the instant for postgres. And you see all the traditional types like this is your binary thing, arrays and so forth. You get strings and text, of course, without even worrying about it. Jason and Jason B are great, even composite types and network addresses. I don't see geo spatial here. And I don't see, of course, user constructed types. But you aren't likely in ordinary practice to need much more than this. And you can do things to cope with new types as well. So you've got your field types known to active records, you can specify them when you first create that table using the Rails generate model command. And that's great. Sometimes there are surprises. Because as I mentioned, Ruby works by convention. And by convention, it does things like let's say you want to count of all the articles, there'll be a pseudo field called articles underscore count, which is that count. If you'd like to call one of your fields underscore count, you could be in for a surprise. Active record is extremely helpful. Sometimes it's too helpful. I remember one favorite problem favorite and only in retrospect where there was an active record command that said I'm going to start here with the parent. And then I'm going to call up its children and its grandchildren and its great grandchildren. And actually, it's great, great grandchildren went four levels down. And for a single active record call, it generated 100,000 SQL calls I counted them. The result was that something should have been instant took 10 minutes. And that was active record being very helpful about fetching the children and the grandchildren. And that was a case where you want to write your own SQL and have one statement, which is infinitely faster. There are all sorts of statements, which features which really require direct SQL support. And you have to know when to cut over to that. Another example of this are time stamp columns. Rails will try fix up the timestamps for you, you do an update through rails, and it will fix the timestamp. But if you have other code also editing the records, you might be better off turning off that Rails feature and fixing the timestamps with a trigger. Okay. So potential problems. As I mentioned, the SQL can be suboptimal. Another problem I've seen a few times is that active record is, of course, talking to the database through some connections, by default, it sets up five connections and just keeps them open. That saves you having to open and drop a connection. It does a little bit of PT bouncer style pool management. But sometimes you run out of connections. You have to be aware of this is happening and be prepared to work around it up the number of connections, spread your code out, or you'll get a bottleneck at the connection pool. Okay. Active record does caching. If you edit a record on the inside, you have to tell active record to reload that record. If it's currently being looked at by a user. And of course, you have the users hammering the database as they always will. Active record, the cascade of Ruby on Rails, active record Ruby and Postgres can't deal with every new piece of ingenuity your users come up with. Well, that's a regular development programmers problem. And the great thing about these frameworks is that they do the routine work that's in common across websites, the stuff specific to your project you're responsible for. It has to be that way. So we are at the pen ultimate page. And we'll look quickly at some best practices for active record and Postgres. They give you a default exception page here. Okay, it's an exception page. And it tells you that something went wrong. I have a checklist for exception pages. Number one, reassure the user it's not his or her fault. Any exception is always the programmers fall. Tell the user what to do next like go to the homepage report to it. For that matter, just tell it send an email back to the developer with a stack trace that also and I think this is important, log the air to a table of error. So you can do analysis. Am I seeing a lot of a particular kind of error? And this is a very valuable technique. Usually, if you see like I have 10 errors at the same line, you can pretty much go to that line and say I know what the problem is. You want when using active record of the rails interface to databases to put an active record what is a good fit for active record stuff that's dynamic that live statute. You don't need to be shy about doing things like relational integrity in active record and in Postgres both. You can go to active record and say these fields have got something in them and go to Postgres and say it's not not. And this way, if some problem gets through one, it will be caught by the other. No harm. There's a lot of automated code checking for Ruby and for JavaScript and so forth, versus Ruby has the minus CW option. Check and warn. And I have this option set. So when I deploy something to production, it runs this check on every module and if a module fails this, which it shouldn't, but it can happen, it stops the replacement. And I have to fix it. You want to write any code that faces the database and begin rescue and which is Ruby for trap the exceptions. And all exceptions should send an email even if they don't create an exception page. I usually like to write the air handling first and then write the code. And this is a variation on test driven development TDD, where you first write the tests, and then you write the code, so that your tests will first go red because there's no code, and then hopefully they'll go green when you write the appropriate code. And you can keep the tests around as a regression suite. Okay. So we are now on the final page of this talk, best practices for site building in general. This is perhaps the most famous site building failure of all time, or at least the biggest. This is the bent pyramid. It's the second pyramid that was ever built four and a half thousand years ago. They decided to save money by not digging the sand out all the way to the basal underneath, because it was a lot of money to dig out the sand. As a result, when they tried to put stone on top of the sand, it turns out when you put enough weight on sand, sand turns into a liquid, and they began to see their little pyramid drift, or actually bigger pyramid. And obviously Faro doesn't care as he is pyramid floating around like a boat. And they had to scale back the plans and put the rest of the pyramid in at a reduced angle to have less weight on the sand. So you want to have a clear overall objective, have dug the sand out of the way. And I hope this talk will have helped you with that. Use each tool for what it is good for. Don't try to write data analysis tools in Postgres. Postgres is not Excel, but don't use a word as a database. Word is not Postgres. One of my big rules is throw the first one away. In fact, I followed that rule for this talk. The first version of this talk was way long and complicated. And I said, you know, I can get a much better talk by letting the tutorial do the step by step stuff and giving you the overview in the talk. So I'm following my own advice. When you put together a site, don't just think about your target architecture, but figure out what order you're going to build the stuff at. Build articles and comments first, some other stuff or do a different build order, but pick an order that lets you test and validate as you go up. Don't do everything. Don't build 10 tables at once and then hope the whole thing's going to work. It's not. Once it's up, make sure you plan to make it easy to fix up. You have lots of error messages and testing and so forth. And that should give you a nice, solid piece of work so you can be proud of that will provide real value to your users. And I hope this talk has provided some value to you guys. Okay, I'm going to take a look at questions now. But thanks for your attention. What have we got here, Lindsay? That was great. Now, we haven't gotten, I think we've only gotten one question. So now is the time to get them in if you have any. And the one question is, will the presentation be available in order to take the URLs? It will actually be up on my website. Probably by the end of today, that will only be the slides, not the actual recording. So that's our only question. And I figure with that. Thank you, John. So much for taking the time. This was really, this was really, really great and really great perspective. So I really do appreciate it. And to all of our attendees who spent a little bit of your day with us. Thank you so much. I hope to see you at future Postgres conference webinars. Cheers. Okay, thanks, Lindsay, for setting this up. Okay.