ER Modeling and pgModeler - My Own Little World

shut up and sit down good morning everyone time to get started let me turn off the funky beats all right good morning from a heavy marine layer santa cruz and for those of you who don't live on the pacific coast of the united states um when we say marine layer we mean that there's cold water off the coast interacting with the warm water on the land i mean the warm air on the land um creating this dense foggy morning so this since i live a couple miles in from the coast this will probably hopefully burn off at around burnoff meaning the sun will warm it up enough that we the marine layer won't have influence about three miles in at about noon ish but until then it's gonna be pretty darn foggy and dreary so hence the i don't know well hedy probably just got up because felix got up so we lost eddie she's not even here for the podcast today um today's tea is going to be a new one so after i showed last time when we talked about horny teas um i went to their website and i found that they have a whole bunch of box so loose leaf decafs so today i'm drinking a decaf darjeeling which is actually usually quite hard to find oh and i'm using my postgres open 2015 mug so five-year-old mug postgres mmm good um it's actually quite a good hearty and sons does a very nice job with their decaf blacks they're not really super weak and they actually have taste and the leaves were nice and big um let's see let me put up the stream so in case anybody says anything about my delicious teas we'll have that today we're going to be talking about entity relationship modeling entity relationship modeling um what entity relationship modeling is is a visual way to design your databases when i first started learning database technology so i am a gra i was a grad student for many years um in forestry and then ecology and evolutionary biology and then i made the switch over to it and then mostly in the research fields we learned we manage all of our data in um well back then was either quattro pro which was my favorite or excel which later took over because of anti-competitive practices by microsoft um but anyway we would do it all in a spreadsheet right we'd have spreadsheets some more advanced people some professors were using databases not to their full extent but in general in the research fields i think most people learn excel and they never learn databases which i think is a really sad i would like to change that i'll probably work in my life to change that to try to get people to use more databases um so anyway so when i moved them over to yale to do internal i.t for professors at yale i was kind of i lit up a program there that was basically a consulting branch for professors in the social sciences so we would do all sorts of projects for them and a lot of them have data management needs as most research projects do and that's when i started to start learning postgresql um we decided way back when so this is 2000 2001 that postgresql was the database for us because it had all the features it was acid compliant right which is what we really wanted we wanted the transactions and rolling data back and stuff which my sql didn't necessarily have at the time anyway long story short i get started to design databases and people are like well just sit down and start writing sql and i'm like are you kidding me i can't do that how do i understand what the relationship is between tables and everything so back then i would use vizio to do my database modeling but it only supported sql server so you would basically export the you do all the design modeling and visio then you export the sql and then you do a bunch of search and replaces to make it postgres compliant then after i left uh yale i didn't have really and then i went to esri and there i gave access to microsoft tools but then after that i didn't really have access to microsoft tools so i started using dia and dia is okay but it's really kludgy in terms of the pictures and um export and then eventually i found my pg modeler to do this for me and i love pg modeler so today we're gonna talk some about entity relationship because i don't know if everybody's done it before and then we're gonna demo it using pg modeler which is my favorite database modeling tool and uh that's it so and if you have questions again always ask them in the chat okay let's see so let's switch over to the full view there we go so this is pg modeler the beginning when we started it up that's the icon that they have for it uh pg modeler is an entity relationship modeling tool specific to postgres right so this won't work with others i mean you can model in here and then export the postgres sql that it's going to export and then adapt it if you want i think it's one of the better modeling tools period but so you might want to do that it's raphael has it in active development right now so there's a beta available but for today i'm going to be using uh 9.2 just because for demos no one likes to see beta crashes so let's go ahead and get started we're going to start with a completely new design today's design is going to be we're going to replace instagram because we i love instagram for sharing pictures and stuff but i don't like giving facebook my information and i need someone to replace an insta replace instagram so i'm going to do it so here we go we're going to make a new model so we're going to design our new databases and right away we get a blank screen a blank screen which looks normal like any graphic design program right we have some menus on the left there's a lot of right click context stuff that happens in here and then this up here is actually the name of the database that we want to create inside of postgres so we're going to change that because i know that uh i'm going to be calling it oh no we do it through this we do through properties right and we say we don't want new database i know the postgres database i'm going to make i already have the syntax command so i'm just going to call it workshop i'm going to use my normal docker docker container that i usually use um you can set your table spaces here you can set the owner of the database if you want here right you can do all these things that you would usually do like the collation type right and it knows all the different collation types that are built into postgres because this is actually built for postgres you can like limit the number of connections to the database you can change the encoding actually let's do that let's do utf-8 i hope that actually worked actually you know what i've never done that before so we're just going to leave it as default i don't like doing that for the first run through of the demo you can have any permission you can edit permissions on it you can have default objects right all that kind of stuff you say apply it changes the name there not so exciting in terms of what we've done visually now the first thing we're going to want to do is create some tables well so let's look at how we do that we can right click here right this is this and go under new and you can create new database objects so if we wanted to create like a schema we could create a schema here we can add extensions we can add users and roles we can do languages like plr and stuff like that foreign data wrappers all those things you can it understands all that stuff in postgres you can event design triggers here all that stuff we're not going to play with schemas or any of that right now instead what we're going to do is we're going to create things within the schema by default it assumes things are in the public schema right and so we're going to actually say we want a new table right and so it brings up this dialog i'm sorry if the fonts are really small um i could change the fonts on my machine but oh and by the way pg modeler let me show you this before we design the table uh where'd it go i don't want to be on this i want to be on this here's the website for it pgmodeler.i it's cross-platform which is another thing i love about it i can use it so if we go to download where's the download download right i can get it for windows linux and mac right it runs on all this platform i think he's using qt for it so we can basically use it on any platform which i love it's not windows only um it i end up buying it i like supporting open source projects this is one of those you can download the source code and you can compile the whole thing yourself if you want and run it but you can also buy it uh i have bought the where does it go but let's go back to home and then there was a purchase i bought the forever license right so you can just say i want it to never expire and then you'll get all the updates so one thing that you should know on today and i'll put this at the end raphael has made us a code called crunchy crunchy ftw and that should get you 20 off the purchase price right and i bought the never expires if you want to see how much the never expires costs that never expires is the discount that's not the true discount i don't think it has actually implied the one we want um because that's not 20 off right um but um i don't know if raphael's turned it on or not rafael or if you're in the chat can you see if you turned it on yet or not um anyway this means you have it for eternity and it's only 140 bucks right which is way cheaper than vizio would cost you and you can do the use it on all the platforms you want wherever you run your laptops or your desktops okay so that's how you get pg modeler pgmodeler.io then you're all set okay um let's go back to pg modeler again all right so we're going to make a table well the first thing we're going to need is people right this is usually how i design a table is a designer's database schema the the sequel for it is i usually define the objects first and then i go and fill in the columns and what goes where so i'm going to say people right is my first table i don't i'm just going to leave in the default tablespace i'm not going to give it an alias if i wanted to have comments in my sql code i can go these are the users right and that'll show up in the sql comments which will then also when you do a slash d plus where it describes the full stuff about a table that will show up in there in the postgres command line we're going to make a column call and so now we're going to make a new column in there again that was here under this little line symbol right here we're going to make a column it's name is going to be id i'm going to call it integer right you can actually start typing it it'll fill it in and then once it's filled in as much as you want you can just tab to the next whatever fields you want um one other thing you should know is it's got a lot of different types in here so you could actually scroll through if you're not sure and you can't remember what you actually want to call it right so we can call it integer oh and you can also see that it takes ranges and it takes vectors right so you can do vectors and ranges and you can do all sorts of other fun stuff here so let's do integer and then we're going to make that an identity column right and it's always the identity column meaning it's all like it's always going to generate a new number for the new rows so this is an auto incrementing series for id and then we just say apply and for this table we're going to make it the primary key by doing this just checking that box and that's it and now we have a table called people which has a primary key of people the id integer so if you look here right then we look at constraints it's got people pk it made a primary key for p a primary key constraint for this for us right underneath the hoods okay so we'll say cancel let's move people up here and then what else do we need in um instagram we need pictures so let's make a new schema object a new table and this is going to be pictures right and then we're going to make another column again and then we're going to call this one id all right and we're going to make that in not a small int this is definitely we can actually make this is probably going to be a big hint because there'll be a lot of pictures in here right and then we can say identity again and say apply and then if we wanted to rather than just checking the box here we could also go through and define a constraint right so you can define all sorts of constraints here we only have one column now so it's not so exciting we'll call this constant pictures underscore pk right and then it'll say constraint type so here's all the types you can do we i did a whole sequence on constraints uh youtube stream on constraints so if you wanted to check those out these are the ones you can enable here right you can do all sorts of fun stuff with the constraint if you want to we're going to say the column type is id which is the bigint right you could do multiple columns if you had them right you can keep adding them and you say apply and now we have a primary key you say apply and we've got two tables so this is the so so far i mean you can kind of almost do this once you have a lot of tables this would get really confusing writing in a sql file like if you sat down and wrote a lot of create table create table create table you could do that and then fill them in one at a time i don't like all that scrolling around i actually like seeing visually the objects i'm creating and then doing stuff with it and then but the next part here's the really fun stuff that would be much harder i think to visualize and it's not to do typing is typing but visualize and kind of understand the relationships so we just do this we say new relationship and we're gonna do uh one-to-many so one person can have many pictures right and so then we pick this one we drag it to this one and it says okay we're gonna make a relationship called people has many pictures um you can change the color of the line that's going to show up there if you wanted it to well i'll show this but you can say whether it's required or not there's also it's going to auto generate names for you for the fields that it's going to put in both the and the constraints um and so what we're going to do you can check this is the default which would be under um properties for the project but you can actually change it here if you want to when it auto generates those things you can do attributes on it you can do constraints the this primary key and attributes this has more to do with a many to many you don't really do that in a with a one-to-many um let's go ahead and make this now right and so it's using crow's feet actually i don't like that color let's change that color it's kind of hard to see let's change that color to a nice dark blue okay so apply there we go or that's actually more of a violet um anyway it's using crow's feet notation for entity relationships um i really like crow's feet it's very because you see it's got this at the end that's called the crow's feet the way you would read this is this is zero or more people have many because there's the crow's feet pictures so a picture has zero or a picture can have zero or many sorry i should say this the other way around a picture has zero or more uh people associated with it no that's still not the right way to say it basically the way i want to say it is a person is not required to have any pictures and a picture is not required to have any people right it's an optional relationship that's the zero part here i can create a picture without having it tied to a people um i don't think that's actually valid for our instagram app i don't want to let anybody just put up random pictures that aren't assigned to a person so i'm going to go back and edit this relationship and say people is required let's do that and now it says this is a one to many not rather a zero or more to many and this is a one to many right so there has to be a person for every picture um careful when you do this in your database remodeling because that means if you do not have a person the the i people id in your picture when you go to insert it it won't work it'll say i don't see that person um so you can't insert the picture right and you'll you can see here and now in our picture in our pictures table we have a new field that was auto generated called id underscore people uh it's an integer because it goes back and it's a foreign key and it's not null right so that's the not null meaning we have to have at least one person that we can link back to on that all right uh any questions so far on the stuff we're showing i hope that shows people how easy it is now to start doing all sorts of modeling and relationships and stuff like that um i find this super helpful um let's so that was fun you're like okay but i have a lot of geo people who watch what i do and so i want to show some fun stuff for the geo people let's actually go ahead and add a spatial field so we want to have a location where the picture was taken if you geotag your photos we want to store that so you can actually go through and say well since it's coming off a phone we're going to assume it's a geography type not a geometry type right so it already knows about the post gis types notice that right away you just say geography spatial so it says okay what type is it well we're going to have to we're going to do a point there's a point right and then the srid we're going to say it's 4326 like it understands all that spatial stuff out of the box like built right into the interface so that's so nice i can actually model spatial entities here as well if it has z or m we can click that as well to let it do that we don't we can actually set the default value to be an expression or a sequence or whatever we don't want any of that we're just going to say apply and now we have a geography type in our table table which i think is freaking awesome again we can edit permissions or do any of that stuff that we want on that right so let's actually add a couple fields here to people as well right so for people we're going to add uh given name given underscore name and that's a text all right let me say it alt you can and he's got keyboard shortcuts in there too i'm gonna call it family name trying to be international here you can tap you can use keyboards rather than using mouse all the time which i love so there we go now we've got two fields there great we're ready to build our we're now we're ready to build our application right um if you want you can look at the sql so this is the sql oh that's just for that object i selected if i select nothing which means the whole database we say look at the source and it'll show you the sql that would actually generate to do that database you can look at that in xml format that's the actual underlying xml that he uses under the scenes um you can pick the postgres version you want to export so whether or not it'll support certain features right so you can there's the sql so a lot of times if i'm having problems just auto exporting it i'll just save the sql out here and send it to somebody if they need to use it um and then i can edit it with my code editor if i want or any of that kind of stuff all right but now that's not so fun the fun part we want to do is so raphael is in the chat raphael is the lead author on pg modeler so i so i'm very bad at portuguese i um aru arujo i think is how you would rafaela rujo i think because then i know at least in portuguese you pronounce the j's like english js not like ho so it's not a whole it's a joke anyway a little trivia fact all right all right so let's actually do something fun now which is let's make this and let's just export this to postgres so i'm going to use my favorite docker containers again so here we are we're in powershell and we're going to do a we're going to make a new docker run we're going to use the let's get to the end can we get to the end of line let's see if we can see the end of the line here no won't let us see the end of the line all right well it doesn't matter i'll show it to you in a second we're using the crunchy app dev container um i need to scroll back and change this yeah we'll call it pg model no we'll call it uh the database the database that's going to create this workshop and we'll call this insta plus plus because it's the new insta the better instagram right so there we've made a new docker container right and it's running postgres all set to go with postgis already in the workshop database and so that actually becomes important for this next part now we're going to say export right and it says it hasn't been validated that goes through the steps of validation to make sure that everything is good in the model and we should do that we'll say validate and the column aha see is referring to a geography type but the postgis extension is not present in the model oh okay so apply fixes great so now it actually added it before i actually go export i want to show you how that shows up now if we go look at objects right we now have an extension here and so it knows in this model we are using post gis this is a non-visible object so the way to look it is to look at the object browser over here right all the other things that are on here the visual things are also in the object browser but non-visual things will only be in the model objects so like if you created a role that's not going to show up visually um but it will show here and the only role we have is this and you'll see we have two schemas but the one that we put thing everything in by default is public right so and there's our two tables in the public schema right you can just look at you can navigate your whole table structure okay so let's go ahead now for real and export it let's export and we're going to make a new connection i have the connections for stuff i'm working on so let's edit connections um we're going to create this connection and we're going to call it insta plus plus right and the connection db is localhost but this could be whatever you want it to be uh oh no the db sorry it's workshop avoided a huge mistake there not really but localhost the user will import just as user uh well we could import as user groot um that's well let's just do it as postgres just make it easier passwords my favorite password which is password timeout seconds anything any of these other things we want to do let's test it great we connected we're connected to a postgres version 12.3 let's add it now we pick instaplus plus and we say apply okay so go to five found connections we say insta plus plus now if we try this by default we're gonna run into a problem right which is uh it says look the database workshop already exists which is true because when i made that container it actually made that database already so you can't you can't draw you can't overwrite it and it's not going to try to drop it so what we say is uh we have to try again right so we go oh i can just go back to settings i should have just gone back to settings rather than output anyway we'll go insta plus plus we're going to say ignore object duplicity so if the object's already there don't worry about it just ignore it um and then i'll maybe i'll leave it let's see what happens if i ignore ignore error code let's see what happens there so it went through and um it it says already there's extensions there and it's ignored and it's proceeding with the export so it's all done so if you want to see it now we can actually go to our sql prompt let's go here uh we'll come into pro localhost right and then the password is password and we do d and then you see in public we have people it created the sequence for us it created pictures and it created the sequence for us and it's all there right and it's a postgis database with all the tiger stuff that we put in there by default too now if you remember where i said you could put comments in we'll go slash d t peoples was it did i call it people or people's that was really bad if i called it people's because people is the the okay good i didn't call it peoples that would have been dt plus for people right and there's that comment we put these are the users right i think it actually puts it there even if i don't put the plus nope it doesn't you got to put the plus to get the description the comment right so that's all in there though if you want to see it so that was how easy it was to go from a picture to sql and one of the things i think that's really helpful about this and i've done this a lot with clients like do you know how i remember i talked about i'm working with island conservation to do some work with them and we'll look at the database i designed in a minute pictures are much easier for people to interact with than looking at sql code especially if they're not database experts so i can say look do you think i've covered all the fields in this table and here's the relationship do you think this relationship it's very easy for people to learn crow's feet notation and it's very easy for them to say oh if it says like you know it's a foreign key or something like that like there's a relationship and i'd tell them to ignore the constraint parts right so what you can also do export here is you can actually export to a graphics file which is a png so what i'll do is usually i'll say let's put this in dd app oh this is the stuff that kat and i have been working on um so that's kind of fun but let's put it in downloads because i don't really care about it that much and we'll just call it workshop and it calls it whatever the name is and i'll do a png and then i go to my downloads let's go to and windows wants to work on it but anyway i really find here here's our png right so i open this up sure let's use photos right and there's our diagram that we can then email to somebody or share to somebody they can write on it with paper and all that stuff and we can talk it's a shared talking point about is that did i capture the relationships properly right and did i capture all the information you guys wanted to put inside the tables and does it go in this table or does it go in this table and all that stuff can happen visually rather than just talking like they can actually see the table structure which is actually turns out to be very helpful for people um it's also very helpful for developers you can send this to them and say here's the table structure right if you want to understand what's going on or it's also very helpful when you want to write sql queries because you can look at the relationships and how you navigate them i just find pictures really great to work with um all right so we did that uh so that's the basics of all the fun stuff you like this is the basic stuff uh oh david really how do i wait so rafael says that you can export a data dictionary an html form is that under export oh wait i've never tried this let's try it ready uh do i want standalone or or split it which i think he means split let's see standalone will generate a single html uh splitted the data dictionaries are generated in separate files uh let's do split uh yeah definitely include an index let's put that in download again uh i guess i'll call this workshop do i have to say html i don't know let's see workshop.html i don't know we'll find out export okay let's go see what's in downloads again oh it's in a folder oh nice oh look at that i never even saw wow raphael that's awesome oh my gosh that's so nice look at the great stuff you should name a folder right it's the folder that that's gonna be so then you can also the nice part is if you're you can put this up on our website that's so great wow i love that yeah definitely peace rafael thank you so wait raphael while we have you here so it's i see raphael silva here ah the uh i see so raphael rojo a rojo arujo arujo um yeah we're navigating through the tables that's super awesome i love this this is so nice oh you can navigate the relationship that's the relation whoa that's so nice oh my gosh anyway um so is this one of those yeah arujo so your family name is arujo silvia is that right i never remember in portuguese i think it's kind of also like some of the spanish countries where like your mother's name is silva your mother's family name is silva and your father's family name was arujo or something like that anyway i always find it fascinating learning how different cultures do naming based on descent i think it's yeah portuguese family nice i love that okay anyway uh let's go back to pg modeler after that little aside um all right so we exported our model let's close that uh i love that i didn't even know that about the data dictionary thank you that was sweet um so let's see what oh and you can also just do plain old uh like markup on here if you want to right so you can do where is it uh there was somewhere that i could just add like text and call outs and stuff quick um no new oh yeah like text box ah so i will put the the code is crunchy ftw and we'll change the font to like uh let's change it to a 48 point font bold oh actually that's the name and so we'll just take that put that there and say code or oops the name of that is code and say apply there we go there we go so again for those of you watching this is the 20 off discount code and uh raphael can actually say if it's been turned on yet or not but if you just put that in the discount code box and you get 20 off your purchase of pg modeler which i think is awesome um all right so the next thing we're going to do is suppose somebody says hey i've got a postgres database i want you to take a look at it and write code against it or i want is you know we're going to start using it and you're like well what is in it i don't even understand it and i don't want to look at all the sql we're going to go the other way um so what we're gonna do now is we're going to docker kill uh insta plus plus all right and then what we're gonna do instead is you can see all my docker images i use for my different projects oops ps-a you can see oops not one but a here's all oh you can't actually see it so here's all the different projects i use what i end up doing is a different docker container per project that way i can keep everything nice and neat and remember stuff so one of the ones i just told you i was working on is um contrapest right so this is where their island conservation international since they care about animals on islands and rats are a big problem on islands for native species we're going to docker start contra pest this is a contraceptive trial a bait trial that's a contraceptive for rats and so they're doing a field study on in california where they're putting out bait rat contraceptive and bait traps and they're trying to see over time does the population of rats go down in that area compared to other areas so we built a database i built a database to take their stuff from arcgis online and put it into like a properly designed schema and so that we can ask queries of do queries of it so the biologists can have saved queries rather than doing a whole bunch of copy paste calculations in excel um so that we started contra pass but you're like okay well what is that database got so we're going to say new project okay and we're going to say import right and there's five connections and the one we're going to do is contra pest right so we give it the connection which is the same as before that we did right you can say what you want to um how you want to do it in import objects to the working model import system objects no extensions not really import errors ignore import errors i don't think there will be any debug mode great all right so there's two it found that there's two databases inside of that container and the one that we care about is contra pest trial it's retrieving all the objects and it says okay what do you want to bring in well we don't want to bring in all the extensions because i'm not using fuzzy i'm not using hstor i'm not using routing i'm not using plr but i am using pi postgis um the roles so they found another role there's this dev user role we'll bring that in because that's who owns the tables and then we'll bring in the schema we want to bring in uh i don't think there's actually anything in dev user right it's all just in public which i'll probably get in trouble with from the sql heads but i don't care let me say import then it goes through and it finds all the objects and it brings all in all the tables so there it's marked the schema this is all in the schema public right this light gray box if you can see it uh let's zoom out we'll go one more so yeah i've run into this font problem before uh raphael on windows just to let you know um but i haven't reported it so like i have to be in at this level for the fonts to render so bug report um all right so we have rats right and we have traps and then we have oh no i'm going to put the trap up top the one that everything's hanging off of that's creating all these relationships that we want to let's actually zoom out some more oh no i can't because i can't see the phones um let's do bait check which should go to a bait station let's do traps and trap check we'll do cameras over here rats are actually over here because i just know what things are um it's fixed in the newer versions okay cool so next time i do this i'll do i'll use the beta version thank you um tracking plates that'll be over here by bait stations and cameras um okay so there's a oh wait were these the tracking plates yeah and then plate check there's the rat rfid there's a camera check and there's a bait check uh what's this relationship going on here that i want it to see okay so bait stations are related to rat yeah that's true they are let's move this over here let's move the bait station here right just get all the relationships to look a little nicer cool let's get the bait check over here and the camera check over here okay now the relationships look so you can see how i'm actually can visually move things around and now you can see that the trap when i check a trap it has a relationship to a trap check has a relationship to rats rad rfid right so i can actually look at this relationship right custom color advanced right there's a constraint on it there's no this one oops this one doesn't actually have a requirement of one or more right and this one actually says let's see bait station and rad rfid so it's got all these nice there there's the constraint you can look at the constraint this is basically the nice way of me basically reverse engineering and looking at all the database and you can see that traps have a location which is a geometry field right so it actually understood that there is post gis and there are spatial data if i browse the objects i can see that there's actually the role of dev user and postgres and so you can i think if we look at the schema i don't remember where the role where i apply the roles let's see if it's actually under here uh edit permissions uh yeah so there you can see it's granting permissions as part of what it did it granted permissions on dev user so the part that i think is awesome about if you're a postgres user the part that's awesome about pgmodeler is everything that you would do with normal postgres you know psql the command line or in your sql like define constraints can grant permissions do all that stuff you can do visually from the the database here so for example i think we can also if i remember correctly we can also define indexes yeah so i can define any index i want here and let's say we're going to make an index we're going to make it we're going to call it don't click apply right and then what we can do is we can say what index type so it understands all the different index types that are in there right we can pick which element it's based on right so any of these things i want to do a bag let's do it based on i know i'm going to be querying on that a lot so say apply i can i don't i wonder if i can make it a full text i don't know that would be a gin and then i don't know if the predicates are somewhere in there maybe we can make it full text i'm not sure though um anyway we can actually go through and say whether we want to concurrent indexing unique is it fast update is it buffering the fill factor all that fun stuff that you would want to place you can um do that and create indexes here as well right and policies and partition keys like if you've done if you're doing partitioning and you want to how this this column how this table is partitioned you can do all that here right so i just think it's freaking awesome i think this tables which i haven't even played with is probably for inheritance so if there's tables that inherent from trap check you can actually have inherited tables here you can have it unlogged so if you want a really fast insert table you can make it unlog all that really fun stuff whatever custom sql does anyway you can put sql that appended or prepend it to the sql statements if there's something that's not available through the interface it's just freaking awesome oh and this is also the owner is also aware of all the owners all the roles so you can pick any of the rules that you want it's just really well thought out and really nicely integrated um anything else i think i want to show i think that's about it usually i'll no matter what just to put it in source control i'll export the source nope that's because i've clicked one object if i click nothing if i click that and i say source it'll actually generate all the or i'll x you can do it that way the better way to actually do it is to say export and then just say sql file and you can export right there um the one thing i haven't played with at all is so i've showed you how you can go forwards and backwards there is a way to do diffing i've seen a lot of improvements coming in the new versions i haven't played um where i haven't played with this at all you can actually diff databases and then apply those changes if you want to so like if there's a dev version where you're making a bunch of changes you can diff that between the production version like make a model diff it with the production version and then apply those changes the production version i haven't played with that at all i haven't had a need to um and so um that's another really advanced awesome thing that i love but i'm not i don't have a need for it yet basically for me the way i use pg modeler is i design my databases this way talk to users share it back and forth and then we and then make changes here or reverse engineer stuff so it's invaluable to me and i love it and now also here you've learned that you don't have to sit down and start hopefully what you also learned today was that you don't have to sit down and um start writing create table blah blah blah and that's the only way to create databases there's a visual way to do it it's nice it can export it right to your database without you even having to do anything else it's this is the only way i actually really design anything past two tables because otherwise for me it just gets lost if you have any questions you can also always put them in the youtube channel again the code for uh the 20 discount let's do that again we'll say new no i don't want to schema oh because i'm in the schema hold on new text box code and the name we'll make this 48 point font again i think maybe i made it 42 but we'll make it i'll make it red this time that's fun the code is steven raphael what's the code is the code really like oh just hit end to see n wait hold on let me finish this raphael and then i'll see what you're talking about um the code for the 20 discount is um crunchy ftw apply that is the code to win all right so now you're saying if i'm here and i hit n oh look at that oh i love that okay but yeah crunchy f crunchy ftw yeah okay so here's my feedback on that feature i never knew about this you can just pre rather than having to right click so raphael really is a coder you can tell um that raphael is a coder because he's making everything keyboard sequence possible so if i'm inside my scheme and i press n i get the objects that i can create in the schema and then oh and then are the letters in the front like if i wanted to make a new table i hit t oh my gosh that is the best raphael oh my god raphael that is so cool i don't ever have to take my hands off the keyboard you're the best cat did you get that see cat no hands off the keyboard leave the mouse alone when you're coding um the only feedback i have on that raphael though the real-time feedback is make it opaque i don't need to see this stuff underneath it's making it hard for me to read this that i have to use the mouse uh yeah i'm gonna add you next time cat keyboard only for all you other new developers stop using your mouse so much you'll be much more productive and faster and your pro any program you're using should have lots of keyboard shortcuts every time you take your hand off the keyboard and use the mouse it cuts your brain flow and it cuts your productivity so anyway i'm going to wrap up there i hope this was helpful again crunchy ftw when you go to purchase it which you should i highly recommend the forever variety it's a value for you you basically get pg modeler for the rest of your life and since um since raphael is going to live to be 120 years old that's a lot of versions of pg modeler and plus also if you like the tool and you want him to keep keeping it updated with new versions of postgres which like 13 has a whole bunch of new stuff and post gis has a whole bunch of new stuff so it's just good to support your local open source developer um buy it okay and you can download it and try it and he has a demo version i think but anyway there you go uh i will see you probably well since today's friday it'll definitely not be until next week i'm not quite sure what i'm gonna do for the next one kat and i are working on in writing some learnings tutorials on indices but i don't really feel like talking about indices yet i'm not sure if you have any ideas about what you'd like me to talk about next go ahead and drop it in the comments uh or in the stream and i'll see you next time hold on we're gonna get that funky music going again which i kind of like i kind of don't like the shut up and sit down at the beginning i kind of like it and i kind of don't like it i can see how some people are going to be like that's really rude for me it just kind of reminds me of being back in new york even though it's kind of a british accent so let's go with that we're out you

Channel: Steve Pousty

Views: 1,869

Rating: 4.9200001 out of 5

Keywords: postgresql, postgres, database, programming, postgis, pgmodeler, er diagrams, database design, schema design

Id: oYttQ72w3Jo

Length: 45min 3sec (2703 seconds)

Published: Fri Jul 17 2020