How to use DenoGres Methods

Note: all methods in this section are asynchronous methods. It is encouraged to use ES6 async/await syntax but promise chaining and callbacks are valid ways to invoke these methods as well.

Instance Methods

save

Insert created properties on an instance object into the database.

// create new instance of model

const person = new Person();

// create properties

person.name = 'Deno';
person.hair_color = 'purple';
person.age = '100';

// inserts created properties

await person.save();

update

Update properties on an instance object in the database.

// reassign property value

person.hair_color = 'blue';

// updates reassigned value in database

await person.update();

Model Methods

Note: ensure single spaces are entered around comparison operators ( =, >, <, >=, <=, <> )

insert

Insert row(s) into the database for the current model.
Input: (...rows: string[ ])

// inserts a new row in the database

await Person.insert('name = Deno', 'hair_color = purple').query();

edit

Update existing rows in the database for the current model.
Input: (...rows: string[ ])

// edits all values in a column

await Person.edit('hair_color = blue').query();

delete

Delete the current model (can be chained with where method).

// deletes entire model

await Person.delete().query();

// deletes where condition is met

await Person.delete().where('name = Deno').query();

select

Select column(s) from current model.
Input: (...columns: string[ ])

// either will return all columns in current model

await Person.select().query();
await Person.select('*').query();

// returns selected columns

await Person.select('name', 'hair_color').query();

// returns selected column where condition is met

await Person.select('name').where('hair_color = black').query();

where

Add condition(s) to the current query (using without 'select' method will select all columns).
AND/OR, NOT and LIKE can be added to the beginning of any arguments.
Input: (...condition: string[ ])

// return selected column where conditions are met

await Person.select('name').where('NOT age < 100', 'AND gender = male').query();

// returns all columns where conditions are met

await Person.where('hair_color = black', 'OR eye_color = blue').query();

limit

Limit the number of returned rows from the current query.
Input: (limit: number)

// returns 5 columns where conditions are met

await Person.select('name').where('hair_color = black').limit(5).query();

having

Add condition(s) involving aggregate functions to the current query.
Input: (...conditions: string[ ])

await Person.select('name').group('name', 'height').having('SUM(height) < 100').query();

innerJoin

Selects records with matching values on both tables.
Input: (column1: string, column2: string, table2: string)

await Person.select('name', 's.name').innerJoin('person.s_id', 'id', 's').query();

leftJoin

Selects records from the current table and matching values on another table.
Input: (column1: string, column2: string, table2: string)

await Person.select('name', 's.name').leftJoin('person.s_id', 'id', 's').query();

rightjoin

Selects records from another table and matching values on the current table.
Input: (column1: string, column2: string, table2: string)

await Person.select('name', 's.name').rightJoin('person.s_id', 'id', 's').query();

fullJoin

Selects all records when a match exists in either table.
Input: (column1: string, column2: string, table2: string)

await Person.select('name', 's.name').innerJoin('person.s_id', 'id', 's').query();

group

Group rows with the same values into summary rows.
Input: (...columns: string[ ])

await Person.select('name').group('people.name').query();

order

Order column(s) by ascending or descending order.
Input: (order: string, ...column: string[ ]) order must be ASC or DESC

// returns all columns ordered by ascending age, then id

await Person.select('*').order('ASC', 'age', 'id').query();

// returns selected columns ordered by descending age

await Person.select('name', 'age').order('DESC', 'age').query();

avg-count-sum-min-max

Calculate aggregate functions (can be chained with 'where' method).
Input: (column: string)

await Person.avg('cost').query(); // returns average of selected column
await Person.count('cost').query(); // returns count of selected column
await Person.sum('cost').query(); // returns sum of selected column
await Person.min('cost').query(); // returns min value of selected column
await Person.max('cost').query(); // returns max value of selected column

query

Chain with other methods to send current query to database

await Person.select('*').query();

queryInstance

Chain with methods to send query to database and create a new instance of a model with key value pairs representing the first row returned from the query. This is generally used in conjuction with the association methods to set getter functions on that instance of the model.

await Country.hasOne(Capital);
const canada = await Country.where('name = Canada').queryInstance();
const canadaCapital = await canada.getCapital();

transaction | endTransaction

Chain at the end of other model methods to create or continue a transaction. Transactions can be used across several models to group queries together.

When a single query in the transaction chain fails, all of the queries will be rolled back so the database state is never changed. To complete the transaction you can invoke endTransaction directly on the model, or chain endTransaction onto your last query.

await Person.insert('name = Alex').transaction();
await Animal.delete().where('name = Spot').transaction();
await Person.insert('nae = Rachel').endTransaction();// throws an error since 'nae' isn't a column, rolls back all previous queries and returns the error for the query that failed