Self Hosted Low-Code - N8N with Postgres & Docker!

so I'm having a bit of a day off today so I'm having a bit of a day off today and I'm tinkering with my home lab again and I'm tinkering with my home lab again and I'm tinkering with my home lab again and one thing that I had been meaning to and one thing that I had been meaning to and one thing that I had been meaning to try out is try out is try out is n8n this is if you know what zapir is n8n this is if you know what zapir is n8n this is if you know what zapir is this is basically an open source or a this is basically an open source or a this is basically an open source or a free edition of zapir well not an free edition of zapir well not an free edition of zapir well not an addition it's something different but it addition it's something different but it addition it's something different but it it's very similar so it's a low code it's very similar so it's a low code it's very similar so it's a low code platform and I used to be really platform and I used to be really platform and I used to be really skeptical about these things like sair skeptical about these things like sair skeptical about these things like sair like why don't you just write your own like why don't you just write your own like why don't you just write your own code but I was code but I was code but I was kind of forced to start using zapir kind of forced to start using zapir kind of forced to start using zapir because it integrates with my school because it integrates with my school because it integrates with my school platform and I was actually really platform and I was actually really platform and I was actually really surprised how much you can do with it surprised how much you can do with it surprised how much you can do with it and how how how much fun it actually is and how how how much fun it actually is and how how how much fun it actually is so I have this project that I've been so I have this project that I've been so I have this project that I've been meaning to do is to read in my aura data meaning to do is to read in my aura data meaning to do is to read in my aura data I wear an aura ring to track my sleep I wear an aura ring to track my sleep I wear an aura ring to track my sleep and I had a project to take all of that and I had a project to take all of that and I had a project to take all of that data and put it in a postgress database data and put it in a postgress database data and put it in a postgress database so I was doing this with python and it so I was doing this with python and it so I was doing this with python and it was working was working was working it's working well but I was now sitting it's working well but I was now sitting it's working well but I was now sitting down and trying to or I'm going to put down and trying to or I'm going to put down and trying to or I'm going to put everything in a Docker image Etc set up everything in a Docker image Etc set up everything in a Docker image Etc set up a different repo but I saw Brian jenss a different repo but I saw Brian jenss a different repo but I saw Brian jenss do uh something similar like this I do uh something similar like this I do uh something similar like this I highly recommend you check him highly recommend you check him highly recommend you check him out and he he was using n8n to put out and he he was using n8n to put out and he he was using n8n to put things in a postest database so I things in a postest database so I things in a postest database so I figured I'm just going to play with n8n figured I'm just going to play with n8n figured I'm just going to play with n8n and see how it works because what I have and see how it works because what I have and see how it works because what I have is I have my Health dashboard here I'm is I have my Health dashboard here I'm is I have my Health dashboard here I'm now tracking my body fat percentage body

now tracking my body fat percentage body now tracking my body fat percentage body weight Etc and I want to get a more weight Etc and I want to get a more weight Etc and I want to get a more comprehensive dashboard in my kubernetes comprehensive dashboard in my kubernetes comprehensive dashboard in my kubernetes home lab for my health which will have home lab for my health which will have home lab for my health which will have to include my sleep data among other to include my sleep data among other to include my sleep data among other things so I am going to be things so I am going to be things so I am going to be playing with this uh repository that I playing with this uh repository that I playing with this uh repository that I found is from the official n8n um found is from the official n8n um found is from the official n8n um project and they have this with project and they have this with project and they have this with postgress directory in this repo postgress directory in this repo postgress directory in this repo basically it starts up n8n with a basically it starts up n8n with a basically it starts up n8n with a postgress deployment so I I already went postgress deployment so I I already went postgress deployment so I I already went through a round of this just to prepare through a round of this just to prepare through a round of this just to prepare a little bit so I know I know how it a little bit so I know I know how it a little bit so I know I know how it works but I'm going to just set it up works but I'm going to just set it up works but I'm going to just set it up from scratch again to see if I really from scratch again to see if I really from scratch again to see if I really understand it and to show you how easy understand it and to show you how easy understand it and to show you how easy it is to work with this so I will go to it is to work with this so I will go to it is to work with this so I will go to my my my repost and I'll go to the n n repost and I'll go to the n n repost and I'll go to the n n repo and into the docker compost with postgress directory so there's an N with postgress directory so there's an N file here which I have file here which I have file here which I have um adjusted a bit um adjusted a bit um adjusted a bit postgress postgress postgress so this is just adding the post user and so this is just adding the post user and so this is just adding the post user and also creating a non-root user which is also creating a non-root user which is also creating a non-root user which is good practice but I don't really use good practice but I don't really use good practice but I don't really use that um in these types of environments that um in these types of environments that um in these types of environments and then the doer compost. yo basically and then the doer compost. yo basically and then the doer compost. yo basically sets up the postgress database it has a sets up the postgress database it has a sets up the postgress database it has a nice initialization script script and nice initialization script script and nice initialization script script and then it sets up n8n so all we need to do

