12. Mastering Databases with Postgres

0:00in this video we are going to talk about

0:03databases when you're dealing with

0:05backend systems interacting and handling

0:07databases is one of the most important

0:10and one of the most frequent operation

0:12that you're going to perform so

0:15understanding all the concepts

0:17surrounding it is crucial to be

0:19efficient in your job so let's start

0:21with the question why why do we need

0:24databases in the first place now at its

0:27core a database is simp simply a way to

0:31persist information across different

0:33different sessions to talk in a very

0:36high level and what that means is and

0:39persistence basically means storing data

0:41in a way so that it survives even after

0:44the program that created it has been

0:47stopped so for example think of your

0:50to-do list app right you add an entry

0:54and you can check off all the task that

0:57you have already finished etc etc like

0:59that's the basic functionality and

1:01expectation from a to-do app now when

1:04you do some operation when you create an

1:06item or you when check off an item and

1:08you close the app and when you open it

1:10again you find all the information the

1:12state of the data that you left it it's

1:15the same when you visit again when you

1:18come back to the app so that's what we

1:21mean when you say persistence the

1:24information has to be there in the same

1:27expected

1:28State even after a considerable amount

1:31of time has passed or across different

1:34different physical locations right

1:36that's what we mean by persistence if

1:38you did not have persistence every time

1:40you open that app You' have to create

1:43new to-do list items and You' lose all

1:45your progress whatever task you had

1:48created previously etc etc right

1:50persistence is a very important thing

1:52and we use it in our day-to-day lives a

1:55lot now what is a database when what do

1:59we mean

2:00when we say the word database the term

2:05database is surprisingly very very Broad

2:09in the simplest sense any kind of

2:12structured any kind of structured

2:14storage can be considered as a database

2:17for example you have a smartphone and in

2:21your phone you have a contact list right

2:24of different different people and that

2:27list can be considered as a database

2:30in the same way if you are a developer

2:33you must be familiar with the concept of

2:35local storage uh which is offered by

2:38every browser so we can check that out

2:41right now if we open this the developer

2:44tools and when we go application and we

2:47can check the local storage and it has

2:49some entries right since we are using

2:51this website called xcal draw the

2:53website has stored some entries in the

2:57format of key Value Store it also has a

3:00session storage also has a cookie

3:02storage etc etc right all these

3:04different different types of storage

3:06mechanisms they are also considered as a

3:09database and even if you take a simple

3:11text file where you just jot down notes

3:15and you can refer to those notes later

3:18on even that can be considered as a very

3:21very basic database so if you try to

3:25derive the patterns from all these

3:27examples database basically means

3:30is some kind of system some kind of

3:32persistent system which offers ways to

3:35create read update and delete data from

3:38it right that's a very basic and high

3:41level example of database which is not

3:44limited to what we mean when we say

3:46database in the context of backend

3:48systems database is a very generic term

3:51which means a persistence layer which

3:54provides operations of crud create read

3:56delete and update but when we say

4:00database in the context of backend

4:02systems in the context of servers right

4:05in typical developer context when we

4:09mention database there what we mean is

4:13disk based databases disk based right

4:16and by dis I mean hard disk it can be

4:19HDD ssds or whatever modern technology

4:22of storage we have available but why dis

4:26storage why dis based databases because

4:30disk storage whether it's a traditional

4:32hard drive or a modern solid state drive

4:35or also known as SSD is relatively cheap

4:39when compared to other means of storage

4:42for example when we consider Ram which

4:44is also considered as a main memory or

4:47the primary memory when we take into

4:49account our CPU unit right if you're

4:52from a computer science background you

4:53must have known that we have a CPU unit

4:56which can access the primary memory and

5:00it can also access the secondary memory

5:02which we also call as dis based memory

5:05right which uses either a hard drive or

5:08a SSD now Ram is very fast it's called

5:12main memory and it is very very fast as

5:15compared to the dis based alternative

5:18but the problem is Ram based storage

5:23it's relatively costly and we don't have

5:27as much Supply as you do when it comes

5:30to dis based so if you check the

5:33configuration of your system whether it

5:35is a Windows Bas Linux Mac does not

5:37matter if you check the configuration of

5:39your system most people have something

5:42like 8 GB Ram or 16 GB Ram or 32 GB Ram

5:46or if you are on the higher end of the

5:49consumer you must be having 64 or Max to

5:53Max 128 GB of RAM right but when it

5:56comes to hard disk when it comes to a

5:58second disc storage most people since

6:01hard discs are relatively cheap these

6:03days most people they have storage

6:06starting from 5 12 GB up to 2 TB right

6:10that's the average storage people have

6:12in their laptops in their systems right

6:15and you can see the pattern we when it

6:17comes to the storage space we have

6:20limited amount of ram we have limited

6:22amount of primary memory but we have a

6:24lot of space when it comes to displaced

6:27memory or secondary memory and trade off

6:29is RAM is very fast when it comes to

6:33data retrieval or data saving and disk

6:36based storage is relatively slow because

6:39of the way the data is stored and

6:41because of the way the data is fetched

6:44and that's the reason when we use

6:47caching mechanisms either in redis or in

6:51memory caches etc etc whenever we talk

6:53about caching we talk about storage in

6:57Ram or storage in primary memory

7:00because fetching data or saving data

7:03from primary memory from our cache is

7:06very very fast when it comes to fetching

7:08data from our databases which are disk

7:10based storages also known as secondary

7:12storage but when it comes to databases

7:15the thing that matters most is we need

7:18more space and we can do a fair amount

7:22of tradeoff when it comes to speed and

7:25that's the reason most databases at

7:27least when it comes to traditional

7:29databases like relational or non-

7:31relational databases they are based on

7:35disk based storage or secondary storage

7:38that's where they actually store the

7:39data and the format they store the data

7:42the way they store their data all the

7:43algorithm surrounding it etc etc that's

7:46a very technically deep area and we are

7:49not going to cover that in this video we

7:52just want to keep this relevant for a

7:55backend engineer right on the

7:57application Level the only thing you

7:58need to know is

8:00caching Technologies like redis Etc they

8:03store their data in primary memory or

8:05RAM and databases traditional databases

8:09like POG or mongod Debi which we'll

8:12cover next all these relational or non-

8:15relational databases they store their

8:16data in disk or secondary memory because

8:20disk based storages offer more capacity

8:23at less price but at the cost of less

8:26speed that it is clear we are talking

8:29about databases in the context of disk

8:33based storage and that too in the

8:36context of backend systems right now we

8:40come to a term which is known as dbms

8:44also known as database management system

8:46now what is a database management system

8:50just storing our data whatever data we

8:53have just storing it in some kind of dis

8:56based storage that is not enough of

8:59course we also need different different

9:01ways of retrieving that data or making

9:04changes to that data or deleting that

9:06data right all these different different

9:08operations that has to be done and that

9:11has to be done in a very efficient way

9:14since we are talking about hundreds and

9:16thousands of GBS of data right so we

9:19need these operations create read update

9:22and delete operations and that to in a

9:24very efficient manner and that's the

9:26reason we have softwares we have

9:28software systems

9:29called as dbms softwares and whose sole

9:34responsibility is to efficiently provide

9:36all these cud operations to this to the

9:40clients or to the users whoever is using

9:42it and storing that data of course they

9:45also have a lot of other

9:46responsibilities like security and

9:48scaling the database systems and load

9:51balancing etc etc right but on a very

9:54high level they have two

9:55responsibilities storing the data and

9:58providing different different operations

9:59to the client or the user primarily CED

10:02operations create read update and delete

10:05so in a way some of the responsibilities

10:08of dbms that we can point out is First

10:13Data organization they need to

10:16efficiently organize the data so that

10:18fetching updating and creating more data

10:21etc etc all these operations are

10:23efficient second access as I've already

10:26mentioned they have to provide methods

10:29to do c operations create read update

10:31and delete third Integrity now it's a

10:34technical term but what it basically

10:37means is the accuracy of the data the

10:41validity of the data we'll see what all

10:43these different different buzzwords mean

10:46when we actually go into examples so

10:49this just means the data is correct

10:52right whatever data that we are storing

10:54that data is valid that data is not

10:56corrupt so to give you a very high level

10:59example it's an e-commerce platform and

11:02we want to store all the order details

11:05of customers and each order will have

11:07some kind of payment information how

11:10much was the order etc etc right and in

11:13the database we are storing the payment

11:15amount as a number so it's the

11:18responsibility of our database

11:20management system or our dbms software

11:23to maintain the Integrity of the data so

11:26that no one can insert anything in that

11:29field if we have a payment field then

11:33for this field we want only number data

11:36Ty right we want some kind of numerical

11:40amount so someone if someone tries to

11:43store a string something like let's say

11:46something and if they try to insert it

11:49here that should fail the dbms software

11:51should not allow it because it's their

11:55responsibility to maintain the Integrity

11:57of the data the correctness of the data

11:59the validity of the data right it has a

12:01lot of meanings in a lot of contexts but

12:05this is what it means the data has to be

12:07accurate the data has to be consistent

12:09now fourth is security which basically

12:12means protecting the data from

12:14unauthorized access databases dbm

12:16softwares have different different users

12:18different different roles etc etc for

12:21the sake of protecting the access to

12:23data right so that's a very high level

12:26overview of what are the different

12:27responsibilities of database management

12:30system now here we have a question why

12:32do we need these dbms softwares why not

12:37use just simple text files or any kind

12:39of file and store all our data in text

12:42format inside it and when people were

12:45coming up with these concepts of

12:48databases or before people came up with

12:50these concepts of database management

12:52system this is how people started with

12:55they try to store all the data in text

12:58files right now the problem with storing

13:01your data in text files is there are a

13:03couple of problems first thing parsing

13:05so if we are going to store our data in

13:08a text file every time you wanted to

13:11find a specific data point let's say

13:14it's a customer database and you are

13:16storing all the details of your customer

13:18in your database inside that text file

13:21as plain text and every time you want to

13:24find a specific customer you'd have to

13:26write code application code it it can be

13:29any programming language python Java

13:31JavaScript go rust it can be any

13:34programming language you have to write

13:36code in that programming language in

13:38your application Level in your backend

13:40code to pass the text file split the

13:43lines so if we have a text file and we

13:46have different different lines you have

13:48to pass all the entries of this text

13:50file and we have to split these lines

13:53and we have to compare each field and

13:55this whole process is very slow if

13:57you're familiar with passing data from

13:59our file system in different different

14:01programming languages languages like

14:03rust are relatively faster when it comes

14:05to passing kind of uh operations but

14:09languages like JavaScript python are

14:11relatively slower as compared to rust

14:14right they are fast but compared to a

14:18highly performing language like rust

14:20they are relatively slow so if you're

14:22programming language is something like

14:24JavaScript or Ruby then you'll also face

14:28a huge performance

14:29hit right and also doing all this

14:32passsing thing is very error prone if

14:36something goes wrong you can corrupt

14:38your data you can provide wrong

14:39information to your customers etc etc

14:41like parsing itself is not an efficient

14:44solution because how slow the operation

14:47is and how error prone it is the second

14:49thing is there is no structure text

14:52files do not have a formal structure you

14:55cannot enforce that data has to be in

14:59this particular structure in this file

15:01right text files are very fluid you can

15:05store any amount of text in any format

15:08you just have to dump it there right

15:10there is no structure and that makes it

15:14very difficult to enforce consistency in

15:16data you cannot enforce something like

15:19this particular field will only have

15:22number you cannot enforce that rule

15:25right since it is all text file since it

15:27is all string it will take any kind of

15:30data and it will put it there right it

15:32cannot promise any kind of data

15:34consistency third thing concurrency by

15:37concurrency what we mean what happens if

15:40two people at the same time try to

15:43update the same taex value right whose

15:45update is going to be considered as

15:48legit and whose update is going to be

15:51going to get discarded obviously the

15:53last one who updated this text file only

15:56their update is going to persist because

15:59let's say we have a text file and we

16:02have an entry let's say amount right

