A Complete Guide to TypeORM Integration with SQLite3

Using TypeORM with SQLite3: A Comprehensive Guide

TypeORM is a popular ORM (Object-Relational Mapping) library for TypeScript and JavaScript that supports multiple databases, including SQLite3. This guide covers the setup and integration of TypeORM with SQLite3 for building database-driven Node.js applications. With detailed examples, you will learn to configure, connect, and manage SQLite3 databases using TypeORM.

Setting Up TypeORM with SQLite3

1. Install Required Dependencies

To get started, install the necessary packages using npm:

npm install typeorm sqlite3 reflect-metadata

Dependencies Breakdown:

• typeorm: The core TypeORM library for managing database operations.
• sqlite3: SQLite3 database driver.
• reflect-metadata: Enables TypeScript decorators required by TypeORM.

2. Configuring TypeORM

Create a ormconfig.json file to define your SQLite3 database configuration:

Code:

{
  "type": "sqlite",
  "database": "database.sqlite",
  "synchronize": true,
  "logging": true,
  "entities": ["src/entity/*.ts"],
  "migrations": ["src/migration/*.ts"],
  "subscribers": ["src/subscriber/*.ts"]
}

Explanation:

• type: Specifies the database type (sqlite).
• database: File name for the SQLite database.
• synchronize: Automatically syncs your entities with the database.
• entities:Path to entity files.

3. Define an Entity

Entities in TypeORM represent database tables. Create a User entity as follows:

Code:

// Import necessary decorators
import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

// Define the User entity
@Entity()
export class User {
  @PrimaryGeneratedColumn() // Auto-generated primary key
  id: number;

  @Column() // Name column
  name: string;

  @Column() // Email column
  email: string;

  @Column() // Age column
  age: number;
}

Explanation:

• @Entity: Marks the class as a database table.
• @PrimaryGeneratedColumn: Indicates a primary key column with auto-increment.
• @Column: Represents regular table columns.

CRUD Operations with TypeORM and SQLite3

1. Connecting to the Database

Code:

import "reflect-metadata"; // Required for TypeORM decorators
import { createConnection } from "typeorm";

async function connectDatabase() {
  try {
    const connection = await createConnection(); // Establish connection using ormconfig.json
    console.log("Database connected successfully!");
  } catch (error) {
    console.error("Error connecting to the database:", error);
  }
}

connectDatabase(); // Call the connection function

2. Create and Insert Records

Code:

import { getRepository } from "typeorm";
import { User } from "./entity/User";

async function createUser() {
  const userRepository = getRepository(User); // Access the User repository

  const user = new User(); // Create a new user instance
  user.name = "Alice";
  user.email = "[email protected]";
  user.age = 30;

  await userRepository.save(user); // Save the user to the database
  console.log("User created:", user);
}

createUser();

Explanation:

• getRepository: Provides access to repository functions like save.
• save(): Persists data to the database.

3. Query Data

Code:

async function fetchUsers() {
  const userRepository = getRepository(User);

  // Fetch all users
  const users = await userRepository.find();
  console.log("Users:", users);
}

fetchUsers();

Explanation:

• find(): Retrieves all records from the User table.

4. Update Records

Code:

async function updateUser(userId: number) {
  const userRepository = getRepository(User);

  // Find the user by ID
  const user = await userRepository.findOne(userId);
  if (user) {
    user.age = 35; // Update the user's age
    await userRepository.save(user); // Save changes
    console.log("User updated:", user);
  } else {
    console.log("User not found.");
  }
}

updateUser(1); // Update user with ID 1

5. Delete Records

Code:

async function deleteUser(userId: number) {
  const userRepository = getRepository(User);

  // Delete user by ID
  await userRepository.delete(userId);
  console.log(`User with ID ${userId} deleted.`);
}

deleteUser(1); // Delete user with ID 1

Explanation:

• delete(): Removes records based on the condition.

Benefits of Using TypeORM with SQLite3

1. Seamless Integration: TypeORM's SQLite3 support allows effortless database management.

2. Decorators: TypeScript decorators simplify entity definitions.

3. Synchronization: Automates schema updates for faster development.

4. Cross-Database Support: Easily switch databases if needed.

Complete Application Example

Code:

async function main() {
  await connectDatabase(); // Connect to the database
  await createUser(); // Create a user
  await fetchUsers(); // Query users
  await updateUser(1); // Update a user
  await deleteUser(1); // Delete a user
}

main();

Conclusion

TypeORM with SQLite3 simplifies database operations in Node.js and TypeScript applications. It offers developer-friendly features like decorators and seamless synchronization while ensuring robust database management. With TypeORM, you can efficiently manage SQLite databases for small to medium-sized applications.

Practical Guides to SQLite Snippets and Examples.