then it sets up n8n so all we need to do then it sets up n8n so all we need to do according to this is Docker compose upd according to this is Docker compose upd according to this is Docker compose upd so this is just going to set up the the so this is just going to set up the the so this is just going to set up the the contents of the docker composed. contents of the docker composed. contents of the docker composed. yaml and pull those yaml and pull those yaml and pull those images and when that is done then we images and when that is done then we images and when that is done then we should be able to reach should be able to reach should be able to reach our application on our application on our application on Port 5678 that's it 5678 Port 5678 that's it 5678 Port 5678 that's it 5678 so I'm I'm doing this with do compose so I'm I'm doing this with do compose so I'm I'm doing this with do compose first on my local machine just to get it first on my local machine just to get it first on my local machine just to get it up and running quickly because the next up and running quickly because the next up and running quickly because the next step will be to just deploy that on my step will be to just deploy that on my step will be to just deploy that on my kubernetes cluster but I just wanted to kubernetes cluster but I just wanted to kubernetes cluster but I just wanted to try out first before I go through try out first before I go through try out first before I go through writing all the manifests and such like writing all the manifests and such like writing all the manifests and such like then I just like to do it with locally then I just like to do it with locally then I just like to do it with locally with Docker so Local Host and then 5 six with Docker so Local Host and then 5 six with Docker so Local Host and then 5 six 7 8 here is s and end deployment so I'll 7 8 here is s and end deployment so I'll 7 8 here is s and end deployment so I'll just do test just do test just do test test.com Miss Vandenberg hello YouTube One exclamation Vandenberg hello YouTube One exclamation mark mark mark next what are we we are next what are we we are next what are we we are a software as a service I'm a business a software as a service I'm a business a software as a service I'm a business owner myself less than 20 blah blah blah owner myself less than 20 blah blah blah owner myself less than 20 blah blah blah blah blah blah blah okay Noe thank you okay so here we go okay Noe thank you okay so here we go here is our complete um n8n deployment here is our complete um n8n deployment here is our complete um n8n deployment super easy right so this is now running

super easy right so this is now running super easy right so this is now running in a Docker container on my local in a Docker container on my local in a Docker container on my local machine I can do Docker PS and then I machine I can do Docker PS and then I machine I can do Docker PS and then I see that I have the NN container and see that I have the NN container and see that I have the NN container and there is a an old container up here as there is a an old container up here as there is a an old container up here as well so I will just um start start from well so I will just um start start from well so I will just um start start from scratch scratch scratch basically and as I mentioned I already basically and as I mentioned I already basically and as I mentioned I already had some code from before but what I'm had some code from before but what I'm had some code from before but what I'm going to do is I'm just going to search going to do is I'm just going to search going to do is I'm just going to search search for curl and then we we we see search for curl and then we we we see search for curl and then we we we see that it has all of these triggers and that it has all of these triggers and that it has all of these triggers and Integrations here already so it does Integrations here already so it does Integrations here already so it does even have an aura integration as well even have an aura integration as well even have an aura integration as well but I believe that this is using the old but I believe that this is using the old but I believe that this is using the old um here get activity summary I think um here get activity summary I think um here get activity summary I think this is using the old API actually so I this is using the old API actually so I this is using the old API actually so I might have to do the I'm just going to might have to do the I'm just going to might have to do the I'm just going to do it myself so what I'm going to do is do it myself so what I'm going to do is do it myself so what I'm going to do is I'm going to do an HTTP request I'm going to do an HTTP request I'm going to do an HTTP request and then um for the method we will do and then um for the method we will do and then um for the method we will do get and then the URL I have that in my get and then the URL I have that in my get and then the URL I have that in my old code over here so that's the end point of the aura here so that's the end point of the aura API and then what's great is that it API and then what's great is that it API and then what's great is that it comes with these predefined credential comes with these predefined credential comes with these predefined credential types and it's just air table everything types and it's just air table everything types and it's just air table everything it has everything and if I then do Aura it has everything and if I then do Aura it has everything and if I then do Aura here here we go I just can create a new here here we go I just can create a new here here we go I just can create a new credential where I enter my token so if credential where I enter my token so if credential where I enter my token so if I go and I uh do pass find

