Working with external Databases

MaSH can connect to and query mysql and MS SQL Server databases.

Under the hood MaSH uses the Eloquent Query Builder package from Laravel. You can read more about the package here.

---

Connecting to a database

To connect to a database in MaSH you pass a URL to the client method of the mash.db object. The URL is in the format driver://username:password@host:port/database?options where driver is either mysql or sqlsrv.

client

mash.db.client(string: url) -> Illuminate\Database\Capsule\Manager

Connects to a database and returns the connection.

Parameters

url

A URL that contains all of the connection information for the database.

---

Performing Queries

Once we are connected we can call the table method on the connection to retrieve a Query Builder instance and begin performing queries.

table

table(string: name) -> Illuminate\Database\Query\Builder

Returns a fluent query builder instance for a database connection.

Parameters

name

The name of the table which we will be querying.

Natural

set connection to mash.db.client("mysql://user:password@myserver/mydatabase")

set query to connection.table("customers")

set customer to query.where("name", "Grace Hopper").first()

printline customer

Standard

connection = mash.db.client("mysql://user:password@myserver/mydatabase")

query = connection.table("customers")

customer = query.where("name", "Grace Hopper").first()

printline(customer)

Output

Object {
    "id": 475,
    "name": "Grace Hopper",
    "created_at": "2017-09-20 11:16:08",
    "updated_at": "2022-02-17 19:11:35",
}

---

Raw Expressions

The Query Builder is great but sometimes you may need to use a raw expression in a query. To create a raw expression, you may use the raw method. Once you have created a raw expression pass it to the select method to run the query.

raw

raw(string: statement) -> Illuminate\Database\Query\Expression

Returns a raw SQL expression.

Parameters

statement

The sql statement to be executed.

Natural

set db to mash.db.client("mysql://user:password@myserver/mydatabase")

set customer to db.select(db.raw("select * from users where `name` = 'Grace Hopper'"))

printline customer

Standard

db = mash.db.client("mysql://user:password@myserver/mydatabase")

customer = db.select(db.raw("select * from users where `name` = 'Grace Hopper'"))

printline(customer)

Output

Object {
    "id": 475,
    "name": "Grace Hopper",
    "created_at": "2017-09-20 11:16:08",
    "updated_at": "2022-02-17 19:11:35",
}