Skip to content
Postgresql on Repl.it part #2 !
Profile icon
Kudos

OK here goes part 2

So we ended on our last part of the tutorial (https://repl.it/talk/learn/Postgresql-on-Replit-part-1/54673) in the middle of making a get request.

app.get("/", function(req,res) { var client = new Client({ connectionString : process.env.POSTGRES, ssl: { rejectUnauthorized: false } }) client.connect() .then(() => console.log("connected")) .catch(e => console.log) .finally(() => {client.end()}) })

Let's finish that.
Just format the .finally() properly like this because we're going to be adding multiple lines of code, and delete the client.end(), because we need to only end it

.finally(() => { })

Now, time to make the sql statement (and after that finally get the frontend working lol)
Insert inside the finally() callback function this code:

let sql = "SELECT * FROM sometable" client.query(sql, (err,result) => { if (err) { throw err } else { console.log("Queried successfully") } client.end(); });

Ok I know there's nothing in "sometable", but it will be added later.

The sql code is the query. SELECT * FROM sometable just selects everything inside "sometable"
client.query(sql) is what actually queries it. The callback function is to get the data (and the error if it exists) inside the callback. If there's an error the conditional throws the error, otherwise the query was successful. Whether or not there was an error, the connection ends because we don't need it anymore. Now, onto the next part. ## Getting the data and using it.
Now, inside the else statement inside the query statement inside the get request (

else { console.log("Queried successfully") }

)
We need to send an html file to the user, that went through the ejs engine. First for the ejs files create a new folder in the directory called "views". In it put a file called "index.ejs". For the sake of simplicity, I'll show you something to copy and paste and tell you how it works. I'm getting too tired to continue this last stretch of the tutorial thoroughly. Here's for index.ejs:

<html> <head> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js" integrity="sha512-bLT0Qm9VnAYZDflyKcBaQ2gg0hSYNQrJ8RilYldYQ1FxQYoCLtUjuuRuZo+fjqhx/qtq/1itJ0C2ejDxltZVFg==" crossorigin="anonymous"></script> <style> .spacer { height:1vh; } </style> <title>Postgresql Tutorial</title> <link rel="stylesheet" href="https://unpkg.com/[email protected]"> <link rel="stylesheet" type="text/css" href="https://unpkg.com/notie/dist/notie.min.css"> <script src="https://unpkg.com/notie"></script> </head> <body> <h1>Current users:</h1> <div class = "card" id = "container"> <% users.map(function(user){ %> <%- `<div class = "card">` %> <%=user.name%> <%-`</div>` %> <% }) %> </div> <h1>Just enter your name and it will be saved to Postgres</h1> <input id = "name" placeholder = "Enter name"/> <div class = "spacer"></div> <a id = "sub" class = "button primary outline">Submit</a> <script> $("#sub").on("click", function() { var name = $("#name").val() if (name) { $.post("/", {"name":name}, function(data) { if (data == "success") { notie.alert({ type: 1, text: 'Success! Reload to see yourself in the list of current users!'}) } }) } }) </script> </body> </html>

Let's explain. If you see the <% %> and <%= %> and <%- %> tags, those are ejs tags. They're not an html thing, which is why we can't send this directly to the frontend. We have to process it and turn it into renderable html. inside the <%= %> you will see user.name. Self explanatory for the most part. It gets user from the paramater passed in the callback, "user", which was inside users.map(). We're getting "user" from data sent to the ejs templating engine alongside the .ejs file. Guess what that data is? The sql result. We'll do that stuff later, I need to keep on explaining. There's just a big div where all of the users are kept. Then there is an input and a button, where you can insert users. There's a script tag that handles the button click and sends an AJAX request to the server, which will receive it and accordingly run a more complex version of this statement: INSERT INTO sometable(name) VALUES(''). If that's successful and their column was inserted, then it will ask the user to reload, then they will see their name because the SELECT * FROM sometable query got that user along with the others. Let's continue with the code.

Continuing with the code

Remember this?

else { console.log("Queried successfully") }

Now it's time to add on to that. Inside that put

res.render('index', {"users" : result.rows})

It looks for a "views" directory (standard), and inside, it looks for index.ejs (index was the name we passed to look for). Along with that it sends some json data, "users" (Remember, from index.ejs?). It passes as the value result.rows, which is the data from postgresql! (except there's no data in it yet).
Put that in and you should have the webserver started!
It's time to go to the stage where we're inserting the users.... Next time!

Stay tuned for Postgresql part 3!

You are viewing a single comment. View All
Profile icon
CodeLongAndPros

Ok so why could you not cat both?