I go and I uh do pass find I go and I uh do pass find copy Ura token then copy Ura token then copy Ura token then it's pass show it's pass show it's pass show C C C token it's done is asking me for my UB token it's done is asking me for my UB token it's done is asking me for my UB key and then I can paste it in and now I key and then I can paste it in and now I key and then I can paste it in and now I have my aura token and then what's great have my aura token and then what's great have my aura token and then what's great is when I when I save it actually tests is when I when I save it actually tests is when I when I save it actually tests it immediately I mean this is re quite it immediately I mean this is re quite it immediately I mean this is re quite polished this software I'm I'm really polished this software I'm I'm really polished this software I'm I'm really happy with it so far so then we have happy with it so far so then we have happy with it so far so then we have this and then for the query parameters this and then for the query parameters this and then for the query parameters um I was um I was um I was using start date and end date as you see using start date and end date as you see using start date and end date as you see here so we are going to have a start here so we are going to have a start here so we are going to have a start date and the value of that is going to date and the value of that is going to date and the value of that is going to be 20241 let's take the 21st of 20241 let's take the 21st of November and then we're going to have November and then we're going to have November and then we're going to have end end end date and I'm going to let's make that date and I'm going to let's make that date and I'm going to let's make that four four four days okay and if I then test this boom days okay and if I then test this boom days okay and if I then test this boom here we go here I have here we go here I have here we go here I have the the Json object containing the data the the Json object containing the data the the Json object containing the data of that day so here it says the day and of that day so here it says the day and of that day so here it says the day and then it's this is the the date and then then it's this is the the date and then then it's this is the the date and then it has my my heart rate it has my sleep it has my my heart rate it has my sleep it has my my heart rate it has my sleep score everything in here so this is score everything in here so this is score everything in here so this is really good I'm I already have the data really good I'm I already have the data really good I'm I already have the data so what I would then eventually do is I so what I would then eventually do is I so what I would then eventually do is I would have a schedule that just runs

would have a schedule that just runs would have a schedule that just runs this every hour or so and then pull in this every hour or so and then pull in this every hour or so and then pull in the data and then um have it run that the data and then um have it run that the data and then um have it run that way so now the next step is to um what way so now the next step is to um what way so now the next step is to um what you need to do is I think it's to split you need to do is I think it's to split you need to do is I think it's to split split split out because you want to split split out because you want to split split out because you want to split that field you see you see it split that field you see you see it split that field you see you see it already here so I just take that in here already here so I just take that in here already here so I just take that in here data and then data and then data and then test and then we get this so then it test and then we get this so then it test and then we get this so then it just splits that up just splits that up just splits that up nicely nicely nicely and now it's just one big list basically and now it's just one big list basically and now it's just one big list basically that's that's the output of this that's that's the output of this that's that's the output of this step and then what I'm going to do is step and then what I'm going to do is step and then what I'm going to do is I'm going to create a step for post I'm going to create a step for post I'm going to create a step for post but what I can postgress so what we do is we do insert postgress so what we do is we do insert or update rows in a or update rows in a or update rows in a table a so-called upsert table a so-called upsert table a so-called upsert operation and what's cool is that you operation and what's cool is that you operation and what's cool is that you can also do execute query so well let's can also do execute query so well let's can also do execute query so well let's just have this one here for now but what just have this one here for now but what just have this one here for now but what I can just do is I can create another I can just do is I can create another I can just do is I can create another one uh let me see uh let me see postgress here then I'm going to do postgress here then I'm going to do postgress here then I'm going to do execute SQL execute SQL execute SQL query and create a query and create a query and create a credential so the host since this is

