Getting started

Guide

This guide walks through a complete example: setting up jsorm.config.ts, defining models, inserting records, and running typed queries.

Tip

Complete Installation first. You need the engine binary provisioned by jsorm init before queries can run.

1. Define models

   Import defineModel and the field builder t. The same definition drives both TypeScript types and runtime query behavior.

   // src/schema/main/user.ts
   import { defineModel, t } from 'jsorm';
   
   export const Role = defineModel('roles', {
     id: t.number().primary(),
     name: t.string().unique(),
   });
   
   export const User = defineModel('users', {
     id: t.number().primary(),
     name: t.string(),
     email: t.string().optional(),
     active: t.boolean().default(true),
     createdAt: t.date(),
     role: t.belongsTo(Role),
   });

   Export inferred types when you need them elsewhere:

   import type { InferModel, InferInput } from 'jsorm';
   
   type UserRecord = InferModel<typeof User>;
   // { id: number; name: string; email?: string; active: boolean; createdAt: Date; role: RoleRecord }
   
   type UserInput = InferInput<typeof User>;
   // { name: string; email?: string; active?: boolean; createdAt: Date; role: { connect: number } | ... }

2. Create jsorm.config.ts

   One config file, used by both the runtime singleton and the CLI. Run jsorm init to generate it, or write it manually:

   // jsorm.config.ts
   import { defineConnectionSource, defineJsormConfig } from 'jsorm';
   import { pgAdapter } from 'jsorm-pg';
   
   const main = defineConnectionSource({
     adapter: pgAdapter({
       name: 'main',
       connectionString: process.env.DATABASE_URL!,
       pool: { min: 2, max: 10 },
     }),
   });
   
   export default defineJsormConfig({
     connectionSources: { main },
     defaults: {
       connectionSource: 'main',
     },
   });

   The global jsorm singleton lazy-loads this file automatically from process.cwd() on first use.

3. Insert data

   Import jsorm and call jsorm.insert(). Relations can be connected or created inline.

   import { jsorm } from 'jsorm';
   import { User } from './src/schema/index.js';
   
   // Connect to an existing role by ID
   await jsorm.insert(User, {
     name: 'Alice',
     email: 'alice@example.com',
     createdAt: new Date(),
     role: { connect: 1 },
   });
   
   // Create a new role and user together
   await jsorm.insert(User, {
     name: 'Bob',
     createdAt: new Date(),
     role: { create: { name: 'editor' } },
   });

4. Read data

   Use jsorm.get(). Every field and relation you select is typed exactly.

   const users = await jsorm.get(User, {
     select: {
       id: true,
       name: true,
       email: true,
       role: { name: true },
     },
     where: {
       active: true,
       role: { name: { eq: 'admin' } },
     },
     orderBy: [{ name: 'asc' }],
     pagination: {
       perPage: 10,
       currentPage: 1,
     },
   });
   // users.data: Array<{ id: number; name: string; email?: string; role: { name: string } }>
   // users.pagination: { perPage, currentPage, total, lastPage, ... }

5. Update records

   Always include where on write operations.

   await jsorm.update(User, {
     data: { active: false },
     where: { role: { name: { eq: 'guest' } } },
   });

6. Delete records

   Relation cascades follow your model’s onDelete config.

   await jsorm.delete(User, {
     where: { id: { eq: 10 } },
   });

Raw SQL

For queries the JSON DSL does not cover, use jsorm.executeSql(). Always use parameterized placeholders:

const result = await jsorm.executeSql(
  'SELECT COUNT(*) AS total FROM users WHERE active = $1',
  [true],
);

Caution

Never interpolate user input directly into SQL strings. Always use $1 / ? placeholders.

Health check

Use jsorm.healthCheck() to verify database connectivity — useful in readiness probes and deploy verification:

const health = await jsorm.healthCheck();
// { main: 'ok' }

Graceful shutdown

Call jsorm.close() to drain connection pools on process exit:

process.on('SIGTERM', async () => {
  await jsorm.close();
  process.exit(0);
});

Best practices

1. Import jsorm once from 'jsorm' — it’s a global singleton backed by jsorm.config.ts.
2. Use jsorm.use('name') to target a non-default connection source.
3. Keep where clauses explicit on all write operations.
4. Prefer the typed JSON DSL; reserve executeSql for genuinely custom queries.
5. Always close the pool on graceful shutdown.