Database Operations

Overview

KnowledgeCheckr uses Drizzle ORM for type-safe database operations. All database functions are located in /database/knowledgeCheck/ and follow a consistent pattern for CRUD operations.

All database operations require authentication and run within transactions to ensure data consistency.

Knowledge Check Operations

Create Knowledge Check

Insert a new knowledge check with all related data. Source: database/knowledgeCheck/insert.ts:17

import insertKnowledgeCheck from '@/database/knowledgeCheck/insert'
import { KnowledgeCheck } from '@/schemas/KnowledgeCheck'

async function createCheck(check: KnowledgeCheck) {
  await insertKnowledgeCheck(check)
}

Parameters:

check

KnowledgeCheck

required

Complete knowledge check object validated against KnowledgeCheckSchema.

Process:

Inserts base knowledge check record
Inserts collaborators
Inserts settings
Inserts question categories
Maps questions to categories and inserts them
Inserts all answers for each question

Transaction Handling: All operations run in a database transaction. If any step fails, the entire transaction is rolled back.

await db.transaction(async (transaction) => {
  try {
    // Insert knowledge check
    const [{ id }] = await transaction
      .insert(db_knowledgeCheck)
      .values({
        id: check.id,
        name: check.name,
        description: check.description,
        difficulty: check.difficulty,
        share_key: check.share_key,
        owner_id: check.owner_id,
        openDate: formatDatetime(check.openDate),
        closeDate: check.closeDate ? formatDatetime(check.closeDate) : null,
      })
      .$returningId()

    // Insert related data...
  } catch (err) {
    logger.info('[Rollback]: Inserting db_knowledgecheck was unsuccessful!', err)
    transaction.rollback()
  }
})

Error Handling: Throws an error if the database insert doesn’t return an ID or if any validation fails.

Select Knowledge Checks

Source: database/knowledgeCheck/select.ts

Get by Owner

Retrieve all knowledge checks owned by a specific user.

import { getKnowledgeChecksByOwner } from '@/database/knowledgeCheck/select'

const checks = await getKnowledgeChecksByOwner(userId, {
  limit: 10,
  offset: 0
})

user_id

string

required

User ID to filter by.

options.limit

number

default:"10"

Maximum number of results to return.

options.offset

number

default:"0"

Number of results to skip for pagination.

Returns:

checks

KnowledgeCheck[]

Array of knowledge check objects.

Get by ID

Retrieve a single knowledge check by its ID.

import { getKnowledgeCheckById } from '@/database/knowledgeCheck/select'

const check = await getKnowledgeCheckById('123e4567-e89b-12d3-a456-426614174000')

string

required

UUID of the knowledge check.

Returns:

check

KnowledgeCheck | undefined

The knowledge check object if found, undefined otherwise.

Retrieve a knowledge check using its share token.

import { getKnowledgeCheckByShareToken } from '@/database/knowledgeCheck/select'

const check = await getKnowledgeCheckByShareToken('share-token-123')

token

string

required

Share token for the knowledge check.

This function does not require authentication to support public sharing.

Returns:

check

KnowledgeCheck | undefined

The knowledge check object if found, undefined otherwise.

Get Public Checks

Retrieve publicly accessible knowledge checks.

import { getPublicKnowledgeChecks } from '@/database/knowledgeCheck/select'

const checks = await getPublicKnowledgeChecks({
  limit: 20,
  offset: 0,
  filter: {
    difficulty: { value: 5, op: 'gte' }
  }
})

options.limit

number

default:"10"

Maximum number of results to return.

options.offset

number

default:"0"

Number of results to skip for pagination.

options.filter

TableFilters

Additional filters to apply (difficulty, date ranges, etc.).

Update Knowledge Check

Update an existing knowledge check with change tracking. Source: database/knowledgeCheck/update.ts:18

import { updateKnowledgeCheck } from '@/database/knowledgeCheck/update'
import { KnowledgeCheck } from '@/schemas/KnowledgeCheck'
import { LodashDifferences } from '@/src/lib/checks/create/SaveAction'

await updateKnowledgeCheck(modifiedCheck, changes)

modifiedCheck

KnowledgeCheck

required

The modified knowledge check object.

changes

LodashDifferences<KnowledgeCheck>

required

Array of change objects indicating which fields were modified.