16:06amount and we have something like 40 two

16:09people are trying to access it for the

16:11sake of modification they want to modify

16:13this field called amount so the first

16:15thing they will do obviously is read the

16:17file right they need to read the file

16:19before they make any changes they cannot

16:22just put some arbitary amount they want

16:24to increase the amount so one person

16:26wants to increase the amount by 20

16:28another person wants to to decrease the

16:29amount by 20 now what happens both of

16:32them read the data right both of them

16:35read the data at the same time they

16:36start their operation at the same time

16:39okay they both have the initial entry of

16:4140 so the first person they increase the

16:43amount so they do 60 and the second

16:47person they want to decrease the amount

16:49so they do it 20 and they save it now

16:52even though both of them started at the

16:54same time because it was a read

16:56operation when they save it of course

16:58there is going to be some kind of first

17:01comes first Ser kind of situation you

17:03cannot tell beforehand that this update

17:06is going to persist or this update is

17:08going to persist it all depends on what

17:10CPU cycle it is running and and thousand

17:13different parameters right so depending

17:16on the environment of the system at the

17:20end of this operation at the end of both

17:22of these operations the value it might

17:25be 60 or it might be 20 right there is

17:28no consistency here that's what you mean

17:31by concurrency when two different users

17:35try to do some kind of modification at

17:38the same time to the same data you need

17:41some kind of concurrency mechanisms

17:43inside that software inside that

17:45database software to efficiently to

17:48accurately manage the whole interactions

17:51to provide a consistent result right and

17:53a simple text file simply cannot do that

17:57so these are overall all the the

17:59challenges that people faced and if you

18:02try to do that today you'll also face

18:05the same challenges after some time

18:06after your data Grows Right and because

18:09of all these challenges because of all

18:11these limitations of Simply storing your

18:14database in a text file people came up

18:18with softwares like dbms and now that we

18:21are talking about dbms we have finally

18:23reached the stage where we can talk

18:26about different types of dbms software

18:29on a very high level we have two types

18:32the major two types one is relational

18:36and the other is the opposite non-

18:38relational relational database basically

18:40means a database system which organizes

18:43data in

18:45tables rows and columns okay and

18:51relationships between different

18:53different tables are defined using

18:56Concepts like foreign key Etc right so

18:58of the key features of relational

19:01databases data is

19:03structured and data is inserted into a

19:06database which have predefined

19:10predefined schema you cannot just

19:13arbitrarily insert or push any kind of

19:16data into your database that particular

19:19data should have a particular schema

19:21which basically means it should have a

19:24corresponding table and that table will

19:27have a very strict schema you have to

19:31beforehand Define All The Columns of a

19:35table and all the data types of those

19:37columns right everything has to be pred

19:40decided you cannot do anything on the

19:44flat it is a very strict system and

19:47because of that strict schema enforcing

19:50the advantage it offers is data

19:54Integrity which basically means at any

19:57point of time you can bet on the state

19:59of your data you know what is the data

20:04type of a particular column is what are

20:06the different relationships between

20:08different different tables and a lot of

20:10different things the data in your tables

20:12always have a consistent State they'll

20:15always have an accurate State similarly

20:18we have non- relational of and next in

20:23order to interact with this database we

20:25generally use the language SQL also

20:28known as structured query language in

20:30the same way we have non- relational

20:33databases so some examples of relational

20:36databases which you might have already

20:38heard MySQL postgress SQL Server etc etc

20:42right we have a lot of different

20:43different relational database types

20:45similarly we have non- relational you

20:47must have heard about mongod one of the

20:51most famous databases in the non-

20:53relational domain and the difference

20:56between these is while relational

20:58databases force you to have a consistent

21:02schema a very expected schema before you

21:05can put data inside your database right

21:10you have to have a predefined schema but

21:13non- relational databases do not enforce

21:15anything such as that right you can put

21:19any kind of data and two entries of a

21:24same table we don't use the term table

21:26in the non-relational domain but

21:29if you're doing a side by-side

21:30comparison then a table is called a

21:33collection in mongod right and inside a

21:36collection each entry in relational it

21:40is called rows and in non- relational or

21:42M it is called a document in relational

21:45domain each row will have the same kind

21:49of data the structure of the rows will

21:52always be the same right but in non-

21:55relational domain each document can

21:58follow different data structure okay

22:00that's the primary advantage but

22:03sometimes disadvantage when it comes to

22:05the no SQL domain the advantage is

22:07obviously it has a very flexible schema

22:10so if you're doing some kind of

22:12prototype you want to move fast you

22:14don't want to spend time figuring out

22:17your database schema enforcing the

22:19schema maintaining it etc etc then you

22:22can go with something like mongod and

22:25move very fast without thinking about

22:27your database schema you can you can

22:29push data on the Fly you can fetch any

22:31kind of data ET Etc right the

22:33flexibility can be an advantage in some

22:35scenarios so if you want to take an

22:37example let's say we have a CRM CRM also

22:40known as customer relationship

22:42management software and inside this what

22:45usually happens is it needs to maintain

22:47accurate and consistent data about

22:49customers data like contacts and all the

22:53sales opportunity details etc etc right

22:56and this data this critical data is more

22:59suited to be inside a relational system

23:01a relational database like postgress is

23:04a very good choice because it provides

23:06strong data integrity and allows for

23:08complex queries and analyze

23:10relationships between customers so a CRM

23:13system is a very good candidate when you

23:16are trying to decide between a

23:17relational database and a non-

23:19relational database in the same way if

23:23we want to find a use case for a non-

23:25relational database we can take an

23:28example like CMS also known as content

23:30management system so content Management

23:33systems are used to push content from a

23:37remote site to different different

23:39content distribution system for example

23:42let's say you have a blogging platform

23:46so every time you want to add a new

23:48article to your website you don't want

23:51to open your code base and write a new

23:53file and push it to GitHub and deploy it

23:56Etc ET right you can pull that data in

23:59your website dynamically from a Content

24:01management system it can be something

24:03like sanity Etc ET like we have a lot of

24:05cmss in the market so you can pull that

24:08data from a Content management system

24:09and you can render that data so every

24:12time you want to add a new article you

24:13just have to log into your CMS system

24:16and write that blog or article in the

24:20markdown format and save it and the next

24:23time someone refreshes your website the

24:25new article will be fetched so when you

24:28have a use case like this the content

24:31that scms needs to store that is not

24:34really structured so an article can have

24:37an image it can have a code blog it can

24:39have a YouTube embedding Etc ET like the

24:44content can be of a lot of types so a

24:48non- relational database something like

24:50the mongod makes a lot of sense when you

24:54don't know beforehand what are all the

24:56different types of data that are going

24:58to be stored here you just want to take

25:00everything and dump it here right so in

25:03these use cases mongod makes a lot of

25:05sense but even though databases like

25:09mongod they offer a lot of

25:12flexibility they can also present

25:15challenges in terms of data Integrity

25:17which is a very important concept when

25:19it comes to database systems and because

25:21they often lack the strong constraints

25:24and relationships of relational

25:27databases is EAS easier to introduce

25:29inconsistencies into the data since the

25:32schema of the data the Integrity of the

25:34data is not enforced at the database

25:37level you have to do it at the

25:39application Level which adds more

25:41complexity to your code and of course it

25:44is more error prone since application

25:46code is generally changed a lot and it's

25:49very easy to miss something right to

25:51introduce new bugs to your code base so

25:54now we have to make a choice right we

25:57are going to learn about databases and

25:59there are a lot of options in the market

26:01we have relational we have non-

26:02relational in inside relational we have

26:05MySQL we have postgress we have site and

26:08SQL Server a lot of different different

26:11products and all of them pretty much

26:13offer the same kind of features and all

26:15of them can scale when it comes to

26:18implementing in in your production

26:20systems so we have to make a choice here

26:22what database are we going to proceed

26:25with and when you have a situation like

26:27this going with postgress makes a lot of

26:29sense why a couple of reasons one it is

26:34open source and free right it is not a

26:37proprietary software it is completely

26:38open source you can go and look at the

26:41source code of the postgress even though

26:43you won't necessarily be doing that but

26:45still it is an open- Source software and

26:49a lot of companies prefer open source

26:50softwares so that they can host it they

26:53can deploy it in their own premises in

26:56their own servers etc etc right second

26:58the postgress dbms system it sticks to

27:03the SQL standard so you can take any SQL

27:06query and you can run it on a postra

27:09system and it will perform the same way

27:12it performs in a different database

27:14system like MySQL or SQL Server because

27:17the postgress database system sticks to

27:19the SQL standard so in the future if you

27:22want to migrate your database to a

27:24different system then you won't have to

27:27do a lot of work let's say you want to

27:29migrate to myql for some reason right so

27:33since postgress is SQL compli and you

27:37have written all your migration

27:39statements all your create update

27:41statements and fet statements in

27:43standard SQL format then you can just

27:46change your database and do some minor

27:48changes and you can easily switch from

27:50postgress to myql right that's one of

27:53the other reasons to go with postgress

27:56it is very easy to migrate to a

27:58different system if you need it three it

28:01is very extensible which means it offers

28:04a lot of features if you go to the

28:06postgress documentation it is around,

28:091400 pages long and it offers a lot of

28:12features pretty much covers every single

28:14use case a typical SAS might encounter

28:19right and it also has a very good

28:21extension based system so you can

28:24customize it depending on your own needs

28:26fourth it is known for its reliability

28:30and scalability and fifth I think this

28:33is the one thing that makes it easy to

28:36make the decision it has very good Json

28:39support now as we discussed just before

28:43this that one of the primary reasons to

28:46go with something like mongod a non-

28:48relational database is you can take any

28:51kind of data so when we say any kind of

28:54data we mostly mean Json any kind of

28:57Json because in Json you can Prett much

28:59store any kind of data right it can be

29:01number it can be string other Json that

29:04are embedded inside it arrays etc etc

29:07right so you can store any kind of Json

29:10as a document in a no SQL database in a

29:13mongod database in the same way since

29:16postgress offers a Json data type and it

29:18has very good indexing and query

29:21capabilities for Json Fields there is no

29:24other reason to go for a different

29:27database just for dynamic data right you

29:30can use postgress with Json fields for

29:33your needs of dynamic data as we saw in

29:35the previous examples of a Content

29:37management system if you have some kind

29:40of content that you're getting from the

29:41user and you want to save it in your

29:43database and you don't have a strict

29:46schema for that content you can go with

29:48a typical Json schema and dump whatever

29:52content that is coming from the user

29:53there and you when you want to render it

29:56you can fetch it from there and you

29:57render it right there is no need to

29:59switch to a non- relational database

30:01just for the needs of your Dynamic data

30:05right and because of all these features

30:07postgress is pretty much the number one

30:10choice when it comes to at least what I

30:12have seen a lot of startups and a lot of

30:14big companies also stick with postgress

30:17and postgress is usually their first

30:20choice and even though if you do your

30:22own research you'll find a lot of

30:24Articles where you'll see my SQL has a

30:26lot of performance benefits etc etc

30:29right but until you are serving millions

30:31of users and you want to optimize a very

30:34specific bottleneck of your application

30:36you don't really need to think about

30:38whether you should go with mycle or

30:40whether you should go with posg right

30:42since the rich set of features and the

30:45very good Json support of postgress

30:47postgress should be your first choice in

30:50pretty much all your projects and that's

30:52the reason in this video we are going to

30:55go with postgress whatever all we are

30:59going to learn about databases we will

31:01learn in the context of hress and with

31:04that I want to give a note here that

31:07there are already thousands of free

31:10resources available on YouTube elsewhere

31:13like Udi you have paid resources and you

31:16have hundreds of free resources that

31:18cover the basics of SQL and postgressql

31:22right SQL is when we say SQL it is the

31:25language that you use to query and

31:27postgress is the database system the

31:30software on which we execute our SQL

31:33queries right so there are a lot of

31:36courses pre courses available right in

31:38YouTube for both SQL and postgress right

31:42so instead of repeating the same thing

31:45instead of doing the all the create

