Akos

数据库(Mysql)

数据库使用主要采用bookshelf.js, sequelize.js。

bookshelf.js

bookshelf.js

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. It features both Promise-based and traditional callback interfaces, transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

It is designed to work with PostgreSQL, MySQL, and SQLite3.

Website and documentation. The project is hosted on GitHub, and has a comprehensive test suite.

Introduction

Bookshelf aims to provide a simple library for common tasks when querying databases in JavaScript, and forming relations between these objects, taking a lot of ideas from the the Data Mapper Pattern.

With a concise, literate codebase, Bookshelf is simple to read, understand, and extend. It doesn't force you to use any specific validation scheme, and provides flexible, efficient relation/nested-relation loading and first-class transaction support.

It's a lean object-relational mapper, allowing you to drop down to the raw Knex interface whenever you need a custom query that doesn't quite fit with the stock conventions.

Installation

You'll need to install a copy of Knex, and either mysql, pg, or sqlite3 from npm.

$ npm install knex --save
$ npm install bookshelf --save

# Then add one of the following:
$ npm install pg
$ npm install mysql
$ npm install mariasql
$ npm install sqlite3

The Bookshelf library is initialized by passing an initialized Knex client instance. The Knex documentation provides a number of examples for different databases.

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
});

var bookshelf = require('bookshelf')(knex);

var User = bookshelf.Model.extend({
  tableName: 'users'
});

This initialization should likely only ever happen once in your application. As it creates a connection pool for the current database, you should use the bookshelf instance returned throughout your library. You'll need to store this instance created by the initialize somewhere in the application so you can reference it. A common pattern to follow is to initialize the client in a module so you can easily reference it later:

// In a file named something like bookshelf.js
var knex = require('knex')(dbConfig);
module.exports = require('bookshelf')(knex);

// elsewhere, to use the bookshelf client:
var bookshelf = require('./bookshelf');

var Post = bookshelf.Model.extend({
  // ...
});

Examples

Here is an example to get you started:

var knex = require('knex')({
  client: 'mysql',
  connection: process.env.MYSQL_DATABASE_CONNECTION
});
var bookshelf = require('bookshelf')(knex);

var User = bookshelf.Model.extend({
  tableName: 'users',
  posts: function() {
    return this.hasMany(Posts);
  }
});

var Posts = bookshelf.Model.extend({
  tableName: 'messages',
  tags: function() {
    return this.belongsToMany(Tag);
  }
});

var Tag = bookshelf.Model.extend({
  tableName: 'tags'
})

User.where('id', 1).fetch({withRelated: ['posts.tags']}).then(function(user) {
  console.log(user.related('posts').toJSON());
}).catch(function(err) {
  console.error(err);
});

Plugins

  • Registry: Register models in a central location so that you can refer to them using a string in relations instead of having to require it every time. Helps deal with the challenges of circular module dependencies in Node.
  • Virtuals: Define virtual properties on your model to compute new values.
  • Visibility: Specify a whitelist/blacklist of model attributes when serialized toJSON.
  • Pagination: Adds fetchPage methods to use for pagination in place of fetch and fetchAll.
  • Case Converter: Handles the conversion between the database's snake_cased and a model's camelCased properties automatically.
  • Processor: Allows defining custom processor functions that handle transformation of values whenever they are .set() on a model.

Community plugins

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Sequelize follows SEMVER. Supports Node v6 and above to use ES6 features.

New to Sequelize? Take a look at the Tutorials and Guides. You might also be interested in the API Reference.

v5 Release

You can find the upgrade guide and changelog here.

Table of Contents

Installation

$ npm install --save sequelize # This will install v5

# And one of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

Documentation

Responsible disclosure

If you have any security issue to report, contact project maintainers privately. You can find contact information in CONTACT.md.

Resources

Tools

Learning

Translations

← sequelize ORM →