TypeScript Database Integration
In this chapter, we'll delve into the exciting realm of integrating TypeScript with various database technologies. TypeScript, with its type safety and robust features, empowers you to build secure and maintainable applications that interact seamlessly with databases. We'll explore the fundamental concepts, popular approaches, best practices, and advanced techniques to equip you for effective database interactions in your TypeScript projects.
Understanding Database Interactions
Databases: A database is a structured storage system for organizing and managing data. Popular database types include relational databases (MySQL, PostgreSQL), NoSQL databases (MongoDB, Cassandra), and document databases (CouchDB).
CRUD Operations: The core of database interactions revolves around CRUD (Create, Read, Update, Delete) operations. These operations manipulate data within the database.
Drivers: To communicate with a database from your TypeScript application, you’ll utilize database drivers. These drivers provide an interface for sending queries and receiving results specific to the chosen database technology.
Overview of Databases
SQL vs NoSQL
- SQL Databases: Structured Query Language (SQL) databases are relational databases that store data in tables with predefined schemas. Examples include MySQL, PostgreSQL, and SQLite.
- NoSQL Databases: NoSQL databases are non-relational databases designed for flexibility and scalability. They store data in various formats such as key-value pairs, documents, graphs, or wide-column stores. Examples include MongoDB, CouchDB, and Cassandra.
Popular Databases for TypeScript
- MySQL: A widely-used open-source relational database.
- PostgreSQL: A powerful, open-source relational database with advanced features.
- SQLite: A lightweight, file-based relational database.
- MongoDB: A popular document-oriented NoSQL database.
Setting Up the Development Environment
Installing Node.js and TypeScript
First, ensure you have Node.js and npm (Node Package Manager) installed. You can download them from the Node.js website.
To install TypeScript globally, use the following command:
npm install -g typescript
Verify the installation:
tsc --version
Installing Database Drivers and ORMs
Depending on the database and ORM you choose, you’ll need to install the relevant packages. Here are some common installations:
MySQL
npm install mysql2
PostgreSQL
npm install pg
MongoDB
npm install mongodb
TypeORM (for SQL databases)
npm install typeorm reflect-metadata
npm install mysql2 pg # Depending on the database you're using
Prisma (for SQL databases)
npm install @prisma/client
npx prisma init # Initializes Prisma in your project
SQL Databases Integration
MySQL
Connecting to MySQL
To connect to a MySQL database, you need to create a connection using the mysql2 package.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'test_db'
});
console.log('Connected to MySQL');
Performing CRUD Operations
Create
const [rows] = await connection.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
['John Doe', 'john@example.com']
);
console.log('User added:', rows);
Read
const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);
console.log('User:', rows);
Update
const [rows] = await connection.execute(
'UPDATE users SET name = ? WHERE id = ?',
['Jane Doe', 1]
);
console.log('User updated:', rows);
Delete
const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);
console.log('User:', rows);
PostgreSQL
Connecting to PostgreSQL
To connect to a PostgreSQL database, use the pg package.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'test_db'
});
console.log('Connected to MySQL');
Performing CRUD Operations
Create
const res = await client.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
['John Doe', 'john@example.com']
);
console.log('User added:', res.rows[0]);
Read
const res = await client.query('SELECT * FROM users WHERE id = $1', [1]);
console.log('User:', res.rows[0]);
Update
const res = await client.query(
'UPDATE users SET name = $1 WHERE id = $2 RETURNING *',
['Jane Doe', 1]
);
console.log('User updated:', res.rows[0]);
Delete
const res = await client.query(
'UPDATE users SET name = $1 WHERE id = $2 RETURNING *',
['Jane Doe', 1]
);
console.log('User updated:', res.rows[0]);
NoSQL Databases Integration
MongoDB
Connecting to MongoDB
To connect to a MongoDB database, use the mongodb package.
import { MongoClient } from 'mongodb';
const client = new MongoClient('mongodb://localhost:27017');
await client.connect();
console.log('Connected to MongoDB');
const db = client.db('test_db');
const usersCollection = db.collection('users');
Performing CRUD Operations
Create
const result = await usersCollection.insertOne({ name: 'John Doe', email: 'john@example.com' });
console.log('User added:', result.insertedId);
Read
await usersCollection.updateOne(
{ _id: result.insertedId },
{ $set: { name: 'Jane Doe' } }
);
const updatedUser = await usersCollection.findOne({ _id: result.insertedId });
console.log('User updated:', updatedUser);
Update
const result = await usersCollection.insertOne({ name: 'John Doe', email: 'john@example.com' });
console.log('User added:', result.insertedId);
Delete
await usersCollection.deleteOne({ _id: result.insertedId });
console.log('User deleted');
Using ORMs with TypeScript
TypeORM
Installation and Setup
Install TypeORM and the necessary database drivers:
npm install typeorm reflect-metadata
npm install mysql2 pg # Depending on the database you're using
Create a tsconfig.json file if it doesn’t exist and ensure it includes:
{
"emitDecoratorMetadata": true,
"experimentalDecorators": true
}
Defining Entities and Repositories
Define an entity:
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column()
email: string;
}
Set up a connection and perform CRUD operations:
import { createConnection } from 'typeorm';
import { User } from './entity/User';
const connection = await createConnection({
type: 'mysql',
host: 'localhost',
port: 3306,
username: 'root',
password: 'password',
database: 'test_db',
entities: [User],
synchronize: true,
});
console.log('Connected to MySQL');
const userRepository = connection.getRepository(User);
// Create
const user = new User();
user.name = 'John Doe';
user.email = 'john@example.com';
await userRepository.save(user);
console.log('User added:', user);
// Read
const savedUser = await userRepository.findOne(user.id);
console.log('User:', savedUser);
// Update
user.name = 'Jane Doe';
await userRepository.save(user);
console.log('User updated:', user);
// Delete
await userRepository.remove(user);
console.log('User deleted');
Prisma
Installation and Setup
Install Prisma CLI and client:
npm install @prisma/client
npx prisma init
This initializes a prisma directory with schema.prisma.
Defining Models
Edit prisma/schema.prisma to define your data model:
datasource db {
provider = "mysql"
url = "mysql://root:password@localhost:3306/test_db"
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
name String
email String @unique
}
Generate Prisma Client:
npx prisma generate
Performing CRUD Operations
Use the Prisma Client in your TypeScript code
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// Create
const user = await prisma.user.create({
data: { name: 'John Doe', email: 'john@example.com' },
});
console.log('User added:', user);
// Read
const savedUser = await prisma.user.findUnique({ where: { id: user.id } });
console.log('User:', savedUser);
// Update
const updatedUser = await prisma.user.update({
where: { id: user.id },
data: { name: 'Jane Doe' },
});
console.log('User updated:', updatedUser);
// Delete
await prisma.user.delete({ where: { id: user.id } });
console.log('User deleted');
Advanced Topics
Handling Migrations
TypeORM
npm install -g typeorm
typeorm migration:create -n MigrationName
typeorm migration:run
typeorm migration:revert
Prisma
npx prisma migrate dev --name init
npx prisma migrate deploy
Database Testing in TypeScript
Use tools like Jest for testing your database interactions.
Example Test with TypeORM and Jest
import { createConnection, getConnection } from 'typeorm';
import { User } from './entity/User';
beforeAll(async () => {
await createConnection(/* your connection options */);
});
afterAll(async () => {
await getConnection().close();
});
test('should create a new user', async () => {
const userRepository = getConnection().getRepository(User);
const user = new User();
user.name = 'John Doe';
user.email = 'john@example.com';
await userRepository.save(user);
const savedUser = await userRepository.findOne({ email: 'john@example.com' });
expect(savedUser).toBeDefined();
expect(savedUser.name).toBe('John Doe');
});
Database Testing in TypeScript
Use tools like Jest for testing your database interactions.
Example Test with TypeORM and Jest
Integrating databases with TypeScript involves understanding the basics of SQL and NoSQL databases, setting up your development environment, and performing CRUD operations. Advanced topics like migrations, testing, and query optimization further enhance your skills. With this comprehensive guide, you are well-equipped to handle database integration in TypeScript projects, ensuring efficient and robust applications. Happy coding !❤️