31:47table select the basics of Order byy

31:50Group buy right all the basics of SQL

31:53and the basics of postgress again in

31:55this video we are going to save some

31:57time and we are only going to stick with

32:00some Concepts that are very relevant

32:02when it comes to backend systems and we

32:04are going to leave the basics so that

32:06you can learn it elsewhere so if you

32:09want you can pause this video right now

32:12and just search in YouTube SQL Basics

32:16you will get videos from 1 hour to 20

32:18hours and same for postgress Basics so

32:22you can choose a video according to your

32:24own

32:25Comfort what amount of comprehensiveness

32:27that you need you can watch a video of 1

32:29hour and come back to this video or you

32:32can spend a couple of days learning

32:34about SQL Basics and postgress Basics

32:36and come back to this video right so

32:39that is one of the prerequisite before

32:40you proceed to the next section so we're

32:43going to skip all the basics so that you

32:45can do your own research this is table

32:48plus it is a graphical software for

32:52interacting with your databases and it

32:55has a very modern UI fill

32:58and this is the software that I use on

33:00my day-to-day basis for all my database

33:03query operations and exploring Etc ET

33:06right so we'll be using this in this

33:09entire video to go through different

33:10different demos and to explore different

33:12different concepts now starting with

33:15what are all the different different

33:17data types that we have available in

33:19postgress I just want to bring this even

33:21though just before this I mentioned that

33:23we won't be covering the basics of

33:26postgress and basics of SQL this video

33:28but I feel this is an important piece of

33:31information that we have to cover before

33:33we proceed so that we don't have to

33:35repeat this again when we are actually

33:38talking about our backend system right

33:41now we won't be going very deep into

33:44each data type we'll just do a very high

33:46level introduction this is a create

33:48table query if you're already familiar

33:51with the SQL language then you'll

33:54recognize this this is how we create a

33:56table in any relational database whether

33:59it is POG gra myc etc etc right now we

34:02are creating a table whose name is data

34:04types demo and these are all the fields

34:07which basically means these are all the

34:09columns that are going to be in this

34:11table for each row right so starting

34:15with we have serial what does serial

34:17mean it is just an integer data type but

34:21each time we add a new entry into the

34:24table this will increment the value so

34:27by default let's say it starts with zero

34:30right the next time you insert a row and

34:33if you omit this ID field in your insert

34:37statement value of this column for that

34:39row is going to be one same way the next

34:42time you enter something it'll be 2 3 4

34:45right and usually we use serial or big

34:49serial so there is another version which

34:51basically has more capacity the

34:55maximum number that you can store in big

34:57serial is a lot higher when it comes to

35:00serial so usually when you're dealing

35:03with production systems we go with big

35:05serial when we want to make ID as our

35:08primary key so primary key is basically

35:12a unique field using which we can

35:14identify a particular row in a table

35:16right now serial is an integer which is

35:19going to be automatically incrementing

35:22with each entry in our table then we

35:24have small int we have big int and we

35:27have integer all these three are

35:29basically integers just that the

35:31capacity as I said the maximum number

35:34that you can store an integer so this is

35:37how the scale works we have integer

35:40Which is less than small end Which is

35:43less than big end so depending on your

35:45need you can either go with integer

35:48small end or big end same way we have

35:51decimal and numeric which are mostly

35:53considered identical you can use either

35:56of them they pretty much much behave the

35:58same way right and the purpose of this

36:03is what does this 10 and this two means

36:06is it basically says you can store a

36:10number you can store a number in this

36:14format right on the right side of the

36:16decimal point there will always be two

36:19numbers right that's what this two means

36:22on the right side of the decimal point

36:24there will always be two numbers and

36:26what do 10 means is across all these

36:29numbers in this complete number across

36:33all these numbers in this numerical

36:34repes representation the maximum amount

36:37you can store is 10 so you can store

36:41something like 1 2 3 4 5 6 7 8 dot 9 0

36:48right now if you count it we have 10

36:51numbers we cannot store something like

36:54three here we cannot add something like

36:56this because the maximum we can store is

36:5910 numbers across this whole numerical

37:01representation right and on the right

37:04side we have two numbers this is what

37:07this means and the question mostly

37:10people ask is what is the difference

37:14between decimal or numeric something

37:17like this as compared to something like

37:19real or double Precision or float right

37:21now the difference is a little

37:23complicated floating Point numbers they

37:26can be real double float whatever you

37:28call them they are represented in a very

37:32different manner as compared to the

37:34decimal counterparts which we have in

37:36decimal and numeric so the thumb rule

37:40that you should have is if accuracy is

37:42an important aspect for that field so

37:47let's say you are storing an information

37:50like price for a field like price

37:52accuracy is very important right because

37:54it is going to be involved in a lot of

37:57calculation

37:58and a lot of things can go wrong if you

38:00have different different representations

38:03of the same number in different

38:04different systems etc etc right the

38:07accuracy is very important when it comes

38:08to a field like price so in this case

38:11you should always go with decimal right

38:14you should always stick with either

38:15decimal or numeric but standard says you

38:18can go with decimal right so if accuracy

38:21is an important aspect then always go

38:24with decimal because floating Point

38:26representation

38:28can have different different values

38:29across different different systems

38:31because of the way they are stored

38:33because of the way they are processed

38:36they have a very different algorithm

38:37when it comes to rendering or at the

38:40same time let's say you have a field

38:44like size right and it is a size of a

38:47area it can be something like five

38:5267.

38:538987 something like this right some

38:56fractional number some floating Point

38:58number where accuracy let's say if 89

39:0287 turns into 9 or 8 9 67 something like

39:09this very small discrepancies in the

39:13accuracy or the representation of the

39:14number does not impact a lot of

39:17difference in your systems when you have

39:20numbers like this you should go with

39:22floating points right now if you have a

39:25question like if decimals are always

39:28accurate then why shouldn't we always go

39:31with decimals and the reason is because

39:33decimals always have accurate

39:35representation of their value and

39:37because floating points don't have that

39:40kind of accuracy floating points are

39:42considered faster when it comes to

39:45storage when it comes to performing

39:47different different calculations etc etc

39:48right floating points are very fast that

39:52is the reason in a lot of scientific

39:54computation based domains roting points

39:58are preferred as compared to decimals or

40:01decimal

40:02representations so you should always

40:04evaluate what kind of value that you're

40:06going to store and if it is not

40:09something that is going to have a lot of

40:11impact if there are small discrepancies

40:13in the representation of the number then

40:16you should go with float because they

40:18are very fast to process and very

40:20preferable in these kinds of scenarios

40:23but if it is a value like price or

40:27something something very critical where

40:29accuracy matters a lot then always go

40:31with decimals right that covers pretty

40:34much all the floating Point data types

40:36then we have a very interesting category

40:38which are string categories and we have

40:42three types in that we have care V care

40:45and text now all these three data types

40:48store text that is the similarity

40:51between the three data types the

40:53difference is when we say car 10 and

40:56inside this field if he says store

40:59something like AB if you store something

41:02like this inside a data type which is

41:05declared as this and the length is

41:08defined as 10 then what the database

41:11system will do is it will pad extra

41:14eight spaces in that field before saving

41:17because we have defined the length is 10

41:21and it is a care data type right that is

41:25the property of character data type

41:27whatever length that you have defined

41:30does not matter the actual value that

41:34you're are trying to insert it will

41:36always try to store the same length if

41:39you don't provide the same length then

41:41it will just pad some empty spaces right

41:45inside whatever way it is represented it

41:47will add some empty spaces and it that's

41:50how it will store it and that is the

41:51reason people came up with another

41:53standard which is called Vare also known

41:55as variable character now the way it

41:57works is when we say the length is 255

42:01what it means 255 is the maximum length

42:05that you can store in this field but if

42:09you store something like this AB then

42:12the length will be two right it won't

42:14add Extra Spaces just to make a for this

42:16255 length if you store a smaller value

42:20then it will be two if you store

42:21something like a b c d then it'll be

42:25four etc etc right depending depending

42:27on what Valu are actually being inserted

42:30into the database it will adjust itself

42:33just that the maximum amount the maximum

42:38number that you can store is 255 you

42:41cannot exceed that if you do then you

42:42will get a database level error now the

42:46last thing is text which is a very

42:48modern alternative to Ware so imagine

42:52Vare without any length field basically

42:55any amount of text

42:57a text of any length that's what text

43:00means you don't enforce any length on

43:04this field you can store a text of any

43:07length usually it has some kind of upper

43:10limit like 250 MB or something something

43:13you can do your own research for that

43:16but yeah and when we say 250 MB of text

43:20it is very very very long text right

43:22usually we won't be needing that much

43:24but that's what it means now the

43:26question is when you want to store a

43:29string which one you should go with the

43:31first thing is never choose care right

43:34it it is a very old standard people used

43:37to do it way back so either go with

43:40varer or text you can use care if you

43:43know beforehand that the length is

43:47always going to be the same right it

43:49cannot be variable so let's say you are

43:53storing the codes of all the days of a

43:56week so the values can be M Mo or Tu or

44:01W Etc you get my point right if you have

44:05a requirement where you know that you

44:07have all these values and all of them

44:10have the same length then you can go

44:13with a data type like Car 2 and this way

44:17you won't be wasting a lot of space

44:20because of the variable length of

44:21different different entries of the data

44:24right only go with care if you have a

44:26use case like is but if it is a general

44:29text let's say name of a customer name

44:32of a product etc etc or a description

44:34Etc ET then either go with Vare or text

44:38now how do you choose Vare versus text

44:42what I believe personally is you should

44:43always go with text because if you go

44:48explore the documentation of Whois they

44:52themselves recommend that always use

44:54text which is the model alternative of

44:57Vare without length because most people

45:00what they confus with is if you use Vare

45:04with 255 this is going to perform better

45:08as compared to a field like text or you

45:11cannot index a field uh we'll cover

45:14index shortly but people believe you

45:16cannot index a field of type text there

45:19are a lot of misconceptions around this

45:21because other databases databases like

45:23MySQL SQL Server sqlite they have

45:26different different conventions the way

45:28they operate the way they function are

45:31very different from postgress and when

45:34people switch between different

45:35different databases they get confused or

45:38they are misguided so if you explore the

45:41official documentation of postgress they

45:44recommend that always go with text there

45:46is pretty much no performance difference

45:48between Vare or text and the second

45:51thing which is my personal opinion that

45:54using something like Vare 250

45:57which is by the way a convention from

46:00myql that people from myql which was a

46:04very popular database and that has a

46:07Convention of using Vare 255 and that's

46:11the reason a lot of people still use

46:12this and

46:14255 is at least when it comes to

46:17postgress 255 has a meaning in the

46:21context of a mySQL database but when it

46:23comes to postgress people just use the

46:26number 255 as a random number right it

46:30has no meaning whatsoever in a postgress

46:33database and people just use it without

46:36thinking just because the way it has

46:39always been done and that's why I

46:42personally believe instead of going with

46:43a random number like 255 go with text

46:47and and the reason is let's say later on

46:51you want to increase the length for some

46:53use case let's say you are dealing with

46:56a field called description and you just

46:58set the length is 255 as V so later on

47:03you want to increase the length so to

47:05cater to that requirement you have to do

47:07a database level migration which affects

47:11a lot of data and a lot riskier of

47:13course migrations are performed on a

47:15day-to-day basis but still you should

47:17always avoid a database level migration

47:19if you can right and that's the reason

47:22using something like text and enforcing

47:25the length for whatever it is on your

47:27application Level and since we are not

47:30in the data engineering domain we are

47:32not in the data analyst domain people in

47:35those domains they usually deal with SQL

47:39directly right they have some kind of

47:41interface and they write SQL directly

47:43and they see the result of the SQL data

47:46directly etc etc right SQL is their

47:48first point of interaction but as

47:50backend Engineers when we are dealing

47:52with backend systems we usually interact

47:55with SQL databases

47:57through a driver right we have different

48:01different drivers depending on what

48:02programming language that we are talking

48:04about and that's how we interact with

