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. Can you make a tutorial of user registration using react native with the PostgreSQL and inserting the data with the button onPress function , what i mean is a restful api with node.js using react native with the PostgreSQL and just add new user into database….
    Thanks

  2. Thanks for this tutorial. I was getting an error like “Cannot GET /users” and the resolution was to stop and start node index.js – in case anyone else gets stuck on that

  3. I don’t really know why this brilliant tutorial doesn’t have many likes or comments. It’s perfect for a Node newbie looking to intercept the postgres world to build apis. Thanks very much!!

  4. This was incredible. Thank you so much. I’ve been having so many issues connecting to postgres that I thought it was going to take me forever to connect locally as well.
    Note to anybody who might have permission issues connecting with your created user, you might have to grant read access instead of just creating a database: https://dba.stackexchange.com/questions/36870/permission-denied-in-postgres

    just change myuser and paste the rest into postgres
    for read access
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
    for inserting data
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

  5. absolutely nice starting guide for me, theres a bit mistype in your create user response, just change to “results” and you good to go, anyway thanks… your starting guide helps me understand node works

  6. Thanks for the tutorial, this was really helpful getting me started on making a backend for my apps 🙂

    I had only one issue, the setup with the bodyParser middleware (`app.use()` lines) was preventing my browser from connecting to Express. The server would start via CLI fine, but I was getting “waiting for localhost” when trying to hit the server from Firefox & Chrome. Starting Express in debug mode didn’t show anything indicative.. But after removing those lines, the rest of the tutorial worked fine! I’m going to investigate what the bodyParser component is meant for because it’s likely I’m skipping something important 😅

  7. Awesome tutorial. Real help for beginners. With your step by step approach you explained everything so clearly. Thanks!

  8. Hello there !
    I got through the whole tutorial (which is really clear and great, thanks for that !), and I guess this is going to be a pretty stupid question (fairly new to Node/Express/APIs), but I’m on Windows, and the curls commands to POST and UPDATE don’t work, only the DELETE one those.
    When I try, it returns an error :
    “Cannot destructure property `name` of undefined or null;”

    In any case thanks again for this tutorial 🙂 !

  9. @ryan this means that you are trying to access name from a variable which is undefined

    request.body is undefined and you are trying to get the name data which will throw this error

  10. Thanks for this article. Helped a novice like me to start on Nodejs and PostGres. As token of thanks…was getting undefined for results.insertId for insert POST api. I replaced that by response.status(201).send(`User added with ID: ${results.rows[0].id}`) to get the last inserted id.

  11. I solved this issue by adding the bodyParser.json() middelware in the app.post as follows:

    app.post(‘/users’, bodyParser.json(), db.createUser);

  12. I like this tutorials. It was straight to the point, integrating basic concepts of node and postgresql. I wish more tutorials and/or videos where this straight forward, without trivial distractions.

  13. It is best tutorial. thank you.
    but, can you tell me how to access the four methods(GET, POST, PUT, and DELETE) in angular JS?

  14. What a life saver! I wish many will copy your style of tutorials. I went through a number of frustrating ones until I found yours.Thanks.

  15. where do you enter the mkdir node-api-postgres? I tried on my windows command prompt but when i got to npm i express pg, it says “‘npm’ is not recognized as an internal or external command, operable program or batch file”. Could you be more specific please cos I know it did not work on psql terminal so I’m not sure where to run this successfully

  16. This tutorial helps me a lot in my Web development learning. Thanks.
    I have a use case that needs a response for multiple Ids. Something like:
    ‘SELECT * FROM users WHERE id IN $1’, [ListOfID].
    The ListOfID comes from a Multiple HTML Select Options.
    Thank you very much for a suggestion.

  17. This was very helpful. One thing to mention, we’re missing the RETURNING clause for the add user query, which means we always get an empty results.rows array back. I used this query:
    INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id
    and was able to get the id using: results.rows[0].id

  18. The insert route doesn’t work anymore under Postgres 12.
    It should be something like this:

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

    The main difference being the `RETURNING id` which returns the ID of the row that was inserted, and `results.rows[0].id` to return that id.

  19. Great tutorial ! Thanks – helped me get going quickly with Postgres as well – and I too had to do what Thomas above me said to get the returning id – much appreciated!

  20. Great article, but i found some issue in queries.js file, in post request, in callback function there’s “results” in parameter and in response the function is returning “result”.

  21. Such a great tutorial, I learned lots of things from that, just one thing is that results should be result in callback function, i also needed to add ssl:true in connection string cause i got ssl require error.

  22. Folks who still have troubles with POST request and “result.insertId” / “results.rows[0].id” issue – you have to do one simple change: in the end of pool.query add “RETURNING *”, so the full query will look like this:

    ‘INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *’

    P.S. Perfect writing, thanks a lot!

  23. i have user bodyParser.json() but still when i am doing console.log(request.body) in app.post(‘/users’, bodyParser.json(), db.createUser); getting empty result

  24. Hi, has anyone experimented problems with the middleware part that checks for the presence of the parameter (request.params.id)? The error looks like it is not present so the information fetch is not happening. Greeitngs

  25. Tania, I appreciate your step by step work through. Very helpful for my development using Postgresql. Nodejs and express configuration Kind regards

  26. same with me, except i also had to add ‘RETURNING id’ in my SQL query at the end. so my final createUser function that finally works looks like this:

    “`
    const createUser = (request, response) => {
    const { name, email } = request.body

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

Leave a Reply