mr-mdb

Gerencia multiplas bases de dados MS-SQL e MySQL. Permite instanciar facilmente em um servidor http (express) servindo as bases de dados via metodologia REST.

Getting started

npm install mr-mdb

1. Definir as variaveis de ambiente

// caminho da rota raiz das requisições rest
process.env.DEFAULT_MDB_REST_ROOT_ROUTE = 'mdb-test'      // => default: 'mdb-rest'

2. Instanciando um servidor REST

Esta ferramenta pode ser facilmente configurada em um servidor http (express), sem a necessidade de manipulação dos dados.

import express from 'exprepss'
import mdb from 'mr-mdb'

const app = express()
app.use(mdb({
	defaultRootRoute: 'mdb-test',
	environments: [
		{
			name: 'my_database_mysql',
			plataform: 'mysql',
			dbConfig: {
				host: 'localhost',
				// port: 1234,
				database: 'my_database_name',
				user: 'my_user_name',
				password: 'my_password'
			},
			envDefaults: {
				idColumnName: "_id",
				deletedColumnName: "_deleted",
				deletedValue: 1,
				columnsNotShow: ["_date_insert", "_date_edit", "_date_delete"],
				updateAdd: ["_date_edit = now()"],
				insertAdd: [],
				deleteAdd: ["_date_delete = now()"],
				idAutoIncrement: true,
				relationshipConditionById: true
			}
		},
		{
			name: 'my_database_mssql',
			plataform: 'mssql',
			dbConfig: {
				server: 'localhost',
				database: 'my_database_name',
				user: 'my_user_name',
				password: 'my_password',
				options: {
					enableArithAbort: true
				}
			},
			envDefaults: {
				idColumnName: "R_E_C_N_O_",
				deletedColumnName: "D_E_L_E_T_",
				deletedValue: "*",
				columnsNotShow: ["D_E_L_E_T", "R_E_C_D_E_L_"],
				updateAdd: [],
				insertAdd: [],
				deleteAdd: ["R_E_C_D_E_L_ = R_E_C_N_O_"],
				idAutoIncrement: false,
				relationshipConditionById: false
			}
		}
	]
}))

app.listen(3000)

No exemplo foram definidos:

1. a rota raiz como mdb-test (http://localhost:3000/mdb-test)

2. dois ambientes que poderão ser requisitados utilizando a metodologia REST, sendo cada um para uma base de dados especifica:

Método | Caminho | Descrição -------- | ---------------------------------------------------------------- | -------------------------------------------------- GET | http://localhost:3000/mdb-test/:environment_name/:table_name | requisita todos os registros de uma tabela GET | http://localhost:3000/mdb-test/:environment_name/:table_name/:id | requisita um registro específico da tabela POST | http://localhost:3000/mdb-test/:environment_name/:table_name | requisita a inclusão de um registro em uma tabela PUT | http://localhost:3000/mdb-test/:environment_name/:table_name/:id | atualiza um registro específico em uma tabela DELETE | http://localhost:3000/mdb-test/:environment_name/:table_name/:id | remove um registro específico de uma tabela

3. Uso da base de dados em aplicações

2.1. Criar um ambiente

// 1. mdb.createConnection({ name: string, path?: string, logger?: boolean })
const environment = new mdb.Environment({
	name: 'my_database_mysql',
	plataform: 'mysql',
	dbConfig: {
		host: 'localhost',
		// port: 1234,
		database: 'my_database_name',
		user: 'my_user_name',
		password: 'my_password'
	},
	envDefaults: {
		idColumnName: "_id",
		deletedColumnName: "_deleted",
		deletedValue: 1,
		columnsNotShow: ["_date_insert", "_date_edit", "_date_delete"],
		updateAdd: ["_date_edit = now()"],
		insertAdd: [],
		deleteAdd: ["_date_delete = now()"],
		idAutoIncrement: true,
		relationshipConditionById: true
	}
})

// 2. Connections.createConnection({ name: string, path?: string, logger?: boolean })
const environment = new MdbEnvironment({
	name: 'my_database_mssql',
	plataform: 'mssql',
	dbConfig: {
		server: 'localhost',
		database: 'my_database_name',
		user: 'my_user_name',
		password: 'my_password',
		options: {
			enableArithAbort: true
		}
	},
	envDefaults: {
		idColumnName: "R_E_C_N_O_",
		deletedColumnName: "D_E_L_E_T_",
		deletedValue: "*",
		columnsNotShow: ["D_E_L_E_T", "R_E_C_D_E_L_"],
		updateAdd: [],
		insertAdd: [],
		deleteAdd: ["R_E_C_D_E_L_ = R_E_C_N_O_"],
		idAutoIncrement: false,
		relationshipConditionById: false
	}
})

2.2. Instanciar uma das classes MdbQuery enviando o nome da conexão desejada

QueryModel

// 1. mdb.QueryModel(tableName: string, environment: EnvironmentOpts | MdbEnvironment)
const model = new mdb.QueryModel('table_name', environment)

// 2. MdbQueryModel(tableName: string, environment: EnvironmentOpts | MdbEnvironment)
const model = new MdbQueryModel('table_name', environment)

QueryManager

// 1. mdb.QueryManager(tableName: string, environment: EnvironmentOpts | MdbEnvironment)
const qm = new mdb.QueryManager('table_name', environment)

// 2. MdbQueryManager(tableName: string, environment: EnvironmentOpts | MdbEnvironment)
const qm = new MdbQueryManager('table_name', environment)

2.3. Executar as consultas

QueryModel

// model.findAll(query?: IObjectQueryString): Promise<Record[]>
const recordset = await model.findAll({ $map: 'name,age' })

// model.findAndCountAll(query?: IObjectQueryString): Promise<QueryCollectionResponse>
const { affectedRows, recordset } = await model.findAndCountAll({ $map: 'name,age' })

// model.findById(id: number | string, query?: IObjectQueryString): Promise<Record>
const data = await model.findById(id: number | string, { $map: 'name,age' })

// model.findOne(query: IObjectQueryString): Promise<Record>
const data = await model.findOne({ name: 'joao' })

// model.create(data: Record): Promise<Record>
const data = await model.create({ name: 'joao', age: 52 })

// model.updateById(id: number | string, data: Record, overwrite?: boolean): Promise<Record>
const data = await model.updateById(1234567, { address: 'Rua 1, 15' })

// model.removeById(id): Promise<number>
const affected = await model.removeById(1234567)

QueryManager

// qm.select({ query, id }: QueryManagerOpts = {}): Promise<QueryConnectionResponse>
const { affectedRows, recordset } = await qm.select({ query: { $map: 'name,age' } })

// qm.insert(data: Record): Promise<QueryConnectionResponse>
const { affectedRows, recordset } = await qm.insert({ name: 'joao', age: 52 })

// qm.update(data: Record, { query, id }: QueryManagerOpts = {}): Promise<QueryConnectionResponse>
const { affectedRows, recordset } = await qm.update({ address: 'Rua 1, 15' }, { id: 1234567 })

// qm.delete({ query, id }: QueryManagerOpts = {}): Promise<QueryConnectionResponse>
const { affectedRows, recordset } = await qm.delete({ id: 1234567 })