48:07the database through a driver and we

48:09have a lot of application code when we

48:12are dealing with the data that's the

48:14reason we can enforce the length or

48:17whatever constraint it is on the

48:20application code so that our database

48:23migrations all the database system

48:25database statements are simpler to read

48:28and simpler to maintain so let's say

48:30someone new someone new comes over and

48:33they look at this they see vat 255 and

48:37they think that this has some meaning

48:39even though the person who just created

48:43it they just added it without thinking

48:46right it is just a random number for

48:47them but if someone new comes over they

48:50will think there is some meaning to this

48:52number or there is some meaning to this

48:5410 number they will try to understand

48:56and where this is coming from and since

48:58there is no documentation they'll try to

49:00dig through the code Etc ET there's a

49:02lot of headache that comes with it

49:05because a number a number like this a

49:08number like this this represents that it

49:11has some kind of meaning even though it

49:13doesn't most of the times right so

49:17instead of this if you go with just text

49:20then it is a lot simpler to read a lot

49:23simpler to write these migration

49:25statements all the SQ statements because

49:27you won't have to think about these lens

49:30and all and if someone new comes over

49:32and they read it they just think that it

49:34is a text field it is a character field

49:35right there is no extra meaning to this

49:38so those are some of the concepts

49:40surrounding string Fields string data

49:42types in post moving on we have Boolean

49:46we can store true or false in this field

49:49we have date we can just store the date

49:52in this and we have time where we can

49:54store the time right in hour minute

49:57seconds format then we have time stamp

50:00for storing the information of date and

50:02time at the same time then we have time

50:05stamps with is extra Zed at the end

50:08which just means store the time zone

50:10information also in this so it will

50:12store time stamp and a time zone

50:16information then we have interval for

50:19storing like 10 days or one week etc etc

50:22right those kinds of datas then we have

50:24uu ID U ID is a very a popular choice

50:27when it comes to making table primary

50:30keys right because of their Ur flend and

50:33their unique nature so that's the reason

50:35posg offers a native uid type right

50:38inside the database then we have Json

50:40and Json b as I have mentioned before

50:43postgress offers native Json and we have

50:46an extra type which is called Json B

50:49also known as Json binary and the

50:51primary difference between Json and Json

50:53B is when we use a field called Json

50:57this will be stored as a plain text a

51:01plain text with the key value format

51:03that we usually see when we are reading

51:05Json but when we use this Json B this

51:11will be converted into a different

51:13format postgress will serialize it into

51:16its own native Json format for

51:19efficiency purpose so that it can

51:20perform better it can perform a lot of

51:22query capabilities etc etc right there

51:25something that is limited to postgress

51:28it is not an SQL standard and most of

51:31the times you should go with Json B

51:33because of the performance advantage

51:35that it offers then we have the aray

51:37types you can store an arrow of

51:39different different data types right

51:41store an arrow of integer you can store

51:42an AR of Json date time Etc whatever all

51:45the different different data types that

51:47we have then we have some other random

51:49data types like storing Network

51:52addresses Mac addresses and geometrical

51:54points XML etc etc that we don't really

51:58use on our data basis but if you do want

52:00to use it you can explore the official

52:02documentation and you'll find more

52:04information on that and this is how we

52:06insert data into our database we write

52:09insert into the table name and the Order

52:12of the all the fields that we want to

52:14insert the data in and then we provide

52:18all the values in the same order that we

52:21wrote Our fields in right and this is

52:23how we are inserting all the data this

52:25is an example example small integer this

52:28is an integer big integer as you can see

52:31it is a lot higher than a normal integer

52:34then we have decimal we have real double

52:37Precision which basically floating Point

52:39representations then we have character

52:42Vare and text and we have bullan we can

52:45either pass true or false here we have

52:47date we have time and hour minute

52:50seconds format we have time stamp where

52:53we have the date and the time we have

52:55time stamps you have date time and the

52:57time zone information then we have

52:59interval have uid this this is what a

53:01typical uid looks like we have Json Json

53:04B and while inserting even though Json

53:07and Json B looks the same but postgress

53:10serializes Json B into a different

53:12format for faster processing and faster

53:15retrial right then we have array then we

53:18have IP addresses Mac addresses

53:20geometrical points and XML etc etc right

53:23all the data types that we basically saw

53:25and that's pretty much all about about

53:26all the data types that we are going to

53:28deal with as backend engineers in a

53:31typical database system next up what we

53:33are going to do is we are going to

53:37design the database of a project

53:41management platform in the previous

53:44video we created the apis we designed we

53:48modeled the apis of a project management

53:51platform similarly to understand

53:54different concepts of databases and and

53:56different relevant Concepts not just

53:58theoretical concepts are very Advanced

54:00concept that we don't necessarily use on

54:02a day-to-day basis but very relevant

54:04Concepts in database systems especially

54:06in postgress we are going to design a

54:09database of a project management

54:11platform and we are also going to write

54:14queries for different different apis

54:16using the same databases now before we

54:19get into that just want to bring up one

54:22topic which are known as migrations in

54:26systems especially in production systems

54:29you don't necessarily just open a

54:33software like table plus and you just

54:36start writing queries right you cannot

54:39do that because if something goes wrong

54:42or even if something does not go wrong

54:44but there is no way to track what all

54:47changes that are applied to a particular

54:50databas over time or who applied it

54:53right there is no way to control the

54:56version or the control the state of the

54:59databases across different different

55:01times right and that is the reason most

55:03database systems follow something called

55:06as database migrations now database

55:09migrations are basically different

55:11different files let's say we have 1. SQL

55:15we have a folder which are called

55:17migrations inside the folder database

55:20right you have a folder structure like

55:22this you have DB folder inside that you

55:24have migrations and inside that you have

55:27different different files you have 1

55:29dsql you have 2. SQL you have 3 dsql etc

55:32etc right and in each file you have a

55:36couple of SQL statements normal SQL

55:38statements nothing fancy right you have

55:40create table or create index or drop

55:44table etc etc and with that you have a

55:47tool usually it's a command line tool we

55:49have a lot of command line tools for

55:53performing migrations and maintaining

55:54migrations for example Le we have dbate

55:58or we have go migrate right a lot of

56:01this famous tools now what this tool

56:04does it goes to this location DB

56:08migrations and goes through all these

56:11files in this sequential Manner and

56:14because of that we write the names of

56:17these files in a sequential manner

56:20either we perform some kind of numerical

56:22series 1 2 3 or we add dates uh

56:26timestamps and since time stamps follow

56:29a single uh flow that's the reason we

56:32get sequential files right okay now what

56:36this tool does it goes through all those

56:38files in that sequence and applies all

56:42the SQL statements basically executes

56:45all these SQL queries on that particular

56:47databases whatever database that you

56:49have configured in your environment it

56:52executes all this SQL statements on that

56:55data database inside migration also we

56:58have two kinds of migration we have up

57:00migrations and we have down migrations

57:03now some of the modern approaches of

57:05handling databases they don't really

57:07believe or they don't really Implement

57:09down migrations but this is the standard

57:12way of operating right you have up

57:13migrations up migrations are basically

57:16whatever change that you want to do you

57:17want to create a new table so you write

57:20create table etc etc you want to create

57:22a new type let's say you want to create

57:24a new enum type you write create type

57:27enom etc etc then you want to create

57:29some indexes whatever all the create

57:31statements you write one by one right

57:33with proper SQL syntax or postgress

57:37syntax then depending on the tool let's

57:40say a tool like dbate what it does it

57:44divides the same file the same SQL file

57:47let's say 1. SQL into two sections one

57:49section with a comment for up migrations

57:52and another section for down migrations

57:55now the purpose of down migration is

57:59whatever change you did in your up

58:01migrations whatever change you applied

58:03to the database in the down migration

58:06you have to revert all those changes so

58:09that the reason for having this feature

58:12called down migration is let's say you

58:14applied some migrations you applied some

58:16changes to your database systems and

58:19something goes wrong right or something

58:22breaks in your production system or

58:24anything can go wrong what what you want

58:26to do you want to roll back to previous

58:28version of the database without any

58:30changes right the state has to be the

58:32same for that to happen you have to have

58:35down migrations so that all the changes

58:38that you did in your up migration the

58:39down migrations can rever that so that

58:43you can go to a previous state you can

58:44roll back okay that's how a migration

58:49workflow works you have a tool and that

58:52tool usually creates a table called SK

58:56scha something like that in your

58:57database and using which it tracks that

59:00which is the current version of the

59:02database right let's say you have 1 SQL

59:062. SQL 3. SQL right then in your schema

59:10table you will have something like four

59:12right current schema is four because

59:14four is the latest changes that you have

59:17applied on your database when you come

59:18back and you want to add another file.

59:205. SQL before you apply that file before

59:23you up that migration your current data

59:26schema will still have four right that's

59:29how the tool works you have a migration

59:31tool which goes through all your SQL

59:33files applies all the SQL queries on

59:35your databases one by one if it is an up

59:37migration then it applies all the

59:39changes if it is a down migration it

59:41reverse all the changes for that version

59:43now the question is why do we need

59:44migrations there are a couple of reasons

59:46one reason as I've already mentioned you

59:48cannot just open a graphical tool and

59:50execute SQL statements because that's

59:52very hard to keep track of what are the

59:55all the changes that you made right

59:57unless you want to save it to a

59:59particular file and you want to commit

1:00:00to git etc etc and after all those

1:00:03things you will come up with your own

1:00:05migration system that's why we have this

1:00:07concept called migrations couple of

1:00:09advantages of using migration is one as

1:00:12I said keeping track of

1:00:15database changes keeping track of

1:00:18database changes over time right you

1:00:20have a file you have a lot of files in a

1:00:22particular folder which is committed to

1:00:25your version control system let's say

1:00:27it's git or git lab bit bucket whatever

1:00:30platform that you follow to Version

1:00:31Control your code usually your migration

1:00:33statements stay with your code and you

1:00:36commit it to get GitHub whatever

1:00:38platform you follow and over the time

1:00:41you keep adding new migration files to

1:00:43the same folder and that's how you keep

1:00:46track of all the changes that are

1:00:48happening to your database schema second

1:00:50is roll back as I already mentioned if

1:00:53something goes wrong you can roll back

1:00:55to a particular version so that's pretty

1:00:57much all there is to it migrations are a

1:00:59system using which you apply new changes

1:01:02on top of your databases you can add new

1:01:05tables you can modify tables you can

1:01:07create indexes you can create triggers

1:01:09etc etc right whatever change that you

1:01:13want to perform on a database you have

1:01:16to create a new migration for it because

1:01:19relational databases follow a particular

1:01:23strict schema you cannot just insert

1:01:26data randomly into the databases you

1:01:27have to follow a particular schema

1:01:30that's the reason we have this migration

1:01:31statements to make changes to the

1:01:33database over time using any command

1:01:36line tool now we will start modeling our

1:01:39database for our project management

1:01:41platform and for that we'll write

1:01:43migrations okay here I have my vs code

1:01:46open this is where we'll write all our

1:01:48SQL statements to model our database

1:01:52right so for the sake of migration we

1:01:54are going to use d mate it's a command

1:01:57line migration tool that helps applying

1:02:00all the changes to your database right

1:02:02and for that we have to provide the URL

1:02:05of our database to the tool and for that

1:02:08we have to create a new environment

1:02:10file. EnV and inside that we can create

1:02:13a variable called database URL and the

1:02:16tool will pick up the value of our

1:02:20postgress database URL from this

1:02:22location okay so to create our first

1:02:26migration dbate offers a command you can

1:02:29write dbate new create users St that

1:02:32sounds right so press enter it creates a

1:02:36new folder called DB inside that another

1:02:38folder called migrations and inside that

1:02:41a migration file so as I said it can

1:02:44follow a sequential number like 1 2 3 4

1:02:465 or time stamps whichever sequence that

1:02:49you want to prefer right in this case

1:02:52it's adding a time stamp then the name

1:02:54of our file which is created users table

1:02:57for now it's empty we'll add our

