@florajs/datasource-mysql


MySQL data source for Flora, based on the mysql module.

Usage

$3

``js
module.exports = {
// …
dataSources: {
mysql: {
constructor: require('@florajs/datasource-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' }],
},
},
},
},
// …
},
};
`

$3

SELECT queries may be executed on one of the slaves servers (if present), unless useMaster is set to true.

`js
// 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'
);
`

$3

Write queries are executed on master servers automatically, even when useMaster was set to false in getContext.

`js
// 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 });
`

$3

`js
const trx = await db.transaction();
try {
await trx.update('profiles', { firstname: 'John', lastname: 'Doe' }, { userId: 1337 });
await trx.insert('log', { userId: 1337, msg: 'Changes applied' });
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', { firstname: 'John', lastname: 'Doe' }, { userId: 1337 });
await trx.insert('log', { userId: 1337, msg: 'Changes applied' });
});
``

Features

- When being used as data source for resource-processor, SQL queries are optimized and only the columns needed are selected.

License

MIT