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:

Qwik Adoption Guide Overview Examples And Alternatives

Qwik adoption guide: Overview, examples, and alternatives

Qwik’s focus on performance, innovative features, and commitment to DX make it a fantastic choice for building high-performance web apps.

Fimber Elemuwa
Jun 18, 2024 ⋅ 10 min read
Exploring The Css Display Property A Deep Dive

Exploring the CSS :display property: A deep dive

Understanding the CSS :display property and its values is important for creating well-organized and attractive websites.

Ibadehin Mojeed
Jun 18, 2024 ⋅ 12 min read
7 TUI Libraries For Creating Interactive Terminal Apps

7 TUI libraries for creating interactive terminal apps

When writing applications, a good user interface is just as important as the actual app’s functionality. A good user interface […]

Yashodhan Joshi
Jun 14, 2024 ⋅ 18 min read
Expo Router Adoption Guide Overview Examples And Alternatives

Expo Router adoption guide: Overview, examples, and alternatives

Expo Router provides an excellent file-based routing solution with crucial features such as deep linking and native support.

Marie Starck
Jun 13, 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