1:02:58migration statements here pretty soon so

1:03:01this is how as I mentioned this section

1:03:05is for writing out down migrations

1:03:07whatever SQL statements that we write

1:03:10after this comment are considered as

1:03:13down migrations and whatever SQL

1:03:16statements that we write before this

1:03:18down migration and after this up

1:03:20migration are considered as our up

1:03:22migrations right now to save some time

1:03:24I'll just paste all the SQL statements

1:03:26and then we can go through that one by

1:03:28one right I've pasted all the SQL

1:03:31queries that are needed to perform this

1:03:35migration the up migrations and create

1:03:37all the necessary tables so let's

1:03:40explore all the statements one by one to

1:03:43understand all the concepts surrounding

1:03:44it the first thing that you notice is we

1:03:48are

1:03:48creating some data types called as enums

1:03:53right now enums are basically all those

1:03:57values for which you have a predefined

1:04:01set of allowed values for example we

1:04:04have a field called status in the

1:04:07projects table and for that we only want

1:04:10to allow these three values the project

1:04:13status can either be active or completed

1:04:16or achieved active or completed or

1:04:19archived right in the same way for tasks

1:04:23in the task table the task status either

1:04:26can be pending in progress completed or

1:04:28cancelled same way for members they

1:04:31either can have the role owner admin or

1:04:34member the reason for using a data type

1:04:37called enum instead of just going with

1:04:40our standard text because first thing

1:04:44data Integrity data Integrity when we

1:04:47say in the context of databases we mean

1:04:50we leave the responsibility of checking

1:04:53the validity the checking the accuracy

1:04:55of the data

1:04:56to the database instead of our

1:04:58application code because we created

1:05:00types these enum types and we used these

1:05:04types let's say we created this type

1:05:07called project status and as you can see

1:05:09we used it here right in the status

1:05:11field that's the type of the status

1:05:13field is our custom inom type which is

1:05:16project status because we did this

1:05:19whenever we insert data or whenever we

1:05:22are trying to update data in the

1:05:24projects table and and in the field of

1:05:27status if we try to insert any random

1:05:30string or let's say we try to install we

1:05:35have active completed or archived we try

1:05:37to inster something something if we try

1:05:40to insert some random string like this

1:05:43we will get a database level error we

1:05:46don't have to check this in our

1:05:47application code we can leave that

1:05:49responsibility for the database we can

1:05:51check it in our application code that is

1:05:54just decreasing the scope of the error

1:05:56but still to make it more robust to make

1:05:59it more secure we can provide a set of

1:06:02allowed values and we can use that

1:06:04custom type for any field then whenever

1:06:08we insert some data whenever we update

1:06:10some data the database will check

1:06:13whether that data whatever value that we

1:06:16are providing for the Project's status

1:06:19whether it is one of these if it's not

1:06:22then we'll get a database level error

1:06:24okay the first thing is data Integrity

1:06:26the second thing is documentation and I

1:06:29think this part the second point is more

1:06:32important than the first point because

1:06:34the first point data Integrity this we

1:06:37can do in our application code right we

1:06:40since we are talking in the context of

1:06:42backends we have a programming language

1:06:44in our hand and we can validate whether

1:06:48whatever status that is coming from the

1:06:49user that is coming from the front end

1:06:51or whatever application code whether it

1:06:53is one of these strings or not completed

1:06:56or archived we can do that in our

1:06:58application code but another advantage

1:07:01of using enums in our migration

1:07:03statements is when you're going through

1:07:05your old migration statements for

1:07:07debugging something or let's say you are

1:07:09unboarding a new team member into your

1:07:11team and they are going through the

1:07:13database migration statements to

1:07:15understand what all changes that the

1:07:17project went through over the last

1:07:19couple of months or the last couple of

1:07:20years and they want to understand right

1:07:23what is the state of the database what

1:07:25are the past states of the database what

1:07:28changes happened over the time etc etc

1:07:31right and when they are doing that when

1:07:33they are going through all the database

1:07:35migration statements if they come across

1:07:37this table called projects and they see

1:07:40the field status and if it is of type

1:07:43text but in our application code we are

1:07:47only doing active completed archived now

1:07:50there is no way for them to find out

1:07:53this piece of information that you

1:07:55cannot put any arbitrary text into your

1:07:57status field it has to be either of

1:08:00these three right so to find out that

1:08:04piece of information they have to go

1:08:05through all the application code they

1:08:06have to see what what are all the places

1:08:09where this field is used and to track

1:08:11down all those use cases then they can

1:08:14boil it down to these three values which

1:08:17is a lot of headache right so using enum

1:08:22type provides some kind of documentation

1:08:24for you for your team members for your

1:08:26future team members so that at one

1:08:29glance you can tell what are all the

1:08:31allowed fields that can be in a

1:08:34particular field which makes everything

1:08:36very easy to understand right so that's

1:08:39all about enum fields we are creating

1:08:42three enum types and this is a syntax we

1:08:44write create type the name of the enum

1:08:48and we write as enum and inside the

1:08:50braces we provide all the allowed values

1:08:52we create three types three enum types

1:08:55and we are going to use this in our

1:08:57future tables now moving on we are

1:08:59creating a users table since it's a

1:09:02project management platform of course

1:09:03we'll have users right so that's the

1:09:06first table we are starting with we are

1:09:08creating a table called users and if you

1:09:11notice this each table has some default

1:09:14fields we have ID we have created it we

1:09:17have updated it same for user profiles

1:09:20you have ID created at updated at for

1:09:22projects we have ID created at updated

1:09:24so each table has this kind of metadata

1:09:27kind of field the ID is not really a

1:09:29metadata it is the primary key and by

1:09:32primary key I mean using this ID we can

1:09:36uniquely identify a particular Row in

1:09:39this table right let's say we want to

1:09:42find out or let's say in our backend API

1:09:45we have a requirement that we want the

1:09:47information of user okay since it's a uu

1:09:50ID let's generate a random uu ID and

1:09:53let's paste it and a comment here here

1:09:58okay now we have a requirement in our

1:10:01back end we have a API which takes the

1:10:04user ID in a dynamic parameter and we

1:10:07have to return the details of that user

1:10:10so this is the ID of that user and we

1:10:13have to find out the information of the

1:10:15user using this ID now if we don't make

1:10:19a particular field let's say the ID

1:10:21field primary key okay we don't assign

1:10:25the

1:10:26condition primary key to a particular

1:10:28field there's a couple of things that

1:10:30can happen this primary key property has

1:10:34some implicit properties those

1:10:36properties are if you write primary key

1:10:38for a particular field that field cannot

1:10:41be null not null and it also has a

1:10:44unique constraint so relational

1:10:46databases have this concept called

1:10:48constraints right you can apply a

1:10:50particular condition particular property

1:10:54to a field

1:10:56and that property or that condition will

1:11:00protect that field from going corrupt

1:11:02okay that's a very high level definition

1:11:04we'll see what that means when we

1:11:06encounter more examples so when we say

1:11:08primary key it means it implicitly has

1:11:11these two properties not null and unique

1:11:13though so this field cannot be null and

1:11:17this will always be unique if there is

1:11:19another entry with the same value gets

1:11:22inserted into this table we'll get a

1:11:23database level errors and because of

1:11:26this implicit properties and some other

1:11:28foreign key Behavior we use this

1:11:32property called primary key for our ID

1:11:34field so each table will have a field by

1:11:38convention called ID and we will assign

1:11:41the property primary Q to that which

1:11:44will help us fetch different different

1:11:46information of the user okay now what we

1:11:49are saying we have a field called ID it

1:11:52is of type uu ID and it will be a

1:11:55primary key for this table and by

1:11:59default if the ID is not provided when

1:12:02an insert statement is executed by

1:12:05default generate a random U ID okay this

1:12:08is what this means this is by default

1:12:10natively provided by postgress so if we

1:12:13don't provide the ID field which we

1:12:15generally don't we leave it to postgress

1:12:17the database system to generate a random

1:12:20ID for us and to assign it to that R

1:12:23next we have another field called email

1:12:26and it is of type text it is not null

1:12:29one thing to keep in mind is by default

1:12:32all the fields in a postgress table can

1:12:35have null values unless you explicitly

1:12:38specify that it cannot have null values

1:12:41More than 70% of your tables Fields

1:12:44should have the not null constraint

1:12:46right because this not null constraint

1:12:50enables the database to have a

1:12:51consistent state if you don't add this

1:12:55then the application can push null

1:12:57values into the table even though you

1:12:59don't do it uh intentionally because a

1:13:03lot of database interactions are also

1:13:06dealt with automated scripts so that

1:13:08script can have a bug and you'll end up

1:13:10with a lot of null values in your

1:13:12database which makes things very very

1:13:14difficult right so for a lot of reasons

1:13:17like that always try to stick to n Nal

1:13:21okay unless you have a reason for that

1:13:24field to be null okay that's the reason

1:13:28we are writing not null here we have a

1:13:30field called email it is of type text

1:13:32and it cannot be null and we have

1:13:35constraint called unique but this means

1:13:37is each door of this table will have a

1:13:40unique email the moment you try to push

1:13:43another row with the same email address

1:13:45that already exists in the table we will

1:13:47get a database level error that it

1:13:50violates the unique constraint okay and

1:13:53since email is something that we don't

1:13:56want to create two users with the same

1:13:58email we are applying this constraint

1:14:00called unique next we have a field

1:14:02called full name it is of type text

1:14:04again not null then we have password

1:14:06hash it is again of type text and not

1:14:09null since we don't directly store the

1:14:12plain text value of the password we hash

1:14:14the password and store it this it's

1:14:16called password hash just a name then we

1:14:19have two other fields create at and

1:14:21update at and the reason is we want want

1:14:25to know when this user was created okay

1:14:28when was the first time this user was

1:14:30created in our database that's the

1:14:31reason we maintain this field called

1:14:33created it and usually we don't manually

1:14:35insert it we are providing this default

1:14:40property and we are saying if created is

1:14:43not passed by default you can take the

1:14:46current time stamp and you can put it in

1:14:49that row okay and it is of type

1:14:52timestamp with time zone okay this is of

1:14:55type time stamp with time zone this will

1:14:58have the date the time and the time zone

1:15:00information same way updated it we

1:15:03create this field so that we can keep

1:15:04track of when was the last time we

1:15:07updated some field of this row okay and

1:15:11that can have some use cases for example

1:15:14if you have a table in your front end

1:15:16and you want to display all the users in

1:15:20the order of when was the last time

1:15:22their name was updated something like

1:15:24this right so in that case you can use

1:15:27this updated it property to sort by and

1:15:31that's all about users table some of the

1:15:33other things to notice here is all our

1:15:36tables have this plural form right we

1:15:39have users we have user profiles

1:15:41projects and that is a standard a lot of

1:15:43people also prefer the singular form

1:15:46user user profile project Etc ET it's a

1:15:50matter of what your team has decided and

1:15:53what your company has decided but the

1:15:56industry standard is to mostly stick

1:15:58with the plural form second thing all

1:16:02the table names and all the field names

1:16:04will be small case and snake case and by

1:16:08that I mean if it is a single word it

1:16:11will have small case of course as we

1:16:13have users ID email right all small case

1:16:17if it has multiple words let's say full

1:16:19name password hash created it update

1:16:22then we'll use snake case if you are

1:16:24coming from other progr programming

1:16:25languages or Etc you might have the

1:16:27instinct to go with uh camel case right

1:16:31you might want to write something like

1:16:32full name with n capital and you want to

1:16:36merge and get rid of the snake case but

1:16:37that is not a good idea because in

1:16:40postgress everything is case insensitive

1:16:43by Nature so even if you write camel

1:16:45case postgress will take it as so if you

1:16:49write something like this will name

1:16:52pogress will understand this will name

1:16:56unless you write it like this full name

1:16:59with quotes and when you're writing your

1:17:02application code using double Cod again

1:17:04and again can be very difficult and make

1:17:07the code very ugly that's the reason we

