Postgresql on Repl.it part #1 !
Hello, welcome to my tutorial on Postgresql
It will guide you through all of the setup and half of the actual code.
At the end of this you will be mostly done through creating a simple input/output webserver with postgresql, like this one
Here's how you create a postgresql database!
Go to https://elephantsql.com
Scroll down to the big green button
Now click on "Tiny Turtle"
At the bottom "sign up"
Sign in with whatever you want. I used Google. If you use email you may need email verification.
Ignore the warnings about billing, they're just talking about paid plans.
Name it whatever you want.
Add any tag you want.
Now press the "select a region"
Go straight down to the "review" button and press that big boy, unless you want to change the database provider to something else.
Scroll down and press "create instance"
Great! Setup is done.
Time to code.
Create a new nodejs repl.
If you code in other languages either figure it out by yourself or let me know to create one for your preferred language.
Let's create a basic input/output project with Postgres.
Here's the idea:
Get all users and create a user on demand
If you want to also allow updating users and deleting users , you can add those by using different sql commands.
Let's get started with modules that we'll need.
We're using ejs, pg (postgres), express, and body parser.
Add those to your project.
To make sure they're installed in node modules, CTRL-SHIFT-P, type in shell in the little tab that shows, and press enter. At the bottom right there should be a terminal
Time to make the webserver!
But first, we need to create a table to store our data in.
Copy and paste this at the end of your repl:
I'll tell you how that works in a sec. Don't run it yet!
First though, we need to create an .env file to store our precious (but not so much since it's free) postgres url in. So just create one, and in it put:
POSTGRES=<YOUR POSTGRESQL URL HERE>
Of course, with your postgresql url instead of the statement in elbow brackets.
Let's go back to index.js, and let me tell you how the code I told you to copy and paste works.
Tells the code where this postgres database is, since it's a URL (Uniform Resource locator, get it?).
It also configures settings to use for the database.
We set this
Now we need to connect it whenever you want to perform an sql query and disconnect it right after it's done. .then() tells us if it was successfully connected. .catch() catches it. .finally() comes after all of that, and is where we perform our sql. Inside the callback, when the query is already done, we close the connection since we don't need it anymore. If we keep the connection open for too long it will close itself, and cause an error.
CREATE TABLE sometable
creates a table called sometable (self explanatory).
What's inside the parentheses is the schema, to specify the structure of the table. In it we specified "id SERIAL primary KEY", which is just mysql AUTO_INCREMENT into postgres. It creates a new id that increments for every table row so that there will be no exact copies, which there cannot. "name varchar(255) NOT NULL" creates a name column with a max of 255 characters, and it cannot be null. Run it, and if there are no errors in the console, then delete the code, because we've already created the table. Now we can finally start making a transition into the frontend (though that will be saved for part 2)
Starting to create the webserver
Create this app.get statement:
This is just a starting base. It connects the postgres database on a get request and immediately ends it. Now, inside the .finally, we need to create a read query (SELECT * FROM sometable), and on it's success we can finally render index.ejs and send it as html to the client.
We'll do that....
In part 2!
BTW below is the final product. If you want to view it do it in a new tab
Wow! This is nice! I really want to use it! Is there any sign up-free options you can think of?