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:

Vue logo over a brown background.

A guide to two-way binding in Vue

Learn how to implement one-way and two-way data binding in Vue.js, using v-model and advanced techniques like defineModel for better apps.

David Omotayo
Nov 22, 2024 ⋅ 10 min read
TypeScript logo over a pink and white background.

Drizzle vs. Prisma: Which ORM is best for your project?

Compare Prisma and Drizzle ORMs to learn their differences, strengths, and weaknesses for data access and migrations.

Temitope Oyedele
Nov 21, 2024 ⋅ 10 min read
Practical Implementation Of The Rule Of Least Power For Developers

Practical implementation of the Rule of Least Power for developers

It’s easy for devs to default to JavaScript to fix every problem. Let’s use the RoLP to find simpler alternatives with HTML and CSS.

Timonwa Akintokun
Nov 21, 2024 ⋅ 8 min read
Rust logo over black marble background.

Handling memory leaks in Rust

Learn how to manage memory leaks in Rust, avoid unsafe behavior, and use tools like weak references to ensure efficient programs.

Ukeje Goodness
Nov 20, 2024 ⋅ 4 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