Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

关系型数据库 #19

Open
luoway opened this issue Sep 23, 2023 · 0 comments
Open

关系型数据库 #19

luoway opened this issue Sep 23, 2023 · 0 comments

Comments

@luoway
Copy link
Owner

luoway commented Sep 23, 2023

关系型数据库(Relational Database,RDB)是指采用了关系模型来组织数据的数据库。

关系模型包括数据结构(如二维表)、操作指令集合(SQL语句)、完整性约束(表内数据约束、表与表之间的约束)。

SQL(Structured Query Language)是访问和操作关系型数据库的标准语言。

有代表性的关系数据库管理系统(Relational Database Management System,RDBMS)有:

  1. Oracle Database:甲骨文,支持平台多
  2. SQL Server:微软,适用于Windows系统
  3. PostgreSQL:开源,可靠性高
  4. MySQL:开源,流行易上手,是LAMP(用于Web开发的软件包,Linux、Apache、MySQL、PHP)中的一员
  5. SQLite:开源,轻量级的嵌入式数据库,无需另起数据库服务,可以嵌入到使用它的应用程序或服务器中

得益于SQL标准化,SQL适用于所有的RDBMS。

在 Node.js 中,使用 RDBMS 除了有针对特定RDBMS开发的 npm 库,也有兼容多RDBMS的 npm 库。考虑迁移数据库的可能性,推荐在生产环境使用兼容多RDBMS的 npm 库。

本文以 SQLite 为例,在 Node.js 中使用以下 npm 库分别实现操作 RDB。

SQLite

import { Database } from "sqlite3"
// 创建数据库,或打开已有的数据库
const db = new Database(__dirname + '/demo.db')
// 运行SQL
db.run('CREATE TABLE books (id INTEGER PRIMARY KEY AUTOINCREMENT, title TINYTEXT, introduction TEXT)')
// 关闭数据库
db.close()

Knex

Knex 的定位是SQL查询构建器。它提供一组对Node.js开发者友好API,替代拼写SQL查询语句的过程,并代理开发者与SQL客户端(针对特定 MS 开发的 npm 库)的交互。

import knex from 'knex'
// 打开数据库
const client = knex({
  client: 'sqlite3',
  connection: {
    filename: 'sqlite3/demo.db'
  },
  useNullAsDefault: true
})
// 增: insert into books values (null, "nodejs-roadmap", "A notebook for studying Node.js")
await client('books').insert({
  title: 'nodejs-roadmap',
  introduction: 'A notebook for studying Node.js',
})
console.log('insert')
// 查: select * from books where title="nodejs-roadmap" limit 1
const selected = await client('books').select().where({title: 'nodejs-roadmap'}).first()
console.log('select', selected)
// 改: update books set title="node.js roadmap" where id in (select id where title="nodejs-roadmap" limit 1)
await client('books').where({title: 'nodejs-roadmap'}).first().update({title: 'node.js roadmap'})
const updated = await client('books').select().where({title: 'node.js roadmap'}).first()
console.log('update', updated)
// 删: delete from books
await client('books').del()
console.log('delete')
// 关闭数据库
client.destroy()

Sequelize

Sequelize 是一个Node.js ORM(Object Relational Mapping,对象关系映射)工具。

ORM工具作用是将数据库表的概念与面向对象编程中的对象概念,通过映射联系起来,以便使用面向对象编程的方式操作关系型数据库。

连接数据库

import { Sequelize, DataType } from 'sequelize'

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'sqlite3/demo.db'
})

定义对象模型

使用对象描述表结构

const Books = sequelize.define(
  "books",
  {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
    },
    title: DataTypes.STRING,
    introduction: DataTypes.STRING,
  },
  {
    timestamps: false,
  }
)

console.log(Books === sequelize.models.books) // true

操作

import { Sequelize, DataTypes } from 'sequelize'

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'sqlite3/demo.db',
})

const Books = sequelize.define(
  'books',
  {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
    },
    title: DataTypes.STRING,
    introduction: DataTypes.STRING,
  },
  {
    timestamps: false,
  }
)

console.log(Books === sequelize.models.books) // true

// 增: insert into books values (null, "nodejs-roadmap", "A notebook for studying Node.js")
await Books.create({
  title: 'nodejs-roadmap',
  introduction: 'A notebook for studying Node.js',
})
console.log('insert')
// 查: select * from books where title="nodejs-roadmap" limit 1
const selected = await Books.findAll({
  where: {
    title: 'nodejs-roadmap',
  },
  limit: 1,
})
console.log('select', selected[0].dataValues)
// 改: update books set title="node.js roadmap" where id in (select id where title="nodejs-roadmap" limit 1)
await Books.update(
  {
    title: 'node.js roadmap',
  },
  {
    where: {
      title: 'nodejs-roadmap',
    },
    limit: 1,
  }
)
const updated = await Books.findAll()
console.log('update', updated.map((item) => item.dataValues))
// 删:delete from books
await Books.destroy({
  truncate: true, // truncate 不能回滚,实际使用的 delete
})
console.log('delete')

Prisma

Prisma 在ORM工具的基础上,使用自定义的 schema 来创建和维护数据库表结构。这样做有效节省了ORM工具重复描述表结构的工作。

schema

以下是 schema.prisma 文件

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url = env("DATABASE_URL")
}

model books {
  id Int @id @default(autoincrement())
  title String
  introduction String?
}

运行 prisma CLI 命令,根据 schema 生成 SQLite 数据库文件 demo.db

npx prisma migrate dev --name demo

操作

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// 增: insert into books values (null, "nodejs-roadmap", "A notebook for studying Node.js")
await prisma.books.create({
  data: {
    title: 'nodejs-roadmap',
    introduction: 'A notebook for studying Node.js',
  },
})
console.log('insert')

// 查: select * from books where title="nodejs-roadmap" limit 1
const selected = await prisma.books.findFirst({
  where: {
    title: 'nodejs-roadmap',
  },
})
console.log('select', selected)

// 改: update books set title="node.js roadmap" where id in (select id where title="nodejs-roadmap" limit 1)
await prisma.books.updateMany({
  data: {
    title: 'node.js roadmap',
  },
  where: {
    id: {
      in: (
        // 由于分成2次查询,容易遇到并发问题
        await prisma.books.findMany({
          select: {
            id: true,
          },
          where: {
            title: 'nodejs-roadmap',
          },
          take: 1,
        })
      ).map((item) => item.id),
    },
  },
})
const updated = await prisma.books.findMany()
console.log('update', updated)

// 删:delete from books
await prisma.books.deleteMany()
console.log('delete')

Demo

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant