ORM All In One

ORM All In One ORM OOP SQL Injection SQL NoSQL TypeScript ORM

ORM All In One

ORM

Object Relational Mapping

​

ORM is a technique that allows you to communicate with a database using an object oriented pattern

Advantages of ORMs

ORMs map our database entries to objects that we define.

This allows us to write a lot of reusable logic for our database.

It also makes a lot of our more complex queries simpler and can cut down bloat in our code base.

Most ORMs also come with built in protection from SQL Injection.

Disadvantages of ORMs

ORMs aren’t perfect for every project.

Often times, you will not have direct control over the SQL the ORM is using under the hood.

This can sometimes make queries slower than they would be if you wrote them by hand.

ORMs also are more mistake prone than SQL, It’s easier to accidentally make a function that queries the database 200 times in an ORM vs SQL since you may not always realize when a function is triggering a query.

Although we’re not discussing NoSQL in this module, There are many NoSQL ORMs available as well!

TypeScript ORM

TypeORM

​

supports MySQL / MariaDB / Postgres / CockroachDB / SQLite / Microsoft SQL Server / Oracle / SAP Hana / sql.js

$ yarn add typeorm

$ typeorm init --name MyProject --database mysql

2. DataMapper

// models
import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;
    @Column()
    firstName: string;
    @Column()
    lastName: string;
    @Column()
    age: number;
}

// logic
const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.age = 25;
await repository.save(user);

const allUsers = await repository.find();
const firstUser = await repository.findOne(1); // find by id
const timber = await repository.findOne({ firstName: "Timber", lastName: "Saw" });

await repository.remove(timber);

3. ActiveRecord

// models
import {Entity, PrimaryGeneratedColumn, Column, BaseEntity} from "typeorm";

@Entity()
export class User extends BaseEntity {
    @PrimaryGeneratedColumn()
    id: number;
    @Column()
    firstName: string;
    @Column()
    lastName: string;
    @Column()
    age: number;
}

const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.age = 25;
await user.save();

const allUsers = await User.find();
const firstUser = await User.findOne(1);
const timber = await User.findOne({ firstName: "Timber", lastName: "Saw" });

await timber.remove();

RAW SQL

db.query("SELECT * FROM users");

SQL 注入

SQL Injection

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";

mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");