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:

Implementing Vector Search With Open Ai, Next Js, And Supabase

Implementing vector search with OpenAI, Next.js, and Supabase

Let’s build a Next.js app that implements vector search using Supabase and OpenAI to offer better search experiences for users.

Peter Ekene Eze
Feb 27, 2024 ⋅ 11 min read
Comparing React Native BLE libraries

Comparing React Native BLE libraries

Discover the most popular libraries for enabling Bluetooth Low Energy (BLE) in React Native apps, allowing them to interact with Bluetooth hardware on iOS and Android platforms.

Fimber Elemuwa
Feb 26, 2024 ⋅ 4 min read
Using CRDTs To Build Collaborative Rust Web Applications

Using CRDTs to build collaborative Rust web applications

CRDTs, or conflict-free replicated data types, is a concept that underlies applications facing the issue of data replication across a […]

Mario Zupan
Feb 23, 2024 ⋅ 15 min read
Guide to Using TensorFlow in Rust

Guide to using TensorFlow in Rust

We explore the fusion of TensorFlow and Rust, delving into how we can integrate these two technologies to build and train a neural network.

Rosario De Chiara
Feb 22, 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