TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

本小节将展示如何使用pg驱动或Sequelize ORM框架进行编程交互。用户需要事先安装、配置CockroachDB。

使用pg驱动

Step 1: 安装pg驱动等

npm install pg
npm install async

Step 2: 启动节点

以非安全模式启动节点:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行Node.js代码

基本操作

Node.js代码:

var async = require('async');
var fs = require('fs');
var pg = require('pg');

// Connect to the "bank" database.
var config = {
    user: 'maxroach',
    host: 'localhost',
    database: 'bank',
    port: 26257
    //安全模式
    /*
    * ssl: {
    *    ca: fs.readFileSync('certs/ca.crt')
    *        .toString(),
    *    key: fs.readFileSync('certs/client.maxroach.key')
    *        .toString(),
    *    cert: fs.readFileSync('certs/client.maxroach.crt')
    *        .toString()
    * }
    */
};

// Create a pool.
var pool = new pg.Pool(config);

pool.connect(function (err, client, done) {

    // Close communication with the database and exit.
    var finish = function () {
        done();
        process.exit();
    };

    if (err) {
        console.error('could not connect to cockroachdb', err);
        finish();
    }
    async.waterfall([
            function (next) {
                // Create the 'accounts' table.
                client.query('CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT);', next);
            },
            function (results, next) {
                // Insert two rows into the 'accounts' table.
                client.query('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250);', next);
            },
            function (results, next) {
                // Print out account balances.
                client.query('SELECT id, balance FROM accounts;', next);
            },
        ],
        function (err, results) {
            if (err) {
                console.error('Error inserting into and selecting from accounts: ', err);
                finish();
            }

            console.log('Initial balances:');
            results.rows.forEach(function (row) {
                console.log(row);
            });

            finish();
        });
});

执行:

node basic-sample.js

输出:

Initial balances:
{ id: '1', balance: '1000' }
{ id: '2', balance: '250' }

创建事务(带重试逻辑)

Node.js代码:

var async = require('async');
var fs = require('fs');
var pg = require('pg');

// Connect to the bank database.

var config = {
    user: 'maxroach',
    host: 'localhost',
    database: 'bank',
    port: 26257
    //安全模式
    /*
    * ssl: {
    *    ca: fs.readFileSync('certs/ca.crt')
    *        .toString(),
    *    key: fs.readFileSync('certs/client.maxroach.key')
    *        .toString(),
    *    cert: fs.readFileSync('certs/client.maxroach.crt')
    *        .toString()
    * }
    */
};

// Wrapper for a transaction.  This automatically re-calls "op" with
// the client as an argument as long as the database server asks for
// the transaction to be retried.

function txnWrapper(client, op, next) {
    client.query('BEGIN; SAVEPOINT cockroach_restart', function (err) {
        if (err) {
            return next(err);
        }

        var released = false;
        async.doWhilst(function (done) {
                var handleError = function (err) {
                    // If we got an error, see if it's a retryable one
                    // and, if so, restart.
                    if (err.code === '40001') {
                        // Signal the database that we'll retry.
                        return client.query('ROLLBACK TO SAVEPOINT cockroach_restart', done);
                    }
                    // A non-retryable error; break out of the
                    // doWhilst with an error.
                    return done(err);
                };

                // Attempt the work.
                op(client, function (err) {
                    if (err) {
                        return handleError(err);
                    }
                    var opResults = arguments;

                    // If we reach this point, release and commit.
                    client.query('RELEASE SAVEPOINT cockroach_restart', function (err) {
                        if (err) {
                            return handleError(err);
                        }
                        released = true;
                        return done.apply(null, opResults);
                    });
                });
            },
            function () {
                return !released;
            },
            function (err) {
                if (err) {
                    client.query('ROLLBACK', function () {
                        next(err);
                    });
                } else {
                    var txnResults = arguments;
                    client.query('COMMIT', function (err) {
                        if (err) {
                            return next(err);
                        } else {
                            return next.apply(null, txnResults);
                        }
                    });
                }
            });
    });
}

