A Beginner's Guide to Mastering CRUD Operations with PostgreSQL and TypeScript
What is SQL Database?
SQL, or Structured Query Language, is the cornerstone for managing relational databases. It's a standardized programming language used for storing, manipulating, and retrieving data stored in a relational database management system (RDBMS). SQL databases organize data into tables that can be linked—or related—based on data common to each.
Why Choose SQL Database over NoSQL (e.g., MongoDB)?
Structured Data and Schema: SQL databases are ideal for cases where the structure of your data is known in advance and unlikely to frequently change. This enables rigorous data integrity and validation through predefined schemas.
Complex Queries: SQL databases excel at handling complex queries. Thanks to their relational nature, you can perform intricate queries to retrieve or analyze related data across multiple tables efficiently—a task that can be more challenging with NoSQL databases.
Building a Basic CRUD Application with PostgreSQL and TypeScript
Setting Up PostgreSQL Environment
There are multiple ways to get the Postgres URL.
Elephant Sql, neon.tech, aiven.io, running Postgres locally using docker. In this application, i will be using Postgres locally running on docker.
Create an empty node js project and run this commandmkdir pg-crud-appcd pg-crud-appnpm init -y
Install pg library to interact postgres with nodejsnpm install pg typescript @types/node @types/pg --save
initialize typescript, create src folder where we will write our ts files, create dist folder where converted js file will be saved.
set source directory for the ts file and dist directory for the js file, your tsconfig.json should look like this after changes.npx tsc --init
create docker-compose.yml file and pate this
version: "3,9"
services:
db:
image: postgres:latest
environment:
POSTGRES_PASSWORD: postgres
POSTGRES_DB: crud-app
ports:
- "5432:5432"
volumes:
- ./data:/var/lib/postgresql/data
Now let's start everything by running each command in the new terminal
docker-compose up to start docker
tsc -b --watch it will watch your ts files and convert them to js files in dist folder
Let's start writing code
Create an index.ts file and export the client
import { Client } from "pg";
import { DB_URL } from "./config";
export const client = new Client({
connectionString: DB_URL,
});
Creating Tables for users and Todos
import { client } from '../index';
export async function createTables() {
await client.query(`
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL
);
`);
await client.query(`
CREATE TABLE IF NOT EXISTS todos (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
title VARCHAR(255) NOT NULL,
description TEXT,
done BOOLEAN DEFAULT false
);
`);
}
export async function dropTables() {
await client.query(`DROP TABLE IF EXISTS todos;`);
await client.query(`DROP TABLE IF EXISTS users;`);
}
module.exports = { createTables, dropTables };
whenever we need to call any function we will export in index.ts and
connect to the client call the function and then end the connection
async function main() {
await client.connect();
await createTables();
await client.end();
}
main().catch(console.error);
run node dist/index.js, two tables have been created one for the user and one for todos, they are connected using the foreign key user_id in the todo table.
Implementing CRUD Operations for User
Creating Users
export async function createUser(
username: string,
password: string,
name: string
) {
try {
const insertUserQuery = `INSERT INTO USERS(username,password,name) VALUES($1,$2,$3) RETURNING id`;
const insertUserValue = [username, password, name];
const res = await client.query(insertUserQuery, insertUserValue);
console.log("User created with ID:", res.rows[0].id);
return res.rows[0].id;
} catch (error) {
console.error("Error creating user:", error);
throw error;
}
}
Creating Todos
export async function createTodo(
userId: number,
title: string,
description: string
) {
try {
const insertTodoQuery = `
INSERT INTO todos (user_id, title, description, done)
VALUES ($1, $2, $3, $4)
RETURNING id, title, description, done;
`;
const values = [userId, title, description, false];
const res = await client.query(insertTodoQuery, values);
if (res.rows.length > 0) {
const todo = res.rows[0];
return {
id: todo.id,
title: todo.title,
description: todo.description,
done: todo.done,
};
} else {
console.error("Failed to create todo.");
return null;
}
} catch (error) {
console.error("Error creating todo:", error);
throw error;
}
}
Getting User
export async function getUser(userId: number) {
try {
const getUserQuery = `SELECT username, password, name FROM users WHERE id = $1;`;
const res = await client.query(getUserQuery, [userId]);
if (res.rows.length > 0) {
const user = res.rows[0];
return {
username: user.username,
name: user.name,
};
} else {
console.log(`No user found with ID ${userId}`);
return null;
}
} catch (error) {
console.error("Error retrieving user:", error);
throw error;
}
}
Getting Todo
export async function getTodo(todoId: number) {
try {
const queryText = `
SELECT id, title, description, done
FROM todos
WHERE id = $1;
`;
const res = await client.query(queryText, [todoId]);
if (res.rows.length > 0) {
const todo = res.rows[0];
return {
todo,
};
} else {
console.log(`No todo found with ID ${todoId}`);
return null;
}
} catch (error) {
console.error("Error retrieving todo:", error);
throw error;
}
}
Updating Todo
export async function updateTodo(todoId: number) {
try {
const updateQuery = `
UPDATE todos
SET done = TRUE
WHERE id = $1
RETURNING id, title, description, done;
`;
const res = await client.query(updateQuery, [todoId]);
if (res.rows.length > 0) {
const updatedTodo = res.rows[0];
return {
id: updatedTodo.id,
title: updatedTodo.title,
description: updatedTodo.description,
done: updatedTodo.done,
};
} else {
console.log(
`No todo found with ID ${todoId}, or it is already marked as done.`
);
return null;
}
} catch (error) {
console.error("Error updating todo:", error);
throw error;
}
}
Delete User
export async function deleteUser(userId: number) {
try {
const deleteQuery = "DELETE FROM users WHERE id = $1;";
const res = await client.query(deleteQuery, [userId]);
// Check how many rows were affected
if (res.rowCount && res.rowCount > 0) {
console.log(`User with ID ${userId} deleted successfully.`);
return {
success: true,
message: `User with ID ${userId} has been successfully deleted.`,
};
} else {
console.log(`No user found with ID ${userId}.`);
return { success: false, message: `No user found with ID ${userId}.` };
}
} catch (error) {
console.error("Error deleting user:", error);
throw error;
}
}
Joins
Joins in SQL are powerful, allowing you to combine rows from two or more tables based on a related column between them. They are crucial in relational database systems for querying complex data that spans multiple tables.
Types of Joins:
INNER JOIN: Returns rows when there is at least one match in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
Implementing Joins in Our Application
Suppose we want to retrieve all todos along with the user details. We can accomplish this by using an INNER JOIN operation between the userstodos tables.
import { client } from "../index";
export async function getTodosWithUserDetails(userId: number) {
try {
const joinQuery = `
SELECT todos.id, todos.title, todos.description, todos.done, users.name, users.username
FROM todos
INNER JOIN users ON todos.user_id = users.id
WHERE users.id = $1;
`;
const res = await client.query(joinQuery, [userId]);
return res.rows;
} catch (error) {
console.error("Error getting todos with user details:", error);
throw error;
}
}
QL Injection
SQL Injection is a prevalent vulnerability in applications that interact with a database. It occurs when an attacker manipulates a query by injecting malicious SQL code through an application's input channels. If the application directly includes input values in SQL commands without proper sanitation, it can lead to unauthorized data access, deletion, or data breach.
For example, consider a query string concatenated with user input:
SELECT * FROM users WHERE username = '" + userInput + "';
If userInput is something malicious like a'; DROP TABLE users; --, the final SQL statement becomes:
SELECT * FROM users WHERE username = 'a'; DROP TABLE users; --';
This could lead to the users table being dropped if executed, showcasing a simple yet effective SQL injection attack.
How Parameterized Queries Help
When using parameterized queries, instead of directly embedding user inputs into the query string, you use placeholders (e.g., $1, $2 in PostgreSQL) and provide the input values separately. The database engine then knows to treat these inputs strictly as data, not as part of the SQL command. This approach has two main benefits:
Prevents SQL Injection: Since the input data is never part of the query compilation process, there's no way for an attacker to inject malicious SQL. This significantly enhances the application's security.
Improves Performance: Parameterized queries can improve performance through query plan reuse. The database can cache the execution plan for a parameterized query, reducing planning time for subsequent executions with different parameters.
Github Repo Link for all the above code. https://github.com/KartikayFTW/assignments-100x/tree/master/week-10/postgres-crud-app
PostgreSQL, with its rich feature set and compliance with SQL standards, provides a robust platform for building reliable and efficient applications. By mastering these operations and concepts, you can manipulate and access your data in powerful ways, unlocking the full potential of your application's data layer.