Smart Updates: The function only updates the parts of the knowledge check that have changed:

If settings changed: Updates settings
If collaborators changed: Updates collaborators
If questions changed: Updates questions
If questionCategories changed: Updates categories
If base properties changed: Updates base knowledge check record

const changedKeys = changes.map((change) => change.key)

await db.transaction(async (tx) => {
  if (changedKeys.includes('settings')) {
    await updateSettings(tx, modifiedCheck.settings)
  }
  
  if (changedKeys.includes('collaborators')) {
    await updateCollaborators(tx, modifiedCheck.id, modifiedCheck.collaborators)
  }
  
  // ... other updates
})

Update only the share token for a knowledge check.

import { updateKnowledgeCheckShareToken } from '@/database/knowledgeCheck/update'

const success = await updateKnowledgeCheckShareToken({
  checkId: '123e4567-e89b-12d3-a456-426614174000',
  token: 'new-share-token'
})

checkId

string

required

UUID of the knowledge check.

token

string | null

required

New share token or null to remove sharing.

Returns:

success

boolean

True if the update was successful, false otherwise.

Security: Only the owner of a knowledge check can update its share token. The function verifies ownership:

await db
  .update(db_knowledgeCheck)
  .set({ share_key: token })
  .where(and(
    eq(db_knowledgeCheck.id, checkId),
    eq(db_knowledgeCheck.owner_id, userId)
  ))

Delete Knowledge Check

Remove a knowledge check and all related data. Source: database/knowledgeCheck/delete.ts:9

import { removeKnowledgeCheck } from '@/database/knowledgeCheck/delete'

const success = await removeKnowledgeCheck({
  checkId: '123e4567-e89b-12d3-a456-426614174000'
})

checkId

string

required

UUID of the knowledge check to delete.

Returns:

success

boolean

True if the deletion was successful (affected rows > 0), false otherwise.

Security: Only the owner can delete their knowledge check. The function enforces this:

const result = await db
  .delete(db_knowledgeCheck)
  .where(and(
    eq(db_knowledgeCheck.id, checkId),
    eq(db_knowledgeCheck.owner_id, userId)
  ))

return result[0].affectedRows > 0

Cascade Deletion: When a knowledge check is deleted, all related records are also removed:

Questions
Answers
Categories
Settings
Collaborators

This is handled by database foreign key constraints.

Question Operations

Insert Questions

Insert questions for a knowledge check. Source: database/knowledgeCheck/questions/insert.ts:10

import insertKnowledgeCheckQuestions from '@/database/knowledgeCheck/questions/insert'

const questionsWithCategories = questions.map(q => ({
  ...q,
  categoryId: 'category-uuid'
}))

await insertKnowledgeCheckQuestions(
  db,
  questionsWithCategories,
  'check-uuid'
)

DrizzleDB

required

Database transaction instance.

questions

Array<Question & { categoryId: string }>

required

Array of questions with assigned category IDs.

check_id

string

required

UUID of the parent knowledge check.

Process: For each question:

Insert question record with metadata
Based on question type, insert appropriate answers:
- Single/Multiple Choice: Insert choice answers with correct flag
- Drag and Drop: Insert answers with position information
- Open Question: Insert expectation text

Example - Inserting Choice Answers:

async function insertChoiceAnswers(
  db: DrizzleDB,
  question_id: string,
  answers: ChoiceQuestion['answers']
) {
  let index = 0
  for (const answer of answers) {
    await db.insert(db_answer).values({
      id: answer.id,
      questionId: question_id,
      answer: answer.answer,
      correct: answer.correct ? 1 : 0,
      _position: index++
    })
  }
}

Select Questions

Source: database/knowledgeCheck/questions/select.ts Retrieve questions for a knowledge check (implementation varies based on requirements).

Update Questions

Source: database/knowledgeCheck/questions/update.ts Update questions for a knowledge check, handling additions, modifications, and deletions.

Category Operations

Insert Categories

Source: database/knowledgeCheck/catagories/insert.ts Insert question categories for a knowledge check.

import { insertQuestionCategories } from '@/database/knowledgeCheck/catagories/insert'

