Basic Retrieval
Back in the DataContext section, the DataContext.from method was mentioned briefly. This method is the starting point for retrieving records, and it takes a single parameter: An EntityType, which is some Table-decorated class. (See the Models section.) DataContext.from returns a FromAdapter instance which has a number of useful operations that we’ll step through in the following sections.
Retrieve All From a Single Table
Let’s say you want to select all
Person
records from the people
table that we defined in the
Migrations section. To do so, you would call the
FromAdapter.select
method.
const query: Select<Person> = dataContext
.from(Person)
.select();
You get back a Select instance. As was mentioned in the create example, all Formn queries extend the Query class and thus share a common interface. We can view the SQL associated with the Query instance using the toString method.
console.log(query.toString());
And we can execute the query to retrieve the Person records.
const people: Person[] = await query
.execute();
Full Example
A complete example follows. It’s available in the formn-example
repository under
src/retrieve/retrieve-all-people.ts.
import { inspect } from 'util';
import { MySQLDataContext, ConnectionOptions, Select } from 'formn';
import { Person } from '../entity/person.entity';
async function main() {
const connOpts: ConnectionOptions = require('../../connections.json');
const dataContext = new MySQLDataContext();
try {
await dataContext.connect(connOpts);
// Create a Select query, which will pull all People records from the
// people table.
const query: Select<Person> = dataContext
.from(Person)
.select();
// Log the generated SQL.
console.log(query.toString());
// Execute the query to get all Person records.
const people: Person[] = await query
.execute();
console.log(inspect(people, {depth: null, compact: false}));
await dataContext.end();
}
catch(err) {
console.error(err);
}
}
main();
Run it using ts-node
.
npx ts-node ./src/retrieve/retrieve-all-people.ts
The output will look similar to the following.
SELECT `people`.`createdOn` AS `people.createdOn`,
`people`.`firstName` AS `people.firstName`,
`people`.`lastName` AS `people.lastName`,
`people`.`personID` AS `people.id`
FROM `people` AS `people`
[
Person {
id: 1,
createdOn: 2019-01-29T02:57:06.000Z,
firstName: 'Joe',
lastName: 'Shmo'
},
Person {
id: 2,
createdOn: 2019-01-29T02:57:06.000Z,
firstName: 'Rand',
lastName: 'al\'Thor'
},
Person {
id: 3,
createdOn: 2019-01-29T02:57:06.000Z,
firstName: 'Holly',
lastName: 'Davis'
},
Person {
id: 4,
createdOn: 2019-01-29T02:57:06.000Z,
firstName: 'Jenny',
lastName: 'Mather'
},
Person {
id: 5,
createdOn: 2019-01-29T03:05:14.000Z,
firstName: 'Mickey',
lastName: 'Mouse'
},
Person {
id: 6,
createdOn: 2019-01-29T03:06:01.000Z,
firstName: 'Abe',
lastName: 'Lincoln'
}
]