// The transaction we want to run.

function transferFunds(client, from, to, amount, next) {
    // Check the current balance.
    client.query('SELECT balance FROM accounts WHERE id = $1', [from], function (err, results) {
        if (err) {
            return next(err);
        } else if (results.rows.length === 0) {
            return next(new Error('account not found in table'));
        }

        var acctBal = results.rows[0].balance;
        if (acctBal >= amount) {
            // Perform the transfer.
            async.waterfall([
                function (next) {
                    // Subtract amount from account 1.
                    client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from], next);
                },
                function (updateResult, next) {
                    // Add amount to account 2.
                    client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to], next);
                },
                function (updateResult, next) {
                    // Fetch account balances after updates.
                    client.query('SELECT id, balance FROM accounts', function (err, selectResult) {
                        next(err, selectResult ? selectResult.rows : null);
                    });
                }
            ], next);
        } else {
            next(new Error('insufficient funds'));
        }
    });
}

// Create a pool.
var pool = new pg.Pool(config);

pool.connect(function (err, client, done) {
    // Closes communication with the database and exits.
    var finish = function () {
        done();
        process.exit();
    };

    if (err) {
        console.error('could not connect to cockroachdb', err);
        finish();
    }

    // Execute the transaction.
    txnWrapper(client,
        function (client, next) {
            transferFunds(client, 1, 2, 100, next);
        },
        function (err, results) {
            if (err) {
                console.error('error performing transaction', err);
                finish();
            }

            console.log('Balances after transfer:');
            results.forEach(function (result) {
                console.log(result);
            });

            finish();
        });
});

执行:

node txn-sample.js

输出:

Balances after transfer:
{ id: '1', balance: '900' }
{ id: '2', balance: '350' }

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。

使用Sequelize ORM框架

更多细节可以查看example-orms项目。

Step 1: 安装Sequelize ORM

npm install sequelize sequelize-cockroachdb

Step 2: 启动节点

以非安全模式启动:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行Node.js代码

下述Node.js代码中,代码Account.sync({force: true})将根据Account模型创建accounts表:

var Sequelize = require('sequelize-cockroachdb');
var fs = require('fs');

// Connect to CockroachDB through Sequelize.
var sequelize = new Sequelize('bank', 'maxroach', '', {
    dialect: 'postgres',
    port: 26257,
    logging: false,
    //安全模式
    /*
    * dialectOptions: {
    *      ssl: {
    *          ca: fs.readFileSync('certs/ca.crt')
    *              .toString(),
    *          key: fs.readFileSync('certs/client.maxroach.key')
    *              .toString(),
    *          cert: fs.readFileSync('certs/client.maxroach.crt')
    *              .toString()
    *      }
    * }
    */
});

// Define the Account model for the "accounts" table.
var Account = sequelize.define('accounts', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true
    },
    balance: {
        type: Sequelize.INTEGER
    }
});

// Create the "accounts" table.
Account.sync({
        force: true
    })
    .then(function () {
        // Insert two rows into the "accounts" table.
        return Account.bulkCreate([{
                id: 1,
                balance: 1000
            },
            {
                id: 2,
                balance: 250
            }
        ]);
    })
    .then(function () {
        // Retrieve accounts.
        return Account.findAll();
    })
    .then(function (accounts) {
        // Print out the balances.
        accounts.forEach(function (account) {
            console.log(account.id + ' ' + account.balance);
        });
        process.exit(0);
    })
    .catch(function (err) {
        console.error('error: ' + err.message);
        process.exit(1);
    });

执行:

node sequelize-basic-sample.js

输出:

1 1000
2 250

执行查询:

cockroach sql --insecure -e 'SHOW TABLES' --database=bank
+------------+
| table_name |
+------------+
| accounts   |
+------------+
(1 row)
cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)