1:17:09always try to stick with small case we

1:17:12make everything small case and if you

1:17:14have multiple wordss we stick with snake

1:17:16case so that we can avoid using double

1:17:17codes to make postgress understand case

1:17:21sensitivity okay that's some of the

1:17:22things that you have to remember moving

1:17:24on

1:17:25we have another table called user

1:17:28profiles now if you check the commment

1:17:30it says one to one relationship with

1:17:33users now you might have a question like

1:17:36why are we creating a different table

1:17:38for storing information of the user

1:17:41right why don't we store all this

1:17:42information in the same table and the

1:17:45reason for that is you when you're

1:17:46designing a database you have to take

1:17:48into account some of the different

1:17:50different situations that you might

1:17:53modify your data in so if you think

1:17:56about a user the only times you are

1:17:59going to make changes to a users entry

1:18:04in the users table is when they are

1:18:06modifying their profile and their

1:18:09profile can have lot of other

1:18:10information for the sake of this example

1:18:12we have just limited it to their image

1:18:14URL their bio their phone number but

1:18:17user can have other fields also right

1:18:20they can have their social media

1:18:22accounts and they can have their

1:18:25websites and their projects etc etc a

1:18:29lot of different information a user

1:18:30profile can have and because of that we

1:18:32in the future we have to make a lot of

1:18:34migrations to the user table itself and

1:18:39we have to constantly modify the users

1:18:42row again and again and we don't

1:18:46necessarily have to do that if we

1:18:48abstract out the users profile

1:18:51information into a different table which

1:18:53can scale over time which can get bigger

1:18:55over time and which does not affect the

1:18:59primary users table directly these are

1:19:02some of the bu cases that we go with one

1:19:04to one relationship for storing users

1:19:07profile information for storing user

1:19:09preference for storing metadata etc etc

1:19:12right this is a common convention that

1:19:14you will see when it comes to modeling

1:19:16your database so we have a table called

1:19:20user profiles inside this we'll store

1:19:23the information of the user for this

1:19:25what we can do is we don't necessarily

1:19:27have to create another ID field because

1:19:29there is a one to one relationship with

1:19:31another table each entry of that table

1:19:35or each row of that table will only have

1:19:38one row in this table that's why it's

1:19:41called one to one relationship and

1:19:43because of that we can get rid of this

1:19:46ID and we can make the user ID which is

1:19:50the foreign key in this table as the

1:19:53primary key so we can write uu ID then

1:19:57instead of not null we can write primary

1:20:00key and we can also get rid of this

1:20:02unique thing now this user ID field is

1:20:06both the primary key and the foreign key

1:20:09in this table and we have some other

1:20:11fields for storing the image URL the bio

1:20:14the phone number and if you notice we

1:20:16are not writing notal here because these

1:20:18can be optional Fields a user can choose

1:20:21not to have a bio they can choose not to

1:20:24provide the phone number or to have a

1:20:26image customized image etc etc right

1:20:29that's the reason we are not writing not

1:20:31n in these fields okay moving on we then

1:20:35have another table called projects now

1:20:37the same thing happens here we have a

1:20:40primary key with ID and we are

1:20:42generating a default random ID each time

1:20:45we enter a new project into the database

1:20:48same way we have the name of the project

1:20:51which is not null and we have the

1:20:52description of the project which can be

1:20:54null we don't care much of the

1:20:56description but we have to have the name

1:20:58of the project that's why we are writing

1:21:01not null here same way we have a status

1:21:04field which is using our custom enum

1:21:07type is project status is not null and

1:21:11if you don't provide any value for this

1:21:13field by default it will be in the

1:21:15active State okay usually when we are

1:21:18going with enum types we provide a

1:21:20default value with one entries of that

1:21:23enum to make easier for us so that we

1:21:26don't have to explicitly push that value

1:21:28from our application code then we have

1:21:30an owner ID which is a foreign key and

1:21:33we write it like this it is a not n

1:21:36level field and says it references the

1:21:41users table and this is a constraint

1:21:44that on delete restrict and this is what

1:21:47it means this owner ID is the foreign

1:21:50key for stable and it references the

1:21:53user table and usually this is how we

1:21:56write users ID and because the ID field

1:21:59is the primary key in the users table we

1:22:02can omit this part and we can only say

1:22:06references users like we are doing here

1:22:08and it means owner ID references the ID

1:22:13field in the user table users table okay

1:22:17now the second part we are putting a

1:22:19condition here saying that on delete

1:22:21restrict this is also known as refer

1:22:24differential Integrity which basically

1:22:26means that we can protect the data

1:22:29across different tables using the

1:22:32relationship between those tables in

1:22:34this case what we are saying if someone

1:22:37deletes this particular user which user

1:22:41the user whose ID is in this table the

1:22:44project table as the owner ID so let's

1:22:47say we have a users table and we have

1:22:51projects table in ID field we have

1:22:55for the sake of this example let's say

1:22:57we have ID is one and in projects field

1:23:00we have another entry owner ID in this

1:23:03row owner ID is one so this user has

1:23:07created a project that's why in the

1:23:09projects table we have a row which has a

1:23:13field called owner ID and the value is

1:23:15one when we write on delete restrict

1:23:19what it means is if someone tries to

1:23:21delete this user this user whose ID is

1:23:25one the database system will check

1:23:27whether this user has some corresponding

1:23:30rows in projects field since it has what

1:23:34it will do is it will check what is the

1:23:36referential Integrity constraint what is

1:23:38the condition we are mentioning here

1:23:40since we are saying restrict it will

1:23:42fail that operation you cannot delete a

1:23:45user unless you delete the project first

1:23:49that's what it means using referential

1:23:51Integrity we can put some restrictions

1:23:54on some operations on some tables using

1:23:58the relationship between those tables

1:24:00and inside referential Integrity we have

1:24:03couple of constants one is on delete

1:24:06restrict second is on delete Cascade and

1:24:10what on delate Cascade means let's say

1:24:13if we had instead of on delate restrict

1:24:15we had on delate Cascade so when someone

1:24:18deletes a particular user the database

1:24:20system will check if there are entries

1:24:24in the projects table which has owner ID

1:24:26as one it will also delete those

1:24:28projects instead of restricting it will

1:24:30Cascade which means it will delete the

1:24:32user and all the associated projects

1:24:34from the projects table they also have

1:24:36another con condition called set null

1:24:39and set default which basically means if

1:24:42someone deletes a particular user then

1:24:45owner ID in case of set null will be set

1:24:49null but since we are saying not null

1:24:51here and if you try to set null then

1:24:53we'll get a database level error and

1:24:55this operation will not go through same

1:24:57way set default will try to set the

1:24:59default value if some default value is

1:25:02provided while creating the table these

1:25:04are pretty much all the referential

1:25:06Integrity constraints that we use on a

1:25:08day-to-day basis to protect our data

1:25:11from going corrupt to protect our data

1:25:14from going inaccurate right so this

1:25:16basically means we cannot delete a user

1:25:18as long as they have some Associated

1:25:21projects in the project table because of

1:25:24this statement on delete restrict and

1:25:26owner ID is a foreign key and similarly

1:25:28we have created it and updated it moving

1:25:30on we have tasks which another table and

1:25:35in the same way we have a primary key

1:25:37called ID and we have a foreign key here

1:25:40called project ID it is a type uu ID

1:25:44because the primary key of projects is

1:25:48ID and ID is of Type U ID and since

1:25:51we'll be storing the values of ID here

1:25:53this is is also U ID and we are saying

1:25:56not n which means there cannot exist a

1:26:00task without an Associated project ID

1:26:03that cannot be an orphan task that's

1:26:05what we are saying here it cannot be

1:26:06null then we have the foreign key

1:26:08constraint the referential Integrity

1:26:10thing you're saying it references

1:26:12projects and since we have AED the field

1:26:15it will check which is the primary key

1:26:18in Project table since ID is the primary

1:26:19key ID is the forign key here then we

1:26:22have the referential integrity

1:26:24constraint we are saying on delete

1:26:25Cascade now as I've already mentioned on

1:26:29delete Cascade means if we have a

1:26:31project with ID one and if we have a

1:26:36task with id2 and project ID 1 this is a

1:26:39projects row there is a task row and if

1:26:42we delete the projects row with ID one

1:26:45it will check that project one has

1:26:48Associated tasks in the task table so

1:26:51what it will do is since we are redit in

1:26:54on delete Cascade it will delete all the

1:26:57tasks which are associated with the

1:26:59project ID one because of referential

1:27:02Integrity this is a very useful Concept

1:27:04in relational databases then moving on

1:27:07we have the title field is of type text

1:27:09and not null and description which can

1:27:11be null and of type text then we have

1:27:15the priority which is an

1:27:17integer we can only store 1 2 3 4 etc

1:27:20etc and it cannot be null by default if

1:27:24we the application does not pass any

1:27:26values we are setting the value one then

1:27:29in the end we have a constraint of check

1:27:31in po or any relational systems we have

1:27:34different different constraints one we

1:27:37have already seen is unique so for that

1:27:40field in the whole table you can have

1:27:43only one value for a particular field

1:27:46and that value will be unique across the

1:27:48whole table then we have not null which

1:27:52enforces the condition that you cannot

1:27:54set null in this field similarly we have

1:27:57another constraint these are called

1:27:59constraints or conditions that we are

1:28:02putting on a particular field similarly

1:28:05we have check constant which basically

1:28:09using which we can put a particular

1:28:11condition on a field a custom condition

1:28:14and the operation the transaction will

1:28:17only go through as long as this

1:28:19condition is true that's what check

1:28:22means so if we see here you're saying

1:28:26when we are trying to insert the

1:28:27priority field if you do not pass

1:28:28anything set the default as one but if

1:28:31you do pass anything we are checking

1:28:33that the value should be either 1 to

1:28:36five that is the condition that we are

1:28:37putting here the value of priority

1:28:39should be 1 to 5 so that no one can

1:28:42insert some random value like 55 into

1:28:44the priority field we're only

1:28:46considering from value 1 to five moving

1:28:49on we have a field called status and

1:28:52this is a custom enum type and which we

1:28:54created at the start is of task status

1:28:58and it can have pending in progress

1:29:00completed cancelled so the status will

1:29:03have our custom type task status it

1:29:05cannot be null and by default the

1:29:07application if does not pass anything we

1:29:09are setting the value of pending here

1:29:11then we have a due date which is of type

1:29:13date we not considering time here or

1:29:15time zone we're just considering date

1:29:17here then we have assigned to we again

1:29:20have a foreign key condition so assign

1:29:22to is a foreign key which references

1:29:25users ID from the users table and the

1:29:29constraint the referential Integrity

1:29:30constraint that we are putting here is

1:29:33if the user of that ID is deleted and

1:29:36that users's ID is present in the

1:29:39assigned to field in the task table then

1:29:41for that row we are setting the value of

1:29:44assigned to as null that's what this

1:29:46means and similarly we have created it

1:29:48and updated it the nice thing about the

1:29:51foreign key constraint is because you

1:29:53have written that assigned to or in this

1:29:57case owner ID right since these are

1:30:00foreign keys and by default relational

1:30:02databases have this constraint called

1:30:04foreign key constraint because of that

1:30:06you cannot enter anything into the

1:30:09assigned to field if you try to enter

1:30:12some random string or some random uu ID

1:30:14it can be a valid uu ID but that U ID

1:30:18has to be present in the users table as

1:30:21a valid ID if that is not present then

1:30:24your insertion will fail okay that's

1:30:27what we mean by Foreign key constraint

1:30:29if you write references users this make

1:30:33sure that this U ID whatever uid that

1:30:35you are passing for this field this has

1:30:37a corresponding user entry in the users

1:30:40field another thing to notice here is

1:30:42this pattern is called one to many

1:30:45because we are writing project ID here

1:30:48which is a foreign key which references

1:30:50the ID field in the proess table which

1:30:52basically means that if we have a

1:30:54project with id1 that can have multiple

1:30:58tasks right multiple tasks in the task

1:31:01table which refers to this project using