credential so the host since this is credential so the host since this is running on a separate container but on running on a separate container but on running on a separate container but on the same Docker Network then the host is the same Docker Network then the host is the same Docker Network then the host is going to be as going to be as going to be as follows here is the docker compos at follows here is the docker compos at follows here is the docker compos at yaml and here we see that it's as as um where is um where is um where is it postgress database Port he postgress it postgress database Port he postgress it postgress database Port he postgress DB DB DB host is postgress over here so it's a host is postgress over here so it's a host is postgress over here so it's a bit weird that these environment bit weird that these environment bit weird that these environment variables are set but it's not um it's variables are set but it's not um it's variables are set but it's not um it's not reading that in but the host is not reading that in but the host is not reading that in but the host is postgress and that corresponds to this postgress and that corresponds to this postgress and that corresponds to this service here so I was messing around service here so I was messing around service here so I was messing around with bit with this earlier but if I with bit with this earlier but if I with bit with this earlier but if I write postgress here and I believe I write postgress here and I believe I write postgress here and I believe I called the database called the database called the database postgress in the N file if I now save postgress in the N file if I now save postgress in the N file if I now save this yes now it's tested so it this yes now it's tested so it this yes now it's tested so it works and then what I can do is for works and then what I can do is for works and then what I can do is for example do select from and is it public from and is it public is that is that is that a wait so well let's just do a wait so well let's just do a wait so well let's just do an SQL query right in the database an SQL query right in the database an SQL query right in the database container so Docker PS then I can do container so Docker PS then I can do container so Docker PS then I can do Docker EXC

Docker EXC Docker EXC it um postest one bash so now I'm in the um postest one bash so now I'm in the container and if I then switch to the container and if I then switch to the container and if I then switch to the postgress user and then do postgress user and then do postgress user and then do psql now I'm uh now I'm just in psql now I'm uh now I'm just in psql now I'm uh now I'm just in postgressql and then if I postgressql and then if I postgressql and then if I do uh DT now I see all of the tables do uh DT now I see all of the tables do uh DT now I see all of the tables that are in the current that are in the current that are in the current database so what I can do is Select star database so what I can do is Select star database so what I can do is Select star from from from Project roll the bflow has issues and can oh roll the bflow has issues and can oh maybe it's because that other one is yes maybe it's because that other one is yes maybe it's because that other one is yes okay I have to decouple this from this okay I have to decouple this from this okay I have to decouple this from this one and now now it works okay so here we one and now now it works okay so here we one and now now it works okay so here we see if I test it now I get the contents see if I test it now I get the contents see if I test it now I get the contents of the from of the from of the from Roll Roll Roll so what was going wrong was that it was so what was going wrong was that it was so what was going wrong was that it was connected to this one and this one was connected to this one and this one was connected to this one and this one was still not working and then this one still not working and then this one still not working and then this one couldn't execute so that's how it works

couldn't execute so that's how it works couldn't execute so that's how it works but what I wanted to show is that I can but what I wanted to show is that I can but what I wanted to show is that I can actually just do Post queries from the actually just do Post queries from the actually just do Post queries from the browser here into the database that I browser here into the database that I browser here into the database that I have I don't have to mess about with have I don't have to mess about with have I don't have to mess about with being in the container although I can being in the container although I can being in the container although I can just do that but here is the here are just do that but here is the here are just do that but here is the here are all the columns that I want in my all the columns that I want in my all the columns that I want in my database so I just have to set this up database so I just have to set this up database so I just have to set this up so I can I can just show you how to do so I can I can just show you how to do so I can I can just show you how to do it from the browser then here if I now it from the browser then here if I now it from the browser then here if I now test this then the success is test this then the success is test this then the success is true and then if I if I in here do DT now I if I in here do DT now now we see that I get now we see that I get now we see that I get a yeah I have a table called Sleep data a yeah I have a table called Sleep data a yeah I have a table called Sleep data now and that does not have anything in now and that does not have anything in now and that does not have anything in it it it yet or maybe I can do select start from yet or maybe I can do select start from yet or maybe I can do select start from sleep data yes so here we see that it sleep data yes so here we see that it sleep data yes so here we see that it has the columns has the columns has the columns so the next step will then be to put the so the next step will then be to put the so the next step will then be to put the data that we we just pulled in there so data that we we just pulled in there so data that we we just pulled in there so I again I have this I again I have this I again I have this upsert and I'm going to use this upsert and I'm going to use this upsert and I'm going to use this postgress postgress postgress account and account and account and then from lists it does it show the then from lists it does it show the then from lists it does it show the sleep here doesn't seem like

