Database management (Knex)

overview the database management component is a critical element of the ir engine's server core that handles persistent data storage and retrieval it provides a structured approach to defining database schemas, managing data operations, and maintaining data integrity by leveraging knex js, a flexible sql query builder, this component creates an abstraction layer between the application and the underlying database system this chapter explores the implementation, workflow, and tools used for database management within the ir engine core concepts sql database the system uses a relational database for persistent storage structured data information organized into tables with defined relationships acid compliance ensures data reliability through atomicity, consistency, isolation, and durability query language uses sql (structured query language) for data operations schema definition requires predefined structure for data storage indexing optimizes data retrieval through strategic indexing this approach provides a robust foundation for data management query builder knex js serves as a query builder to simplify database interactions sql abstraction translates javascript method chains into sql queries database agnostic works with multiple database systems (mysql, postgresql, etc ) parameterized queries prevents sql injection by properly escaping values promise based uses javascript promises for asynchronous operations migration support provides tools for evolving database schemas this abstraction layer enhances developer productivity and code maintainability schema migrations migrations define and evolve the database structure version control tracks changes to database schema over time up/down functions provides methods to apply and revert changes incremental changes allows gradual evolution of the database structure reproducibility ensures consistent database setup across environments coordination manages dependencies between structural changes this approach creates a reliable process for database schema management implementation database connection the database connection is established in a dedicated module // simplified from src/mysql ts import knex from 'knex'; import appconfig from ' /appconfig'; import { application } from ' /declarations'; / configures the database connection for the application @param app feathers application / export default function(app application) void { const logger = app get('logger'); logger info('configuring database connection'); // create knex client const db = knex({ client 'mysql2', connection { host appconfig db host, port appconfig db port, user appconfig db username, password appconfig db password, database appconfig db database }, pool { min 2, max 10 }, debug appconfig server nodeenv === 'development' }); // store the database client in the app app set('knexclient', db); // set up application lifecycle hooks app hooks({ setup async () => { logger info('running database migrations'); // run migrations to ensure schema is up to date await db migrate latest(); // run seeders if needed if (appconfig db runseeders) { logger info('running database seeders'); await runseeders(app); } logger info('database setup complete'); }, teardown async () => { logger info('closing database connection'); await db destroy(); } }); logger info('database connection configured'); } this function creates a knex client configured to connect to the database stores the client in the application for use by services sets up hooks to run migrations during application setup ensures the database connection is properly closed during shutdown migration definition migrations define the database schema structure // example migration file src/user/user/migrations/20210501000000 create user table ts import type { knex } from 'knex'; / creates the user table @param knex knex client / export async function up(knex knex) promise\<void> { // check if the table already exists if (await knex schema hastable('user')) { return; } // create the user table await knex schema createtable('user', (table) => { // primary key table uuid('id') primary(); // authentication fields table string('email') unique() notnullable(); table string('password') notnullable(); // profile fields table string('name') notnullable(); table string('avatar url') nullable(); // status fields table boolean('is active') defaultto(true); table boolean('is verified') defaultto(false); // timestamps table timestamp('created at') defaultto(knex fn now()); table timestamp('updated at') defaultto(knex fn now()); }); } / drops the user table @param knex knex client / export async function down(knex knex) promise\<void> { // drop the table if it exists await knex schema droptableifexists('user'); } this migration defines an up function to create the table with specific columns specifies column types, constraints, and default values includes a down function to revert the changes if needed checks if the table exists before attempting to create it seeder implementation seeders populate the database with initial data // example seeder file src/user/role/role seed ts import type { knex } from 'knex'; / seeds the role table with default roles @param knex knex client / export const seed = async (knex knex) promise\<void> => { // check if roles already exist const existingroles = await knex('role') select('name'); const existingrolenames = existingroles map(role => role name); // default roles to create const defaultroles = \[ { id '1', name 'admin', description 'administrator with full access' }, { id '2', name 'user', description 'standard user with limited access' }, { id '3', name 'guest', description 'guest user with minimal access' } ]; // filter out roles that already exist const rolestocreate = defaultroles filter( role => !existingrolenames includes(role name) ); // insert new roles if any if (rolestocreate length > 0) { await knex('role') insert(rolestocreate); console log(`created ${rolestocreate length} default roles`); } }; this seeder checks if the default roles already exist in the database defines the default roles to be created filters out roles that already exist to avoid duplicates inserts only the missing roles into the database seeder configuration seeders are aggregated and configured centrally // simplified from src/seeder config ts import { application } from ' /declarations'; import { userroleseed } from ' /user/role/role seed'; import { enginesettingseed } from ' /setting/engine setting/engine setting seed'; // other seeder imports / seeder configuration / export default { // core seeders that run in a specific order coreseeds \[ // user related seeders userroleseed, // settings seeders enginesettingseed, // other core seeders ], // feature seeders that can run in parallel featureseeds \[ // project related seeders projecttypeseed, // other feature seeders ] }; this configuration imports seeder functions from various modules organizes seeders into core and feature categories defines the order for core seeders that have dependencies allows feature seeders to run in parallel for efficiency knex configuration the knex configuration is defined in a dedicated file // simplified from knexfile ts import type { knex } from 'knex'; import appconfig from ' /src/appconfig'; import { custommigrationsource } from ' /src/migration source'; / knex configuration / const config knex config = { // database client client 'mysql2', // connection details from application configuration connection { host appconfig db host, port appconfig db port, user appconfig db username, password appconfig db password, database appconfig db database }, // connection pool settings pool { min 2, max 10 }, // migration configuration migrations { // custom migration source that finds migrations across the codebase migrationsource new custommigrationsource(), // table to track applied migrations tablename 'knex migrations' }, // debug mode in development debug appconfig server nodeenv === 'development' }; export default config; this configuration specifies the database client (mysql) uses connection details from the application configuration configures connection pooling for performance sets up a custom migration source to find migrations across the codebase enables debug mode in development environments custom migration source a custom migration source locates migrations across the codebase // simplified from src/migration source ts import { knex } from 'knex'; import glob from 'glob'; import path from 'path'; / custom migration source that finds migrations across the codebase / export class custommigrationsource implements knex migrationsource\<string> { // migration patterns to search for private patterns = \[ // core migrations 'src/ /migrations/ ts', // feature migrations 'src/ / /migrations/ ts' ]; // cache of migration files private migrationcache record\<string, string> | null = null; / gets all migration names / async getmigrations() promise\<string\[]> { const migrations = await this getmigrationmap(); return object keys(migrations) sort(); } / gets the migration file for a specific name @param name migration name / async getmigration(name string) promise\<knex migration> { const migrations = await this getmigrationmap(); const migrationpath = migrations\[name]; if (!migrationpath) { throw new error(`migration ${name} not found`); } // import the migration file const migration = require(migrationpath); return { up migration up, down migration down }; } / gets a map of migration names to file paths / private async getmigrationmap() promise\<record\<string, string>> { // return cached map if available if (this migrationcache) { return this migrationcache; } // find all migration files const migrationfiles string\[] = \[]; for (const pattern of this patterns) { const files = glob sync(pattern); migrationfiles push( files); } // create map of migration names to file paths const migrationmap record\<string, string> = {}; for (const file of migrationfiles) { const basename = path basename(file, path extname(file)); migrationmap\[basename] = path resolve(file); } // cache the map this migrationcache = migrationmap; return migrationmap; } } this class implements the knex migrationsource interface searches for migration files across the codebase using glob patterns creates a map of migration names to file paths imports migration files when needed caches the migration map for performance database workflow the complete database workflow follows this sequence sequencediagram participant app as feathers application participant mysql as mysql module participant knex as knex client participant migrations as migration files participant seeders as seeder files participant db as mysql database app >>mysql app configure(mysql) mysql >>knex create knex client mysql >>app store client (app set('knexclient')) app >>mysql app setup() mysql >>knex run migrations (migrate latest()) knex >>migrations load migration files migrations >>knex return migrations knex >>db execute migration sql db >>knex confirm migrations mysql >>knex run seeders knex >>seeders load seeder files seeders >>knex return seeders knex >>db execute seeder sql db >>knex confirm seeders mysql >>app setup complete note over app,db normal operation app >>app app service('users') find() app >>knex build query knex >>db execute sql db >>knex return results knex >>app return formatted data app >>mysql app teardown() mysql >>knex close connection (destroy()) knex >>db close connection db >>knex connection closed knex >>mysql confirm closure mysql >>app teardown complete this diagram illustrates the application configures the mysql module the mysql module creates a knex client and stores it in the application during setup, migrations and seeders are run to prepare the database during normal operation, services use the knex client for data operations during teardown, the database connection is properly closed query building knex js provides a fluent interface for building sql queries select queries retrieving data from the database // example of select queries import { application } from ' /declarations'; / finds users with optional filtering @param app feathers application @param query query parameters @returns promise resolving to users / async function findusers(app application, query any) promise\<any\[]> { const knex = app get('knexclient'); // basic select const allusers = await knex('user') select(' '); // select with specific columns const userprofiles = await knex('user') select('id', 'name', 'email'); // select with where clause const activeusers = await knex('user') where({ is active true }); // select with complex conditions const recentusers = await knex('user') where('created at', '>', knex raw('date sub(now(), interval 7 day)')) orderby('created at', 'desc'); // select with joins const userswithroles = await knex('user') join('user role', 'user id', '=', 'user role user id') join('role', 'user role role id', '=', 'role id') select('user id', 'user name', 'role name as role name'); // select with pagination const paginatedusers = await knex('user') select(' ') limit(10) offset(0); // return the appropriate result based on the query // this is a simplified example return allusers; } these queries demonstrate basic selection of all columns selection of specific columns filtering with where clauses complex conditions with date functions joining related tables pagination with limit and offset insert queries creating new records in the database // example of insert queries import { application } from ' /declarations'; import { v4 as uuidv4 } from 'uuid'; / creates a new user @param app feathers application @param userdata user data @returns promise resolving to the created user / async function createuser(app application, userdata any) promise\<any> { const knex = app get('knexclient'); // generate a uuid for the new user const userid = uuidv4(); // basic insert const \[id] = await knex('user') insert({ id userid, email userdata email, password userdata password, name userdata name, created at knex fn now(), updated at knex fn now() }); // insert with returning (if supported by the database) const \[createduser] = await knex('user') insert({ id userid, email userdata email, password userdata password, name userdata name }) returning(' '); // get the created user const user = await knex('user') where({ id userid }) first(); return user; } these queries demonstrate basic insertion of a record using uuid for primary keys using database functions for timestamps retrieving the created record update queries modifying existing records in the database // example of update queries import { application } from ' /declarations'; / updates a user @param app feathers application @param userid user id @param userdata user data @returns promise resolving to the updated user / async function updateuser(app application, userid string, userdata any) promise\<any> { const knex = app get('knexclient'); // basic update await knex('user') where({ id userid }) update({ name userdata name, updated at knex fn now() }); // update with returning (if supported by the database) const \[updateduser] = await knex('user') where({ id userid }) update({ name userdata name, updated at knex fn now() }) returning(' '); // get the updated user const user = await knex('user') where({ id userid }) first(); return user; } these queries demonstrate basic update of a record using where clauses to target specific records using database functions for timestamps retrieving the updated record delete queries removing records from the database // example of delete queries import { application } from ' /declarations'; / removes a user @param app feathers application @param userid user id @returns promise resolving to the removed user / async function removeuser(app application, userid string) promise\<any> { const knex = app get('knexclient'); // get the user before deletion const user = await knex('user') where({ id userid }) first(); // basic delete await knex('user') where({ id userid }) delete(); return user; } these queries demonstrate retrieving a record before deletion basic deletion of a record using where clauses to target specific records integration with other components the database management system integrates with several other components of the server core services services use the database client for data operations // example of service integration import { knexservice } from '@feathersjs/knex'; import { application } from ' / /declarations'; // service class export class userservice extends knexservice { // knexservice uses the provided model (knex client) // to perform database operations } // service registration export default function(app application) void { const options = { model app get('knexclient'), // get the database client name 'user' // table name }; app use('/users', new userservice(options, app)); } this integration uses the knex client for database operations maps service methods to sql queries handles database connections and transactions provides a consistent data access layer abstracts database details from service consumers application configuration the database connection uses configuration values // example of configuration integration import appconfig from ' /appconfig'; import knex from 'knex'; // create database connection const db = knex({ client 'mysql2', connection { host appconfig db host, port appconfig db port, user appconfig db username, password appconfig db password, database appconfig db database } }); this integration uses database configuration from the application configuration applies connection settings to the knex client enables configuration changes without code modifications supports different environments (development, production, etc ) centralizes database configuration benefits of database management the database management component provides several key advantages persistence ensures data survives application restarts structure provides a defined schema for data organization relationships enables connections between different data entities query flexibility supports complex data retrieval patterns transaction support ensures data integrity during operations migration system facilitates controlled schema evolution seeding capability enables consistent initial data setup these benefits make database management an essential foundation for the ir engine's server core next steps with an understanding of how the application stores structured data in a database, the next chapter explores how it handles unstructured data like files next storage providers docid 3y3pf5nesumshelitnxl