1:31:05the field project ID in the task table

1:31:08right it can it will be one here and

1:31:11same way it will be one here it will be

1:31:13one here it will be one here right one

1:31:15project can have multiple tasks in the

1:31:18task table that's why it's called one to

1:31:20many relationship and this is how we

1:31:23implement it

1:31:24the first kind of relationship which is

1:31:26one to one relationship the way we

1:31:28Implement that is we take the primary

1:31:31key of the main table and we create

1:31:34another table and we make the primary

1:31:37key of the main table as the primary key

1:31:40of the second table also but instead of

1:31:42writing just ID we write the name of the

1:31:45table with ID so because we had the

1:31:48table called users for a one toone

1:31:51relationship with the user profiles

1:31:53table what we did we wrote ID and we

1:31:56used the users primary key from this

1:31:59table as a primary key in this table and

1:32:01instead of just writing ID we wrote it

1:32:03as user ID is the primary key in this

1:32:05table this is how we implemented one to

1:32:07one relationship and the way we

1:32:10Implement one to many relationship is we

1:32:12take the ID field and we create another

1:32:16table but we don't make that as a

1:32:18primary key we keep that as a foreign

1:32:19key only and we refer to that using the

1:32:24ID primary key right that's how we

1:32:27Implement one to many relationship in

1:32:29the next one we'll see how we Implement

1:32:31many to many relationship so moving on

1:32:34this is the last table it's called

1:32:36project members the reason we are

1:32:38creating this table is we want to keep

1:32:41track of what are all the projects that

1:32:43a user is part of at the same time we

1:32:46also want to keep keep track of what are

1:32:49all the users that are associated with

1:32:51the project so you can look at it both

1:32:55ways and when you have a condition like

1:32:57that when you have a condition when you

1:32:59take the same thing and you can look at

1:33:02it both ways and it makes sense that's

1:33:05how we know that it is a many to many

1:33:07relationship because if we have a user

1:33:10that user can be part of multiple

1:33:13projects at the same time right you can

1:33:14be part of multiple projects at the same

1:33:16time same way if we have a project that

1:33:19project can have multiple users at the

1:33:22same time right a project can not be

1:33:24limited to a single user same way user

1:33:26cannot be limited to a single project

1:33:28that's why projects and users have many

1:33:31to many relationship and we want to keep

1:33:34track of that relationship by creating a

1:33:37different table and usually we call this

1:33:40table as linking table and we implement

1:33:43this pattern when we are implementing

1:33:46many to many relationship okay by

1:33:49creating a different table for the sake

1:33:51of maintaining the relationship with

1:33:53between two tables that's why it's

1:33:55called a linking table and this is how

1:33:57we do it okay we have to make some

1:33:59changes to that instead of creating an

1:34:02ID here what we'll do we'll remove this

1:34:06we'll remove this row we'll take foreign

1:34:09keys from both the tables so what we'll

1:34:12do is we have project ID here and we

1:34:15have user ID here project ID is a

1:34:17reference to the ID field in the project

1:34:20table and we are seeing on delete

1:34:21Cascade as the referential integrity

1:34:23constraint same way user ID is a foreign

1:34:26key which references the users table and

1:34:29the referential Integrity constraint is

1:34:31the same thing on delete casket which

1:34:32basically means if the project is

1:34:34deleted delete all the entries in the

1:34:36project members table same way if the

1:34:38user is deleted delete on the ENT in the

1:34:40project members table okay and in the

1:34:43end what we'll do instead of writing

1:34:45unique we'll write primary key primary

1:34:48key and what this means is this is a

1:34:51composite primary key in the Technic

1:34:53terms so it is a combination of two

1:34:56fields which are foreign Keys

1:34:58referencing primary keys of two other

1:35:00tables and we take those two and we

1:35:03create the primary key so if a user is

1:35:07part of a project then that will have

1:35:10one entry in projects members table

1:35:13let's say you have users table and you

1:35:16have projects table okay and you have

1:35:21users 1 2 3 and projects 1 2 2 three and

1:35:25we want to maintain the relationship

1:35:26that let's say through some user Journey

1:35:30user one was added to project and we

1:35:33want to maintain this relationship in

1:35:34our projects members table what we can

1:35:37do we take project ID since it's a many

1:35:40to many relationship what we do we take

1:35:42the project ID which is two okay this is

1:35:46the project ID and you take the user ID

1:35:50which is one right take the user ID is

1:35:53one and this combined will have one

1:35:56entry in the project members

1:35:58relationship because user one being part

1:36:02of project two is a single event right

1:36:05that cannot be duplicated user one

1:36:07cannot be part of project two multiple

1:36:09times same way project 2 cannot have

1:36:12user one multiple times okay that's the

1:36:15reason what we did we took this

1:36:17combination project two and user one we

1:36:21took this we said that these two

1:36:24combined will be the primary key for

1:36:26this table because primary key

1:36:29implicitly enforces some constraints

1:36:32constraints like unique and not null so

1:36:36what we are saying this combination can

1:36:39only have one entry in this table which

1:36:42will be a unique entry and that cannot

1:36:44be null and because it is a primary key

1:36:47using this combination we can uniquely

1:36:49identify this Row in this table okay

1:36:52this is the CR of how we Implement many

1:36:55to many relationship in postgress or any

1:36:58relational database using a linking

1:37:00table which will have a composite

1:37:03primary key of the two tables that we

1:37:05are trying to link the rest of the

1:37:07things remain same we have created it

1:37:09and updated that and we have a role

1:37:10field which is again a custom enum so we

1:37:14are saying it is a member role which we

1:37:16have created if we go to the top you're

1:37:18saying member role it can be either

1:37:20owner admin or member Okay so

1:37:24if a user is part of a project in that

1:37:27project whatever characteristics that

1:37:29user will have or whatever project

1:37:32characteristics for that user it will

1:37:34have all those informations we can store

1:37:37in the project member St and because

1:37:40role is specific to the situation where

1:37:43a user is part of a project that's why

1:37:45we have we have kept the information of

1:37:47role in this table okay and by default

1:37:51we are setting the role member and that

1:37:53that's pretty much all our up migrations

1:37:57same way as I mentioned we have to

1:37:59mention our down migrations all the

1:38:01changes that we did we created a couple

1:38:03of types we created a couple of tables

1:38:06now in the down migrations what you have

1:38:08to do we have to revert that so that

1:38:10when we want to roll down to a

1:38:12particular version using these

1:38:13statements our database can do the roll

1:38:16back okay we have to explicitly say that

1:38:19these are the down migrations that the

1:38:21database tool the migration tool can use

1:38:25to roll back to a previous version okay

1:38:28and we are simply dropping all the

1:38:30tables and after dropping all the tables

1:38:32we are dropping all the types in the

1:38:34reverse order that we created them

1:38:35that's pretty much all the tables okay

1:38:38so let's go ahead and apply these

1:38:41migrations to our database so that we

1:38:43can see if it works or not and to apply

1:38:46it dbat has a command you can write DB M

1:38:49and you can write up okay and here

1:38:52applying this migration file and it says

1:38:54applied in this

1:38:56milliseconds and then we go here if we

1:38:59do a refresh and you can see you expand

1:39:02it a little bit these are all the tables

1:39:05that we just created you can go here and

1:39:07you can check the schema it has project

1:39:09ID user ID role C project all the

1:39:12schemas right since we have not inserted

1:39:14any databases yet all these entries are

1:39:17empty all these tables are empty and you

1:39:20can also see there is a special table

1:39:22called schema migrations and this one is

1:39:25created by dbit as I've already

1:39:28mentioned all these migrations tools

1:39:31they have to keep track of what is the

1:39:33current state of the database so that

1:39:36they can decide what migrations to apply

1:39:40right so after this after our first

1:39:42migration if you create a different file

1:39:45the database tool will check what is the

1:39:47current version and it will start

1:39:49applying the migrations from that point

1:39:51on instead of duplicating the Mig s and

1:39:55because if it tries to execute the same

1:39:57statements again it will get an error

1:39:59from postgress that this table already

1:40:01exists you cannot run the same

1:40:02statements again that's the reason the

1:40:06migration tool maintains a table call

1:40:09schema migration and in that it has a

1:40:13single field called version and it keeps

1:40:15tag of what is the current version of

1:40:17the migration that we have now that we

1:40:19have our tables ready we want to insert

1:40:22some data into it now in production

1:40:24environments when you actually deploy

1:40:26this back end you will have normal user

1:40:29flows right people will come to your

1:40:31platform they'll use the front end to

1:40:34sign up with forms and they'll create

1:40:36projects with different different forms

1:40:37you have this pre defined flows when the

1:40:41data will come into your database but

1:40:44when you're creating your backend when

1:40:46you're modeling your data for a backend

1:40:48engineer when you want to test all those

1:40:51changes then you want to see how the

1:40:54user interactions will look like

1:40:56basically for testing purposes in

1:40:58development environments you want to put

1:41:00some test data into your database so

1:41:03that you have something to test with

1:41:05before you deploy whatever changes that

1:41:08you made in the database for that

1:41:10purpose we have a concept called as

1:41:13seeding and seeding basically means you

1:41:16write some script usually you create

1:41:19another migration or you can put it in

1:41:22the same migration also but the best

1:41:23practices say you create different

1:41:26migration file for seeding data seing

1:41:29test data test data into your database

1:41:32and it's called sitting basically seting

1:41:34means putting some test data into your

1:41:36database for testing purposes in

1:41:38development environments so let's create

1:41:41another migration file with the command

1:41:44dbate new seat data SE data and when you

1:41:49press enter it creates another file

1:41:51called the time stamp stamp and the seat

1:41:54data because this time stamp is greater

1:41:55than the previous one these will be

1:41:58sequentially arranged and now this is

1:42:00end empty what I'll do is I'll again

1:42:03paste some SQL statements to save some

1:42:06time then we can go through what's

1:42:07happening there how we are inserting the

1:42:09data for seeding purposes these are all

1:42:12the SQL queries using which we are

1:42:15putting some test data into our database

1:42:17into our tables so there is nothing

1:42:19special here basic SQL queries if you

1:42:22have already explored the basics of SQL

1:42:24the basics of postgress they'll you

1:42:26understand it if you not then please do

1:42:28it it'll make understanding this video

1:42:30much easier so what we doing we are

1:42:33using a CT also called as common table

1:42:35expression to make it more readable so

1:42:37we are creating an intermediate table

1:42:40called inserted users and in that we are

1:42:43inserting some test data into the users

1:42:46table taking some sample emails some

1:42:48names some password hashes and we're

1:42:51inserting it and we returning two fields

1:42:54for this SQL query execution the IDS and

1:42:57the emails of the inserted users and we

1:43:00are taking that and this is an

1:43:02intermediate tib called inserted

1:43:05profiles what we are doing here is we're

1:43:07taking the inserted users table and

1:43:11using this from inserted users we are

1:43:14doing select select the ID of the user

1:43:18and we putting the image since the

1:43:21Avatar URL you have mentioned here we

1:43:23are putting some static image URL and

1:43:26for the bio we are doing a case wi so if

1:43:29email is something like this then put

1:43:31this bio if email is something like this

1:43:33then put this bio for this put this bio

1:43:36otherwise put some logic to put

1:43:39different different bios for different

1:43:41different users then for the phone field

1:43:44we are just inserting some random phone

1:43:47number then moving on same way we are

1:43:51inserting some project

1:43:53and etc etc using SQL queries just

1:43:57normal CD so let's go ahead and apply

1:44:01these migrations on our database so that

1:44:03we have some data to test with for

1:44:06applying the migrations we can do dmate

1:44:08up and we do it it applies seeds all the

1:44:12data into our database and we can go to

1:44:15our tool and we can open any table so as

1:44:19you can see projects has some entries

1:44:22you can

1:44:23click on a row projects project members

1:44:27users all the tables have some some

1:44:29entries and you can check the entries of

1:44:31the table for users we have this ID this

1:44:35email this full name password hash for

1:44:37the second user we have these entries

