Postgresql on part #1 !
Kudos (104)

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
Scroll down to the big green button

Press it.
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"

Press on your instance

Press on the eye for the url

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

In it put in "npm install ejs && npm ci" to install everything that is needed. Should take a couple of seconds. 12.47 for me.

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:

var client = new Client({
  connectionString : process.env.POSTGRES,
  ssl: {
    rejectUnauthorized: false

.then(() => console.log("connected"))
.catch(e => console.log)
.finally(() => {
    client.query('CREATE TABLE sometable (id SERIAL primary KEY, name varchar(255) NOT NULL)', (err) => {
    if (err) {
      throw err
    } else {
      console.log("BRUH YES")

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:
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

to false.
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:

app.get("/", function(req,res) {
  var client = new Client({
  connectionString : process.env.POSTGRES,
  ssl: {
    rejectUnauthorized: false

.then(() => console.log("connected"))
.catch(e => console.log)
.finally(() => {client.end()})

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

You are viewing a single comment. View All
CodeLongAndPros (1564)

Please, don’t cycle squeeze (split tutorials into multiple parts)