Database: Getting Started

Introduction

Almost every real application needs a database. JCC Express MVC keeps this flexible by supporting:

• JCC ORM (default) for SQL with a Knex-backed query layer
• Sequelize as an alternative SQL ORM
• Mongoose for MongoDB document workflows
• Prisma for schema-first SQL with generated client and migrations
• TypeORM for decorator-based entities and DataSource repositories

In practice, most projects start with the default JCC stack, then switch ORM mode only when needed.

Supported database engines

With the default JCC SQL path (DB_ORM=jcc), connection clients include:

• mysql2
• postgres
• sqlite
• better-sqlite3

For document databases, use DB_ORM=mongoose with your Mongo connection settings.

For Prisma, use DB_ORM=prisma and configure DATABASE_URL plus database.prisma.service — see Prisma.

For TypeORM, use DB_ORM=typeorm and configure database.typeorm.dataSource — see TypeORM.

Configuration

Database settings live in your app config and environment values:

• app/Config/database.ts
• app/Config/index.ts (exports database)
• .env values such as DB_ORM, DB_CONNECTION, DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD

Minimal shape from app/Config/database.ts:

export const database = {
  orm: config.get("DB_ORM", "jcc"),
  prisma: { service: PrismaService },
  typeorm: { dataSource: TypeORMDataSource },
  sequelize: { /* ... */ },
  mongoose: { /* ... */ },
};

See Prisma and TypeORM for full setup.

SQLite configuration

For SQLite style connections (sqlite or better-sqlite3), the default JCC database layer resolves to a file database (db.sqlite) at project root.

Typical env setup:

DB_ORM=jcc
DB_CONNECTION=sqlite
DB_DATABASE=db.sqlite

If you need a different file location, align your adapter/config with your deployment path strategy.

Choosing ORM mode

The provider layer uses your env/config to decide connection behavior:

• DB_ORM=jcc -> JCC/Knex SQL flow
• DB_ORM=sequelize -> Sequelize connection path
• DB_ORM=mongoose -> Mongoose connection path
• DB_ORM=prisma -> Prisma client path (see Prisma)
• DB_ORM=typeorm -> TypeORM DataSource path (see TypeORM)

Keep DB_ORM and DB_CONNECTION consistent (for example, do not pair DB_ORM=mongoose with SQL-only assumptions in your app code).

Running queries

Query builder style

const users = await DB.table("users").get();
const user = await DB.table("users").where("id", 1).first();

Raw SQL

const rows = await DB.runQuery("select * from users where active = ?", [1]);

Or:

const raw = DB.raw("select count(*) as total from users");

Use bound parameters (?) instead of string interpolation to reduce SQL injection risk.

More method usage examples

Inserts

await DB.table("users").insert({
  name: "Abdou",
  email: "abdou@example.com",
});

Updates

await DB.table("users").where("id", 1).update({
  name: "Abdou J",
});

Deletes

await DB.table("users").where("id", 1).delete();

Filtering and ordering

const activeUsers = await DB.table("users")
  .where("active", true)
  .orderBy("created_at", "desc")
  .limit(20)
  .get();

Column selection

const slim = await DB.table("users")
  .select("id", "name", "email")
  .where("active", true)
  .get();

Counting and aggregates

const totalUsers = await DB.table("users").count("id as total");
const maxScore = await DB.table("users").max("score as max_score");
const avgScore = await DB.table("users").avg("score as avg_score");

Pagination (offset/limit style)

const page = 2;
const perPage = 15;

const rows = await DB.table("users")
  .offset((page - 1) * perPage)
  .limit(perPage)
  .get();

Conditional create/update patterns

const exists = await DB.table("users").where("email", email).exists();

if (!exists) {
  await DB.table("users").insert({ email, name });
} else {
  await DB.table("users").where("email", email).update({ name });
}

Raw expression inside builder

const report = await DB.table("orders")
  .select("status")
  .select(DB.raw("count(*) as total"))
  .groupBy("status")
  .get();

Transactions

JCC DB supports transaction wrappers:

await DB.transaction(async () => {
  await DB.table("users").insert({ name: "Abdou" });
  await DB.table("profiles").insert({ user_id: 1 });
});

Keep schema-altering or implicit-commit statements out of transactional write flows unless you fully understand your database engine behavior.

Migrations and seeders

Use ArtisanNode for schema and seed lifecycle:

bun artisanNode make:migration create_users_table
bun artisanNode migrate
bun artisanNode migrate:rollback
bun artisanNode migrate:reset
bun artisanNode migrate:fresh
bun artisanNode db:seed
bun artisanNode db:wipe

Queue tables:

bun artisanNode make:queue-table
bun artisanNode migrate

Practical recommendations

• Start with the default JCC stack unless a project requirement mandates Sequelize or Mongoose.
• Keep migrations in version control and run them in CI/CD.
• Prefer parameterized queries over manual string building.
• Validate production env values (DB_ORM, credentials, host/port) early in deployment.

Summary

• JCC gives you one database entry point with multiple ORM backends.
• Config is env-driven and centered in app/Config/database.ts.
• Use DB.table(...), DB.runQuery(...), and migrations/seeders for day-to-day database work.