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.