dlayer-knex/README.md

# dlayer-knex

Small utility for adding database support to your dlayer schema, using Knex, with minimum viable functionality that doesn't get in your way.

- Currently supports SQLite and PostgreSQL only (as this library needs to do a level of introspection that Knex does not have implementations for)
- Deliberately very limited featureset and API surface; you are expected to manually specify *complex* queries in your graph (and dlayer's extension system makes this possible)
- Current status: __beta__. Should work fine, but if it breaks, you get to keep both halves.
- Documentation may be incomplete.

## Realistic-ish example

```js
const knex = require("knex");
const dlayer = require("dlayer");
const dlayerKnex = require("dlayer-knex");

let db = knex(require("./knexfile").development);

let knexUsers = await dlayerKnex.generateModule(db, "users");

let knexThreads = await dlayerKnex.generateModule(db, "threads", {
	user_id: [ "user", "threads" ]
});

let knexPosts = await dlayerKnex.generateModule(db, "posts", {
	user_id: [ "user", "posts" ],
	thread_id: [ "thread", "posts" ]
});

let api = dlayer({
	makeContext: () => ({}),
	modules: [
		knexUsers.module,
		knexPosts.module,
		knexThreads.module
	],
	schema: {
		users: knexUsers.rootQueries,
		threads: knexThreads.rootQueries,
		posts: knexPosts.rootQueries
	}
});

let { moreThan, lessThan, not } = dlayerKnex;
let testResult = await api.query({
	users: {
		list: {
			$arguments: {
				filter: { id: not(lessThan(2)) }
			},
			id: true,
			username: true,
			threads: {
				id: true,
				subject: true,
				user: {
					id: true,
					username: true
				}
			}
		}
	}
});

/* 
testResult = {
	users: {
		list: [{
			id: 2,
			username: 'joepie91',
			threads: [{
				id: 2,
				subject: 'Test 2',
				user: {
					id: 2,
					username: 'joepie91'
				}
			}, {
				id: 3,
				subject: 'Test 3',
				user: {
					id: 2,
					username: 'joepie91'
				}
			}]
		}, {
			id: 3,
			username: 'bar',
			threads: [{
				id: 1,
				subject: 'Test 1',
				user: {
					id: 3,
					username: 'bar'
				}
			}]
		}, {
			id: 4,
			username: null,
			threads: []
		}]
	}
}
*/
```

## API

### generateModule(knexInstance, tableName, relations)

Relations are always defined on the table which stores the referenced ID in a column; the inverse relationship will be automatically created on the foreign table.

The foreign key already needs to be set up in the database schema - `dlayer-knex` reads out the table schema to set up the relations! The values you specify here are only to indicate on which fields of the `dlayer-knex`-generated objects you want the relations to be available.

So assuming that in the schema, `threads.user_id` points to `users.id`, then with this definition:

```js
let knexThreads = await dlayerKnex.generateModule(db, "threads", {
	user_id: [ "user", "threads" ]
});
```

... that means that the following fields are created:

- A `user` field on any `threads` objects created by `dlayer-knex`, referencing the corresponding object from `users` where `users.id = threads.user_id`
- A `threads` field on any `users` objects created by `dlayer-knex`, with a list of all records from the `threads` table that have `threads.user_id` set to the `users` object's `id`

(This is equivalent to a set of `belongsTo` and `hasMany` relations in many ORMs, you just only specify the destination field names in `dlayer-knex`'s config.)

Through-relations, ie. using many-to-many tables, are not *explicitly* supported in `dlayer-knex`, but they don't need to be; you can simply treat the intermediate table as its own type with its own definition and relations, and do two steps of relation-resolving in your `dlayer` query.

For example, if you have a `communities` and `users` table, then the many-to-many table that links them together might be called `memberships`, and you might end up with a query like `users[0].memberships.community` to obtain that user's communities. (This is pseudocode, dlayer does not currently use that sort of syntax, but you hopefully get the idea!)

### lessThan(value), moreThan(value)

Pretty much what it says on the tin; can be used in query arguments in place of exact values.

### anyOf(arrayOfValues)

Can be used in query arguments in place of exact values. Will filter for the value being *one of* the specified values, rather than just a single one. Cannot currently be wrapped around `lessThan`, `moreThan`, or `not` - that might be added in the future, if people have a good reason for it.

### not(value)

Negates the match. Can be wrapped around a `lessThan`/`moreThan`/`anyOf` wrapper, but not around *another* `not` wrapper. Can also be used with exact values.

## dlayer module schema

The `generateModule` method generates a dlayer module and a number of root queries, that you can use in your dlayer schema. The module defines the following things:

- A type representing records from the specified table; it will be named `dlayer-knex.TABLE_NAME`, where `TABLE_NAME` is the name of the table in the database. This is the name you should use when extending the type, eg. to add complex queries.
- Extensions for any other `dlayer-knex` types, that are necessary to generate inverse relations. You don't generally have to care about these.
- A `dlayerKnexTable` value in the module's context, which is a DataLoader that fetches items from the table by its primary key. You don't generally have to care about this, *unless* you need to work around an API limitation; in that case, you can use dlayer's `$getModuleContext` utility function to access it, but keep in mind that this context value is not included in the semver guarantees for this library, and may break between releases.

Separately, a static representation of the generated types' `structure` (for introspection, yet to be documented) and a set of root queries are also generated. You can insert the root queries into your root schema where it fits your usecase. Often this will be in a top-level property with the same name as the table, but it doesn't *need* to be there. These root queries are:

### list

Equivalent to a `SELECT` query, retrieves existing records from a table. Possible arguments:

- __filter:__ an object of predicates to match records against, optionally using `moreThan`/`lessThan`/`anyOf`/`not`.
- __orderBy:__ a column/field name to order the results by; ascending by default, prefix it with a `-` to sort descendingly.
- __skip:__ the amount of records to skip at the start; also known as an 'offset' or 'start'.
- __limit:__ the amount of records to retrieve, counting from the first non-skipped record.
- __first:__ when set to `true`, only return the first result (or `undefined`), rather than a list of results.
- __count:__ when set to `true`, return the count of results instead of the results themselves.

This query produces a list of records according to your criteria (or a single one or the total count, if respectively `first` or `count` has been used). When using the `count` argument, all other arguments except for `filter` are disabled, as they are not meaningful when counting results.

Note that inverse relation functions generated on types are *also* list-type queries; you can provide the same set of arguments to them, to filter down the list of related objects. For example, this is a valid query to fetch the last 10 posts for a user named 'testuser':

```js
await api.query({
	users: {
		list: {
			$arguments: { filter: { username: "testuser" } },
			id: true,
			posts: {
				$arguments: { orderBy: "-postedAt", limit: 10 },
				id: true,
				title: true
			}
		}
	}
})
```

As before, if you need to specify a more complex relation query than these arguments permit, you should consider adding a custom property with `dlayer` directly instead, which will allow you to structure your query and construct your result objects however you wish.

### delete

Equivalent to a `DELETE` query, deletes records from a table. Possible arguments:

- __filter:__ an object of predicates to match records against, same as for `list`. Only these records will be deleted.

Produces an object `{ count: Number }` to tell you how many records were deleted.

### change

Equivalent to an `UPDATE` query, changes existing records in a table, based on some predicate. Possible arguments:

- __filter:__ an object of predicates to match records against, same as for `list`. Only these records will be changed.
- __values:__ an object of new values to set for the matched records. Relation fields are ignored; you need to set their underlying ID references instead. Only literal values can be specified here.

This query produces a list of the matched records, *after* having been updated with the specified new values.

### create

Equivalent to an `INSERT` query, creates new records in a table. Possible arguments:

- __values:__ an array of objects, that should be created in the table. Here, again, relation fields are ignored, and you need to set their underlying ID references. Only literal values accepted.

Produces a list of the newly created records, *after* having been inserted into the database; ie. including their new automatically-assigned ID.