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
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
- Sign in to your ElephantSQL account.
- Once logged in, click the "Create new instance" button to create a new PostgreSQL database instance.
- Choose a plan that suits your needs (e.g., Free Tiny Turtle or any other plan).
- Select a region and a name for your database instance.
- Click "Create ElephantSQL" to create your database instance.
- 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
- Create a new directory for your project and navigate to it in your terminal.
- Initialize a new Node.js project by running the following command:
npm init -y
- Install the required packages: Express, pg (PostgreSQL client), body-parser, morgan (for logging).
npm install express pg body-parser morgan
- 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}`);
});
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 !!!")
})
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');
}
});
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' });
}
});
});
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"
}
}
- To start your app do
npm run dev
- To seed into the database and create a table
npm run seed
- 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)
Great work. I will try it out.