1:44:40when you go to user profiles we have

1:44:42this user ID after URL bio for the

1:44:45second user this is the BIOS third user

1:44:48this is the bio etc etc like same way we

1:44:50also have projects right so our CD is

1:44:53successful and we have some data so that

1:44:57we can start testing now now let's say

1:45:01we have created the tables we have done

1:45:04some seeding and now we want to build

1:45:08some apis for the first API that you

1:45:10want to build is you want to fetch the

1:45:13list of all users and you want to send

1:45:15it to the front end for that we can

1:45:17write a simple query and do select from

1:45:20users and we can execute this and we'll

1:45:23get all the entries right the all the

1:45:26users that exist in the users table now

1:45:29what you want to do is for each user we

1:45:32also want to send the profile

1:45:34information which is on a different

1:45:36table right we have the user profiles

1:45:39table so for each user we want to fetch

1:45:42the profile information from the user

1:45:44profiles table and you want to embed

1:45:46that into the user row and you want to

1:45:50send it to the front end in a single API

1:45:52call we use we don't want the user to

1:45:55make a different call for fetching the

1:45:57profile information that's why we are

1:45:58embedding the profile information before

1:46:01we send it so let's write the query for

1:46:03that usually when we write SQL queries

1:46:06always start from from section right

1:46:08instead of the select section so that

1:46:10you know exactly where you are drawing

1:46:12your data from so we write from

1:46:16users you and this is called an Alas and

1:46:21assigning an alas usually a one letter

1:46:24or two letter alas to a particular table

1:46:26name inside an SQL query makes it more

1:46:29convenient to refer to that table again

1:46:32and again in that query right that's why

1:46:34we use Al SS now from users table we

1:46:37want the information of the user next

1:46:40thing what we want is we want user's

1:46:43profile information which is in a

1:46:45different table okay and it is in the

1:46:49user profiles table now the thing that

1:46:53connects these two tables as you already

1:46:55know is the foreign key so each user

1:46:58profile entry each row in the user

1:47:00profiles table has a field called user

1:47:04ID which is a foreign key which refers

1:47:07to the primary key of the user table so

1:47:09what we can do we can use the join

1:47:12operation to join the users table with

1:47:15the user profiles table on a condition

1:47:17which is the user ID should be the same

1:47:20okay and to write that we can say left

1:47:23join and the reason we are using left

1:47:26join is because for some reason if the

1:47:29user profile in the user profiles table

1:47:32there is not an entry for a particular

1:47:35user okay it is possible in our in our

1:47:38backend system it is possible that the

1:47:40user has never edited their user profile

1:47:43that's why we have never created an

1:47:44entry for them in the user profiles

1:47:46table so in these situations if we go

1:47:50with inner join we will get an mty

1:47:53result because what inner join does each

1:47:55condition for a particular condition

1:47:57there should be an entry for both the

1:47:59tables okay since in this case we want

1:48:03the user information in any case right

1:48:06in any case does not matter whether they

1:48:08have the profile information in the user

1:48:10profiles table or not but we want the

1:48:12user information anyway that's the

1:48:15reason we are going with left joint so

1:48:17that even if there isn't an entry in the

1:48:19user profiles table we'll still get the

1:48:22value of the users information from the

1:48:24users table okay that's the logic behind

1:48:27going with left joint and most of the

1:48:29times we'll be using inner joints and

1:48:32left joints then we are writing SQ

1:48:34queries those are the mostly used joints

1:48:36you want to join with the user profiles

1:48:39table and we'll name it alas up and the

1:48:43condition for the joining is on the join

1:48:47condition is u. ID the ID field in the

1:48:50users table should be the same as the

1:48:54user ID field in the user profile table

1:48:57this is the condition okay this is the

1:48:59condition on which we are joining both

1:49:01these tables now we know where we are

1:49:04drawing our data from okay we have

1:49:05joined two tables we have an join table

1:49:08and now what we can do we can select in

1:49:11the select phase we'll see what are the

1:49:13data that we want to fetch now that we

1:49:16know where we are drawing our data from

1:49:18we'll see what data we are drawing from

1:49:20what we'll do we want to fetch

1:49:23everything from the user R so we'll say

1:49:26U do star comma we want to add another

1:49:30field in each row called profile okay so

1:49:35for that field what you want you want

1:49:38the corresponding Row from the user

1:49:40profiles table and you want to convert

1:49:44it into a Json and we want to embed it

1:49:47into a field in the user row called as

1:49:50profile okay for that what we can do we

1:49:54write 2 Json B which basically converts

1:49:58a particular row into a Json it is a

1:50:01inbu function provided by pogress and we

1:50:04can use that to convert it and we can

1:50:06write take up. STAR which means take the

1:50:10corresponding Row from the user profiles

1:50:12table convert it into a Json and name it

1:50:15as profile before returning okay we have

1:50:18our query now now let's try running it

1:50:21okay and when we run it let's see what

1:50:25the data looks like and this is what the

1:50:28data looks like we have all the fields

1:50:30from the users table we have ID email

1:50:32full name password hash created at

1:50:34updated that and we have an extra field

1:50:37called profile which is a Json and that

1:50:40has all the information from the user

1:50:42profiles table okay now using this query

1:50:45in a single API we can send the users

1:50:49data and the user profile data from a

1:50:51single database call okay using joints

1:50:54another thing to notice here is whatever

1:50:56data whenever we run a particular query

1:50:59in any relational data the data that we

1:51:03get returned is in a random format they

1:51:05don't follow any particular format so

1:51:08because of that whenever we run a select

1:51:11query whenever we are returning a list

1:51:14of data points into our front end we

1:51:16have to sort it by default using some

1:51:19column and usually what we do we sort it

1:51:22by the

1:51:23created it column because we want to

1:51:25return the data in the reverse order of

1:51:28when they were created so that the user

1:51:30can see all the latest users so for that

1:51:33what you can write order by U do created

1:51:36at you want to take the created at field

1:51:39from the users table and we want to sort

1:51:41by that and we want to sort by

1:51:43descending because we want the latest

1:51:45entries and when you do that and run it

1:51:49we get all the users in the reverse

1:51:51order of how or when they were created

1:51:55okay and this is what the query for an

1:51:59API of get all users looks like so if we

1:52:03have an AP something like this

1:52:06slv1 SL users if we have an endpoint

1:52:11which is a get endpoint our backend can

1:52:14use a query like this a database query

1:52:17like this to return all the data to the

1:52:19user of course there will be other

1:52:21processing that will happen this the

1:52:22serialization Der serialization

1:52:23depending on what language that you are

1:52:25using if you're using something like

1:52:27JavaScript or typescript or nodejs then

1:52:30the serialization overhead is not that

1:52:32much but if you're using something like

1:52:33go then it has to deserialize into its

1:52:36own struct then that struct will be sent

1:52:40by serializing into a Json format before

1:52:43it gets sent over the Internet to our

1:52:46front end right this is what a typical

1:52:49get all users endpoint looks like now

1:52:51let's see another end point which is

1:52:53something like slv1 SL users SL a

1:52:58particular user ID okay so we'll write

1:53:01it as user ID we'll get it as a dynamic

1:53:04parameter in our API and we can pass

1:53:07that user ID to our database query as a

1:53:10parameterized query we'll see what

1:53:12parameterized query means but if you

1:53:14don't understand this part please was

1:53:16the previous video where we have done a

1:53:19deep dive on API designing and what are

1:53:21Dynamic parameters how we design these

1:53:23API end points etc etc so let's jump on

1:53:26what this query will look like catching

1:53:29the information the profile information

1:53:31and the user information of a particular

1:53:33user given a user ID from our Dynamic

1:53:36parameter before we get into that let

1:53:41understand what is a

1:53:45parameterized queries parameterized

1:53:47query is basically a safety mechanism

1:53:51provided by databases which basically

1:53:53means before running a query you can

1:53:57provide a particular slot can provide a

1:54:00particular slot and you tell the

1:54:03database that this is your query and at

1:54:07this position there is an empty slot but

1:54:11before I execute this query I will

1:54:13provide the information in this slot now

1:54:16the catch is whatever information I

1:54:18provide here it will be a string you can

1:54:22cannot pass it you cannot pass it to

1:54:26render it as a database action call

1:54:30right it will just be a string so if

1:54:33someone passes something very dangerous

1:54:36like so we have a query and inside the

1:54:40slot if you pass like let's say delete

1:54:42from users a query which deletes

1:54:44everything deletes all the users in the

1:54:46database that will not be considered as

1:54:49a SQL query because the way

1:54:52parameterized query works is whatever

1:54:54you pass in this slot they are

1:54:56considered as a string okay they are

1:54:59escaped in the technical term and it is

1:55:01a safety mechanism to avoid situations

1:55:05avoid vulnerabilities like SQL injection

1:55:08where if you are constructing your query

1:55:12instead of using parameters you are

1:55:14constructing by concatenating Different

1:55:16Different Strings you constructing your

1:55:18SQL query instead of using parameters

1:55:21then you can be a victim to SQL

1:55:24injection okay so this is a very

1:55:26important concept you can do your own

1:55:28research to understand how SQ injection

1:55:31Works etc etc but to simplify everything

1:55:34whenever you have a dynamic value in

1:55:36this case we want to fetch the

1:55:39information of a single user using the

1:55:41ID of that user so the ID of the user is

1:55:44the dynamic value here so what we want

1:55:47to do we want to keep a particular slot

1:55:50in our query and we tell the database

1:55:53that when we run this query we are going

1:55:55to provide the ID of the user but for

1:55:57now it is just an empty slot and when we

1:56:00provide the ID it will it should just be

1:56:02considered as an user's ID just a simple

1:56:04string nothing else no more power should

1:56:07be given to this particular string okay

1:56:11that's the use of parameterized query it

1:56:14makes your query more secure to execute

1:56:17and most of the times whatever database

1:56:19driver whatever library that you are

1:56:21using whether in nodejs or go rust

1:56:24python whatever it is your database

1:56:27driver your OM they have capabilities to

1:56:31handle all these slots with parameters

1:56:33and etc etc since we are not getting

1:56:36into code or any specific programming

1:56:38language here we'll be looking at how

1:56:41parameters work in the SQL editor itself

1:56:45fortunately the this table plus the

1:56:48software that we are using to write our

1:56:51queries and to see our results etc etc

1:56:54they are providing an interface to

1:56:56provide parameters to our query so we

1:56:58can see how it works but in real world

1:57:02when you're actually building backend

1:57:04you'll handle parameters using your

1:57:06library using your driver whatever

1:57:08driver that you are using okay with that

1:57:11let's write our query to get the

1:57:13information of a single user for that we

1:57:16don't really need to change much we

1:57:18still want all the information of the

1:57:19user from the users table we still want

1:57:21all the information of the user from the

1:57:22profiles table using this Json

1:57:25conversion the only thing that will

1:57:26change is we want to filter by users's

1:57:29ID when a single users information now

1:57:32for that we want to add a where clause

1:57:34and write where users ID u. ID is equals

1:57:39to a parameter so this is where we are

1:57:41assigning a slot an empty slot we are

1:57:44telling that this is the name of the

1:57:46variable is called user ID and this is

1:57:50the whole query and we are saying that

1:57:52this is the query and this is an empty

1:57:54slot and when we run this query we'll

1:57:57provide you the value of this slot so

1:57:59that you can do your comparison and show

1:58:01us the results so let's run this and we

1:58:06are shown this dialogue so that we can

1:58:09pass the actual value of the user ID but

1:58:12as I said in your actual backend

1:58:15environment you'll be handling this in

1:58:17code in your particular driver or

1:58:19Library so for this let's let wrap this

1:58:23we have to wrap it in single codes right

1:58:27all the strings in SQL have to be

1:58:31wrapped in single code to be interpreted

1:58:34as strings that's why we are wrapping it

1:58:36in string and we are pasting The UU ID

1:58:39of the user okay this is the U ID I have

1:58:42pasted it here and then let's run this

1:58:46and when we run this we get the