Skip to main content

Query Builder

Formidable uses Knex.js as its database query builder. You may use any of the Knex.js methods to build your queries.

Select Query​

To select data from the database, use the select method from the DB class:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => DB.select('id', 'title').from('tasks'))

If you want to select all columns from a table, you can leave the select method empty:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => DB.select().from('tasks'))

Insert Query​

To insert data into the database, use the insert method after using the table method to specify the table to insert into:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.insert({
			title: 'Task 1',
			description: 'This is a task'
		})
})

Alternatively you can use the into method to specify the table to insert into after using the insert method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.insert({
			title: 'Task 1',
			description: 'This is a task'
		})
		.into('tasks')
})

Multi Insert​

To insert multiple rows at once, use the insert method with an array of objects:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.insert([
			{
				title: 'Task 1',
				description: 'This is a task'
			},
			{
				title: 'Task 2',
				description: 'This is another task'
			}
		])
})

Create​

If you want to insert and return the created row or rows, use the create method. This method is a combination of the insert and returning methods:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', async () => {
	const task = await DB.table('tasks')
		.create({
			title: 'Task 1',
			description: 'This is a task'
		})

	return task
})

Soft Delete​

To soft delete a row, use the softDelete method. This method will set the deleted_at column to the current timestamp:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.where('id', 1)
		.softDelete()
})

Restore​

To restore a soft deleted row, use the restore method. This method will set the deleted_at column to null:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.where('id', 1)
		.restore()
})

With Trashed​

To include soft deleted rows in your query, use the withTrashed method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.withTrashed()
		.where('id', 1)
})

Without Trashed​

To exclude soft deleted rows from your query, use the withoutTrashed method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.withoutTrashed()
		.where('id', 1)
})

Only Trashed​

To only include soft deleted rows in your query, use the onlyTrashed method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('tasks')
		.onlyTrashed()
		.where('id', 1)
})

Raw Query​

To run a raw query, use the raw method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => DB.raw('select * from users where id = ?', [1]))

Joins​

To join tables, use the join method:

routes/api.ts
import { DB } from '@formidablejs/framework'
import { Route } from '@formidablejs/framework'

Route.get('/', () => {
	return DB.table('posts')
		.join('users', 'posts.id', '=', 'users.user_id')
		.select(
			'posts.id as post_id',
			'users.id as user_id',
			'users.name',
			'posts.title',
			'posts.body'
		)
})

For more on joins, see the Knex.js documentation.