sleep here doesn't seem like sleep here doesn't seem like it's refreshes that no so then I'll just do by that no so then I'll just do by name name name sleep sleep sleep data and then it's recognizing and then data and then it's recognizing and then data and then it's recognizing and then here I can either match them here I can either match them here I can either match them manually or I can just so here I have manually or I can just so here I have manually or I can just so here I have each of these each of these each of these columns and I can just um map this to columns and I can just um map this to columns and I can just um map this to the the the fields or I can just do fields or I can just do fields or I can just do map map map automatically and if I test this boom automatically and if I test this boom automatically and if I test this boom now I have now I have now I have this and when I now do a select this and when I now do a select this and when I now do a select star now I see that I will have uh a lot star now I see that I will have uh a lot star now I see that I will have uh a lot of data in here so let's see let's of data in here so let's see let's of data in here so let's see let's do count count deep sleep deep sleep deep sleep duration from sleep duration from sleep duration from sleep data yeah so I have three rows of Deep

data yeah so I have three rows of Deep data yeah so I have three rows of Deep Sleep duration from sleep data for Sleep duration from sleep data for Sleep duration from sleep data for example so I have I have this data in my example so I have I have this data in my example so I have I have this data in my in my postgress database now and it's in my postgress database now and it's in my postgress database now and it's super easy it's with this map super easy it's with this map super easy it's with this map automatically I mean this is really nice automatically I mean this is really nice automatically I mean this is really nice I mean I I mean I I mean I do I I mean I like doing it from from do I I mean I like doing it from from do I I mean I like doing it from from code and getting this up and running uh code and getting this up and running uh code and getting this up and running uh with a separate Docker image because with a separate Docker image because with a separate Docker image because then I am in full then I am in full then I am in full control so I'm not sure if I'm going to control so I'm not sure if I'm going to control so I'm not sure if I'm going to do this but I am sure of what am sure of do this but I am sure of what am sure of do this but I am sure of what am sure of is that I am going to deploy n8n to my is that I am going to deploy n8n to my is that I am going to deploy n8n to my cluster because I can see how in how I cluster because I can see how in how I cluster because I can see how in how I can do some pretty interesting things can do some pretty interesting things can do some pretty interesting things here I mean there is like so many here I mean there is like so many here I mean there is like so many triggers there's even a AI related stuff triggers there's even a AI related stuff triggers there's even a AI related stuff in here um what does it say any AI in here um what does it say any AI in here um what does it say any AI agents open AI llm chains so I think n8n agents open AI llm chains so I think n8n agents open AI llm chains so I think n8n might actually be a a bit of a player in might actually be a a bit of a player in might actually be a a bit of a player in the aii scene too later because you can the aii scene too later because you can the aii scene too later because you can very easily use it to stitch together very easily use it to stitch together very easily use it to stitch together several several several things things things so I so I so I am I think I'm going to do the I'm just am I think I'm going to do the I'm just am I think I'm going to do the I'm just going to do the aura project in n8n even going to do the aura project in n8n even going to do the aura project in n8n even though I like it in in having it in code though I like it in in having it in code though I like it in in having it in code because this was just super easy to get because this was just super easy to get because this was just super easy to get up and run in and I will learn a bit up and run in and I will learn a bit up and run in and I will learn a bit about n8n in the process so in the next about n8n in the process so in the next about n8n in the process so in the next video I'm going to be up um deploying video I'm going to be up um deploying video I'm going to be up um deploying this to my kubernetes home lab and then this to my kubernetes home lab and then this to my kubernetes home lab and then actually pulling in all of the data into

actually pulling in all of the data into actually pulling in all of the data into my proper database which is also running my proper database which is also running my proper database which is also running on kubernetes and being backed up to the on kubernetes and being backed up to the on kubernetes and being backed up to the cloud every day so see you in the next cloud every day so see you in the next cloud every day so see you in the next one