3.0.0 • Published 4 years ago
flora-mysql v3.0.0
flora-mysql
MySQL data source for Flora, based on the mysql module.
Usage
Add flora-mysql to Flora config
module.exports = {
…
dataSources: {
mysql: {
constructor: require('flora-mysql'),
options: {
onConnect: ['SET SESSION max_execution_time = 30000'],
servers: {
default: {
user: 'dbuser',
password: '…',
masters: [{ host: 'db01' }],
slaves: [{ host: 'db02' }, { host: 'db03' }],
}
specialServer: {
user: 'dbuser2',
password: '…',
masters: [{ host: 'specialdb01' }],
},
},
},
},
…
};
Use data source in resources
SELECT
queries may be executed on one of the slaves
servers (if present), unless useMaster
is set to true
.
// Get a Context instance
const db = api.dataSources.mysql.getContext({
db: 'users', // database name
// server: 'default', // default: 'default'
// useMaster: false, // default: false
});
// Query
const allRows = await db.query(
'SELECT firstname, lastname FROM profiles'
);
// Query with parameters
const someRows = await db.query(
'SELECT firstname, lastname FROM profiles WHERE id = ?',
[ 1000 ]
);
// Query with named parameters
const someRows = await db.query(
'SELECT firstname, lastname FROM profiles WHERE id = :userId',
{ userId: 1000 }
);
// Single column
const ids = await db.queryCol(
'SELECT id FROM profiles WHERE lastname = "Smith"'
);
// Single row (first result)
const { firstname, lastname } = await db.queryRow(
'SELECT firstname, lastname FROM profiles WHERE id = 1000'
);
// Single value (first result)
const firstname = await db.queryOne(
'SELECT firstname FROM profiles WHERE id = 1000'
);
Insert, update, delete
Write queries are executed on master
servers automatically, even when useMaster
was set to false
in getContext
.
// Insert a row
db.insert('profiles', {
firstname: 'Max',
lastname: 'Mustermann',
updatedAt: db.raw('NOW()'), // pass through unescaped
});
// Upsert (insert or update)
db.upsert(
'profiles',
{ id: 1000, firstname: 'Max' },
[ 'firstname' ] // Update firstname to "Max" if profile with id 1000 already exists
);
// Update
db.update(
'profiles',
{ updatedAt: db.raw('NOW()') }, // SET updatedAt=NOW()
{ id: 1000 } // WHERE id=1000
);
// Delete
db.delete('profiles', { id: 1000 });
Transactions
const trx = await db.transaction();
try {
await trx.update('profiles', …);
await trx.insert('log', …);
await trx.commit();
} catch (err) {
try {
// Ignore rollback errors
await trx.rollback();
} catch (ignoreErr) { }
throw err;
}
// Same as above, but shorter:
await db.transaction(async (trx) => {
await trx.update('profiles', …);
await trx.insert('log', …);
});
Features
- When being used as data source for resource-processor, SQL queries are optimized and only the columns needed are selected.
License
3.0.0
4 years ago
2.4.0
4 years ago
2.3.0
4 years ago
2.2.1
5 years ago
2.1.2
5 years ago
2.2.0
5 years ago
2.1.3
5 years ago
2.1.1
5 years ago
2.1.0
5 years ago
2.0.4
5 years ago
2.0.3
5 years ago
2.0.2
6 years ago
2.0.1
6 years ago
2.0.0
6 years ago
2.0.0-alpha.8
6 years ago
2.0.0-alpha.7
6 years ago
2.0.0-alpha.6
6 years ago
2.0.0-alpha.5
6 years ago
2.0.0-alpha.4
6 years ago
2.0.0-alpha.3
7 years ago
2.0.0-alpha.2
7 years ago
2.0.0-alpha.1
7 years ago
0.9.2
7 years ago
0.9.1
7 years ago
0.9.0
7 years ago
0.8.1
7 years ago
0.8.0
8 years ago
0.7.0
9 years ago
0.6.2
9 years ago
0.6.1
10 years ago
0.6.0
10 years ago
0.3.4
10 years ago
0.3.3
10 years ago
0.3.2
10 years ago
0.3.1
10 years ago
0.3.0
10 years ago
0.2.0
10 years ago