const categories = await insertQuestionCategories(
  transaction,
  checkId,
  [
    {
      id: 'cat-1',
      name: 'Mathematics',
      prequisiteCategoryId: null,
      skipOnMissingPrequisite: false
    },
    {
      id: 'cat-2',
      name: 'Advanced Math',
      prequisiteCategoryId: 'cat-1',
      skipOnMissingPrequisite: true
    }
  ]
)

Returns:

Update Categories

Source: database/knowledgeCheck/catagories/update.ts Update categories for a knowledge check.

Select Categories

Source: database/knowledgeCheck/catagories/select.ts Retrieve categories for a knowledge check.

Settings Operations

Insert Settings

Source: database/knowledgeCheck/settings/insert.ts Insert settings when creating a knowledge check.

import insertKnowledgeCheckSettings from '@/database/knowledgeCheck/settings/insert'

await insertKnowledgeCheckSettings(transaction, knowledgeCheck)

DrizzleDB

required

Database transaction instance.

check

KnowledgeCheck

required

Knowledge check object containing settings.

Update Settings

Source: database/knowledgeCheck/settings/update.ts Update settings for an existing knowledge check.

import { updateSettings } from '@/database/knowledgeCheck/settings/update'

await updateSettings(transaction, modifiedSettings)

DrizzleDB

required

Database transaction instance.

settings

KnowledgeCheckSettings

required

Updated settings object.

Select Settings

Source: database/knowledgeCheck/settings/select.ts Retrieve settings for a knowledge check.

Collaborator Operations

Insert Collaborators

Source: database/knowledgeCheck/collaborators/insert.ts Add collaborators to a knowledge check.

import { insertCollaboratorsToKnowledgeCheck } from '@/database/knowledgeCheck/collaborators/insert'

await insertCollaboratorsToKnowledgeCheck(
  transaction,
  checkId,
  ['user-id-1', 'user-id-2', 'user-id-3']
)

DrizzleDB

required

Database transaction instance.

checkId

string

required

UUID of the knowledge check.

collaborators

string[]

required

Array of user IDs to add as collaborators.

Update Collaborators

Source: database/knowledgeCheck/collaborators/update.ts Update the list of collaborators for a knowledge check.

Select Collaborators

Source: database/knowledgeCheck/collaborators/select.ts Retrieve collaborators for a knowledge check.

Data Transformation

Convert to Database Format

Source: database/knowledgeCheck/transform.ts Transform Zod schema objects to database-compatible format.

import createConvertToDatabase from '@/src/schemas/utils/createConvertToDatabase'
import { KnowledgeCheckSchema } from '@/src/schemas/KnowledgeCheck'
import { db_knowledgeCheck } from '@/database/drizzle/schema'

const convertTo = createConvertToDatabase(
  KnowledgeCheckSchema,
  db_knowledgeCheck
)

const dbFormat = convertTo(knowledgeCheck)

Purpose:

Converts Date objects to formatted datetime strings
Maps schema field names to database column names
Handles type conversions (boolean to number, etc.)
Strips computed/virtual fields

Transaction Best Practices

All complex operations use transactions to ensure data consistency:

import getDatabase from '@/database/Database'

const db = await getDatabase()

await db.transaction(async (tx) => {
  try {
    // Perform multiple related operations
    await insertKnowledgeCheck(tx, check)
    await insertSettings(tx, check.settings)
    await insertQuestions(tx, check.questions)
    
    // Transaction commits automatically if no errors
  } catch (err) {
    // Transaction rolls back automatically on error
    logger.error('Transaction failed:', err)
    tx.rollback()
  }
})

Key Points:

Use transactions for operations that modify multiple related tables
Always handle errors and call rollback() on failure
Pass the transaction instance to nested operations
Transactions auto-commit on success

API Route Example

Complete example of using database operations in an API route. Source: src/app/api/insert/knowledgeCheck/route.ts:6

import { NextRequest, NextResponse } from 'next/server'
import insertKnowledgeCheck from '@/database/knowledgeCheck/insert'
import requireAuthentication from '@/src/lib/auth/requireAuthentication'
import { safeParseKnowledgeCheck } from '@/src/schemas/KnowledgeCheck'

