2024-01-26
4314
#node#postgresql
Tania Rascia
318
Jan 26, 2024 ⋅ 15 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:

Nx Adoption Guide: Overview, Examples, And Alternatives

Nx adoption guide: Overview, examples, and alternatives

Let’s explore Nx features, use cases, alternatives, and more to help you assess whether it’s the right tool for your needs.

Andrew Evans
Mar 28, 2024 ⋅ 9 min read
Understanding Security In React Native Applications

Understanding security in React Native applications

Explore the various security threats facing React Native mobile applications and how to mitigate them.

Wisdom Ekpotu
Mar 27, 2024 ⋅ 10 min read
Warp Adoption Guide: Overview, Examples, And Alternatives

warp adoption guide: Overview, examples, and alternatives

The warp web framework for Rust offers many enticing features. Let’s see when and why you should consider using warp in your projects.

Ukeje Goodness
Mar 26, 2024 ⋅ 8 min read
Integrating Next Js And Signalr For Enhanced Real Time Web App Capabilities

Integrating Next.js and SignalR to build real-time web apps

In this tutorial, you’ll learn how to integrate Next.js and SignalR to build an enhanced real-time web application.

Clara Ekekenta
Mar 25, 2024 ⋅ 8 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