DEV Community

FredAbod
FredAbod

Posted on

A Step-by-Step Guide to Using ElephantSQL with Node.js and Express

Introduction

ElephantSQL is a managed PostgreSQL database service that simplifies database management for developers. In this guide, we'll walk through the process of setting up an ElephantSQL database, creating tables, and performing CRUD operations using a Node.js and Express application.
Let's Dive into It

Dive


Prerequisites:

  • Node.js and npm installed on your system
  • Basic knowledge of Node.js and Express
  • An ElephantSQL account > (Sign up at https://www.elephantsql.com/)

Step 1: Setting up your ElephantSQL Database

  1. Sign in to your ElephantSQL account.
  2. Once logged in, click the "Create new instance" button to create a new PostgreSQL database instance.
  3. Choose a plan that suits your needs (e.g., Free Tiny Turtle or any other plan).
  4. Select a region and a name for your database instance.
  5. Click "Create ElephantSQL" to create your database instance.
  6. After your database instance is created, you'll receive a connection string. Copy this string as you'll need it to connect to your database.

Step 2: Setting up Your Node.js and Express Application

  1. Create a new directory for your project and navigate to it in your terminal.
  2. Initialize a new Node.js project by running the following command:
npm init -y
Enter fullscreen mode Exit fullscreen mode
  1. Install the required packages: Express, pg (PostgreSQL client), body-parser, morgan (for logging).
npm install express pg body-parser morgan
Enter fullscreen mode Exit fullscreen mode
  1. Create an index.js file for your application. In your index.js file, set up your Express application with the following code:
const express = require('express');
const app = express();
const { Pool } = require('pg');
const bodyParser = require('body-parser');
const morgan = require('morgan');

// Configure morgan to log requests
app.use(morgan('dev'));

// Parse JSON request bodies
app.use(bodyParser.json());

// Create a PostgreSQL connection pool
const pool = new Pool({
  connectionString: 'YOUR_ELEPHANTSQL_CONNECTION_STRING',
});

// Test the database connection
pool.connect((err, client, done) => {
  if (err) {
    console.error('Error connecting to the database', err);
  } else {
    console.log('Connected to the database');
  }
});

// Define your routes and CRUD operations here

// Start your Express server
const port = process.env.PORT || 3000;
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});
Enter fullscreen mode Exit fullscreen mode

Replace 'YOUR_ELEPHANTSQL_CONNECTION_STRING' with the actual connection string you copied from your ElephantSQL instance.
You can add an home page to test your express app:

app.get('/', (req,res) => {
    res.send("HOME PAGE !!!")
})
Enter fullscreen mode Exit fullscreen mode

Step 4: Creating Tables and CRUD Operations

Now, let's create tables and implement CRUD operations (Create, Read, Update, Delete) for a "users" table.

Create a seed.js file to set up the table and seed some initial data:

// Import required packages and configure the pool (similar to index.js)
const pool = new Pool({
  connectionString: 'YOUR_ELEPHANTSQL_CONNECTION_STRING',
});

// Create the users table if it doesn't exist
pool.query(`
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
  )
`, (err, result) => {
  if (err) {
    console.error('Error creating the users table', err);
  } else {
    console.log('Users table created successfully');
  }
});

// Seed the table with initial data
pool.query(`
  INSERT INTO users (username, email)
  VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com')
`, (err, result) => {
  if (err) {
    console.error('Error seeding the users table', err);
  } else {
    console.log('Data seeded successfully');
  }
});
Enter fullscreen mode Exit fullscreen mode

In your index.js file, create routes and implement CRUD operations for the "users" table using Express routes. Here's an example:

app.post('/users', (req, res) => {
    const { username, email } = req.body;

    if (!username || !email) {
      return res.status(400).json({ error: 'Both username and email are required' });
    }

    pool.query('INSERT INTO users (username, email) VALUES ($1, $2)', [username, email], (err, result) => {
      if (err) {
        console.error('Error inserting user into the database', err);
        res.status(500).json({ error: 'Internal server error' });
      } else {
        res.status(201).json({ message: 'User created successfully' });
      }
    });
  });


  app.get('/users', (req, res) => {
    // Use COUNT() to get the total number of users
    pool.query('SELECT COUNT(*) as total_users FROM users; SELECT * FROM users;', (err, result) => {
      if (err) {
        console.error('Error executing SQL query', err);
        res.status(500).json({ error: 'Internal server error' });
      } else {
        // Extract the count from the first query result
        const totalUsers = result[0].rows[0].total_users;

        // Extract user data from the second query result
        const users = result[1].rows;

        // Create a response object with both the count and user data
        const response = {
          total_users: totalUsers,
          users: users,
        };

        res.json(response);
      }
    });
  });

  app.put('/users/:id', (req, res) => {
    const userId = req.params.id;
    const { username, email } = req.body;

    if (!username || !email) {
      return res.status(400).json({ error: 'Both username and email are required' });
    }

    pool.query('UPDATE users SET username = $1, email = $2 WHERE id = $3', [username, email, userId], (err, result) => {
      if (err) {
        console.error('Error updating user in the database', err);
        res.status(500).json({ error: 'Internal server error' });
      } else {
        res.json({ message: 'User updated successfully' });
      }
    });
  });


  app.delete('/users/:id', (req, res) => {
    const userId = req.params.id;

    pool.query('DELETE FROM users WHERE id = $1', [userId], (err, result) => {
      if (err) {
        console.error('Error deleting user from the database', err);
        res.status(500).json({ error: 'Internal server error' });
      } else {
        res.json({ message: 'User deleted successfully' });
      }
    });
  });
Enter fullscreen mode Exit fullscreen mode

Step 5: Testing Your Application

You can make your packeage.json file like this:

{
  "name": "src",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon index.js",
    "seed": "node seed.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.18.2",
    "morgan": "^1.10.0",
    "pg": "^8.11.3"
  }
}
Enter fullscreen mode Exit fullscreen mode
  1. To start your app do

npm run dev

  1. To seed into the database and create a table

npm run seed

  1. Use tools like Postman or curl to test your CRUD operations.

Conclusion

In this step-by-step guide, we've explored how to set up an ElephantSQL database, configure a Node.js and Express application, and implement CRUD operations with PostgreSQL. ElephantSQL simplifies database management, making it easier for developers to build applications that interact with PostgreSQL databases. Happy coding! 🐘📦

Top comments (1)

Collapse
 
dremmaokoh profile image
Dr Emmanuel Okoh

Great work. I will try it out.