export async function POST(req: NextRequest) {
  // Parse request body
  let body: unknown
  try {
    body = await req.json()
  } catch {
    return NextResponse.json(
      { message: 'Please provide a valid json body!' },
      { status: 400 }
    )
  }

  // Authenticate user
  const { user } = await requireAuthentication()

  // Validate body
  if (!body) {
    return NextResponse.json(
      { message: 'Body must not be empty!' },
      { status: 400 }
    )
  }

  // Parse and validate with Zod schema
  const {
    success,
    error,
    data: check
  } = safeParseKnowledgeCheck(
    Object.assign(body, { owner_id: user.id })
  )
  
  if (!success) {
    return NextResponse.json(
      {
        message: 'Please provide a valid knowledgecheck instance!',
        errors: error,
        timestamp: Date.now()
      },
      { status: 400 }
    )
  }

  // Insert into database
  await insertKnowledgeCheck(check)

  return NextResponse.json({ success: true }, { status: 200 })
}

Flow:

Parse JSON body
Authenticate user
Validate request body against schema
Perform database operation
Return success/error response

Error Handling

All database operations include comprehensive error handling:

try {
  await insertKnowledgeCheck(check)
} catch (err) {
  if (err.code === 'ER_DUP_ENTRY') {
    // Handle duplicate entry
  } else if (err.code === 'ER_NO_REFERENCED_ROW') {
    // Handle foreign key constraint
  } else {
    // Handle other errors
    logger.error('Database error:', err)
  }
}

Common Database Errors:

ER_DUP_ENTRY: Duplicate unique key
ER_NO_REFERENCED_ROW: Foreign key constraint violation
ER_ROW_IS_REFERENCED: Cannot delete referenced row
ER_DATA_TOO_LONG: Data exceeds column size

Query Filtering

Source: database/knowledgeCheck/query.ts The getKnowledgeChecks function supports flexible filtering:

import { getKnowledgeChecks } from '@/database/knowledgeCheck/query'

const checks = await getKnowledgeChecks({
  limit: 10,
  offset: 0,
  baseFilter: {
    difficulty: {
      value: 5,
      op: 'gte' // greater than or equal
    },
    owner_id: {
      value: 'user-123',
      op: 'eq' // equals
    }
  }
})

Supported Operators:

eq: Equals
ne: Not equals
gt: Greater than
gte: Greater than or equal
lt: Less than
lte: Less than or equal
like: Pattern matching
in: In array

Architecture

API Reference

Deployment

Overview

Knowledge Check Operations

Create Knowledge Check

Select Knowledge Checks

Get by Owner

Get by ID

Get Public Checks

Update Knowledge Check

Delete Knowledge Check

Question Operations

Insert Questions

Select Questions

Update Questions

Category Operations

Insert Categories

Update Categories

Select Categories

Settings Operations

Insert Settings

Update Settings

Select Settings

Collaborator Operations

Insert Collaborators

Update Collaborators

Select Collaborators

Data Transformation

Convert to Database Format

Transaction Best Practices

API Route Example

Error Handling

Query Filtering

Build docs developers (and LLMs) love

Architecture

API Reference

Deployment

​Overview

​Knowledge Check Operations

​Create Knowledge Check

​Select Knowledge Checks

​Get by Owner

​Get by ID

​Get by Share Token

​Get Public Checks

​Update Knowledge Check

​Update Share Token

​Delete Knowledge Check

​Question Operations

​Insert Questions

​Select Questions

​Update Questions

​Category Operations

​Insert Categories

​Update Categories

​Select Categories

​Settings Operations

​Insert Settings

​Update Settings

​Select Settings

​Collaborator Operations

​Insert Collaborators

​Update Collaborators

​Select Collaborators

​Data Transformation

​Convert to Database Format

​Transaction Best Practices

​API Route Example

​Error Handling

​Query Filtering

Build docs developers (and LLMs) love

Overview

Knowledge Check Operations

Create Knowledge Check

Select Knowledge Checks

Get by Owner

Get by ID

Get by Share Token

Get Public Checks

Update Knowledge Check

Update Share Token

Delete Knowledge Check

Question Operations

Insert Questions

Select Questions

Update Questions

Category Operations

Insert Categories

Update Categories

Select Categories

Settings Operations

Insert Settings

Update Settings

Select Settings

Collaborator Operations

Insert Collaborators

Update Collaborators

Select Collaborators

Data Transformation

Convert to Database Format

Transaction Best Practices

API Route Example

Error Handling

Query Filtering