2022-05-23
3470
#node#postgresql
Tania Rascia
318
May 23, 2022 ⋅ 12 min read

CRUD REST API with Node.js, Express, and PostgreSQL

Tania Rascia Software developer, writer, maker of things. Find me online at tania.dev.

Recent posts:

Mastering Modulo Operator JavaScript

Mastering the modulo operator in JavaScript: A complete guide

We investigate how the modulo operator works in JavaScript and explore several real-life applications, like cyclic patterns and animations.

Timonwa Akintokun
Dec 8, 2023 ⋅ 7 min read
Ken Pickering Leader Spotlight

Leader Spotlight: Building with grit with Ken Pickering

Ken Pickering discusses the challenges associated with uncertainty and shares insights on scaling data structures and engineering teams.

Jessica Srinivas
Dec 8, 2023 ⋅ 7 min read
Understanding Solid Js Props A Complete Guide From Beginner To Advanced

Understanding SolidJS props: A complete guide

Let’s see how Solid props work to promote component reusability, exploring basic to advanced concepts for a complete understanding.

Temitope Oyedele
Dec 7, 2023 ⋅ 11 min read
Eleventy Vs. Next.js Static-Site Generation

Eleventy vs. Next.js for static site generation

We evaluate Eleventy and Next.js and compare both static site generators in terms of performance, developer experience, scalability, and ecosystem.

Nelson Michael
Dec 7, 2023 ⋅ 11 min read
View all posts

82 Replies to "CRUD REST API with Node.js, Express, and PostgreSQL"

  1. I needed to use

    psql -d postgres -h localhost -U me

    to log in, otherwise I got

    psql: error: could not connect to server: FATAL: Peer authentication failed for user “me”

  2. I’m confused. Everything works except why would there be a json response on localhost:3000/users ? I don’t see anything there. Am I supposed have a users.html?

  3. Hi Tania,
    Thanks very much for your very clear and concise example. I followed it closely.
    There was only 1 problem I ran into, which is when creating a user, I could not get back the inserted ID.

    I looked this up, and for PosrgreSQL, there is a different way in which the id is returned after an insert, as follows:

    const createUser = (request, response) => {
    const { username, email } = request.body;

    pool.query(
    “INSERT INTO users (username, email) VALUES ($1,$2) RETURNING id”,
    [username, email],
    (error, results) => {
    if (error) {
    throw error;
    }
    response.status(201).send(`User added with ID:${results.rows[0].id} `);
    }
    );
    };

    The key is in getting the SQL statement correctly, with the “RETURNING id” bit at the end.

    Hopefully this will help others if they find the same problem.

    Best,
    Luis

  4. Nice at first I thought it was the apostrophe formatting, I actually had the RETURNING id part but couldnt get the results.rows piece – thank you!

  5. I seem to be getting this error when I run node index.js at the end: error: password authentication failed for user “testuser”

    Is there a fix for this?

  6. I’ve copied the 2 files completely and when I try and localhost:3000/users it just spins forever. Any way to trace the error?

  7. I’ve failed to INSERT entries via $request.body, I had to use $request.query and I’m stunned nobody else had this issue
    The results.rows[0].id thing isn’t working for me either 🙄

    1. Thank you! I had the issue with $request.body as well and changing it to $request.query fixed it.
      Not sure about results.rows[0] thing though, that one works on my machine.

  8. Hi! I find the text between the headlines “Installation” and “PostgreSQL command prompt” to be misleading. I’m using Windows and it is unlear to me which of those passages are releveant to my OS and whether I have to run any brew-based or commands or similar ones in npm after downloading the Windows installer (and running it, obviously, though not mentioned) and passin on to the PostgreSQL command prompt section. Clarifications would be appreciated.

  9. Tania, I am a very accomplished React & C# & Python software engineer but am working on my own project now. I was all set to have a Python back-end, like is at my work but then learned about Express.js. Thank you SO MUCH for your excellent, most comprehensive article. It served as the template for building the back-end of my ever evolving application.

    By the way, I’ve built both front-end and back-end with TypeScript so upgraded your Javascript code to TypeScript. Feel free to contact me and I will gladly share my code with you in case you want to update this article or follow-up with another.

  10. For those who have a problem with createUser, change code into:
    response.status(201).send(`User added with ID: ${results.rows[0][“id”]}`)

  11. I can able to create record in users table but both name and email has null values. request.body tells undefined. Any help appreciated.

    1. Maybe is late but this could work for others.

      make use of bodyParser should solve this problem

      app.use(bodyParser.json())
      app.use(
      bodyParser.urlencoded({
      extended: true,
      })
      )

  12. On MacOS – Homebrew required a version for postgresql.

    “`
    brew install postgresql
    ==> Downloading https://formulae.brew.sh/api/formula.jws.json
    ######################################################################## 100.0%
    ==> Downloading https://formulae.brew.sh/api/cask.jws.json
    ######################################################################## 100.0%
    Warning: No available formula with the name “postgresql”. Did you mean postgresql@13, postgresql@12, postgresql@11, postgresql@15, postgresql@10, postgresql@14, [email protected], [email protected], postgrest or qt-postgresql?
    postgresql breaks existing databases on upgrade without human intervention.

    See a more specific version to install with:
    brew formulae | grep postgresql@
    “`

    This worked for me:

    “`
    brew install postgresql@15
    “`

Leave a Reply