@kmacute/vin-db

Convention-driven database utilities for Drizzle ORM.

The package provides DBX, a thin convenience layer for common MySQL/MariaDB and PostgreSQL CRUD operations. DBX keeps Drizzle's schema, expressions, transactions, and low-level database client available; it does not introduce models or a second query language.

Project repository: gitlab.com/kmacute/vin-eloquent.

Installation

Install the package plus the driver for your database:

# MySQL / MariaDB (Drizzle `mysql2` driver)
npm install @kmacute/vin-db drizzle-orm mysql2

# PostgreSQL (Drizzle `node-postgres` driver)
npm install @kmacute/vin-db drizzle-orm pg

DBX targets MySQL and MariaDB through Drizzle's mysql2 driver and PostgreSQL through Drizzle's node-postgres driver.

Quick start

// db.ts
import { drizzle } from 'drizzle-orm/mysql2';
import { createDbx } from '@kmacute/vin-db';
import * as schema from './schema';

export const db = drizzle(process.env.DATABASE_URL!, {
  schema,
  mode: 'default',
});

export const dbx = createDbx(db, {
  context: async () => ({
    userId: getCurrentUserId(),
  }),
});

PostgreSQL works the same way through drizzle-orm/node-postgres:

// db.ts
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import { createDbx } from '@kmacute/vin-db';
import * as schema from './schema';

export const pool = new Pool({ connectionString: process.env.POSTGRES_URL! });

export const db = drizzle(pool, { schema });

export const dbx = createDbx(db, {
  context: async () => ({
    userId: getCurrentUserId(),
  }),
});

createDbx accepts any Drizzle PostgreSQL client (including drizzle-orm/node-postgres) and pgTable schemas. The same schema-driven conventions, primary-key discovery, soft deletes, transactions, and context controls apply to both dialects.

Use DBX for convention-driven CRUD:

await dbx.insert(posts).values({ title: 'Hello' });

const post = await dbx.find(posts, 1); // Post | null

await dbx.update(posts, 1).values({ title: 'Updated' });
await dbx.delete(posts, 1); // soft delete when deleted_at exists
await dbx.restore(posts, 1);

dbx.$drizzle exposes the original Drizzle client whenever a query needs functionality outside DBX.

Schema-driven conventions

DBX inspects each Drizzle table the first time it is used and caches the metadata. No table registration is required for standard schemas.

| SQL column | Detected behavior | |---|---| | created_at | Set on insert | | updated_at | Set on insert and update | | deleted_at | Enables soft delete and default trash filtering | | created_by_id | Set on insert | | updated_by_id | Set on insert and update | | deleted_by_id | Set on soft delete and cleared on restore |

Detection uses the actual SQL column name, not the TypeScript property name:

export const posts = mysqlTable('posts', {
  postId: int('post_id').primaryKey().autoincrement(),
  title: varchar({ length: 255 }).notNull(),
  status: varchar({ length: 50 }).notNull(),

  createdById: int('created_by_id'),
  updatedById: int('updated_by_id'),
  deletedById: int('deleted_by_id'),
  createdAt: timestamp('created_at'),
  updatedAt: timestamp('updated_at'),
  deletedAt: timestamp('deleted_at'),
});

The TypeScript property names may be aliases. For example, creator: int('created_by_id') is still detected as the created_by_id userstamp.

A table can use any combination of these columns. deleted_by_id alone does not enable soft deletes; deleted_at is the feature switch.

Primary keys

DBX discovers primary keys from Drizzle metadata. It does not assume that the property is named id.

const post = await dbx.find(posts, 100);

Composite primary keys use an object containing every key property:

const postTag = await dbx.find(postTags, {
  postId: 1,
  tagId: 5,
});

A missing primary key throws DbxPrimaryKeyNotFoundError. An incomplete or scalar composite key throws DbxInvalidCompositeKeyError.

Insert and update

DBX preserves Drizzle's insert and update type checking:

await dbx.insert(posts).values({
  title: 'Hello',
});

await dbx.insert(posts).values([
  { title: 'One' },
  { title: 'Two' },
]);

await dbx.update(posts, 1).values({
  title: 'Changed',
});

Unknown fields and invalid field types remain TypeScript errors. The underlying Drizzle write result is returned.

Automatic mutation values are applied only when the corresponding columns exist:

• Insert: created_at, updated_at, created_by_id, updated_by_id.
• Update: updated_at, updated_by_id.
• created_at and created_by_id are never changed automatically during update.

Explicit timestamp values are preserved on insert and update:

const importedAt = new Date('2000-01-01T00:00:00.000Z');

await dbx.insert(posts).values({
  title: 'Imported',
  createdAt: importedAt,
  updatedAt: importedAt,
});

await dbx.update(posts, 1).values({
  updatedAt: importedAt,
});

Userstamp values are application-managed by default. DBX replaces supplied audit user values with context.userId to prevent accidental spoofing.

Retrieval and query builder

Normal query conditions remain Drizzle SQL expressions:

import { desc, eq } from 'drizzle-orm';

const published = await dbx
  .from(posts)
  .where(eq(posts.status, 'published'))
  .orderBy(desc(posts.createdAt))
  .limit(20)
  .get(); // Post[]

Available query methods:

const rows = await dbx.from(posts).get(); // Post[]
const post = await dbx.from(posts).where(eq(posts.postId, 1)).first(); // Post | null
const required = await dbx.from(posts).firstOrFail(); // Post
const exists = await dbx.from(posts).where(eq(posts.status, 'draft')).exists(); // boolean
const missing = await dbx.from(posts).doesntExist(); // boolean
const count = await dbx.from(posts).where(eq(posts.status, 'published')).count(); // number

dbx.find(table, key) is equivalent to a primary-key query with the default soft-delete scope. dbx.findOrFail(table, key) throws DbxModelNotFoundError when no row exists.

Soft deletes

Tables with deleted_at automatically exclude deleted rows from find and DBX query-builder reads.

await dbx.delete(posts, 1); // updates deleted_at and, when present, deleted_by_id

const visible = await dbx.from(posts).get();
const includingDeleted = await dbx.from(posts).withTrashed().get();
const deletedOnly = await dbx.from(posts).onlyTrashed().get();
const explicitlyVisible = await dbx.from(posts).withoutTrashed().get();

await dbx.restore(posts, 1); // clears deleted_at/deleted_by_id and refreshes update audit fields

For tables without deleted_at, delete performs a physical delete. forceDelete always performs a physical delete, including for soft-deletable tables.

Context and mutation controls

The context resolver is optional. Its shape supports userId plus application-specific values:

interface DbxContext {
  userId?: string | number | bigint | null;
  [key: string]: unknown;
}

Disable automatic values for a scoped operation when importing or repairing data:

await dbx.withoutUserstamps(async (scopedDbx) => {
  await scopedDbx.update(posts, 1).values({ updatedById: 999 });
});

await dbx.withoutTimestamps(async (scopedDbx) => {
  await scopedDbx.insert(posts).values({ title: 'Imported' });
});

Use raw or $drizzle for operations that should bypass all DBX mutation behavior:

await dbx.raw(async (db) => {
  await db.update(posts).set({ title: 'Raw update' }).where(eq(posts.postId, 1));
});

await dbx.$drizzle.select().from(posts);

Transactions

Transactions retain the full DBX API. The context is resolved once when the transaction starts and reused inside it.

await dbx.transaction(async (tx) => {
  await tx.insert(posts).values({ title: 'Order' });

  const post = await tx.from(posts).where(eq(posts.title, 'Order')).firstOrFail();
  await tx.update(posts, post.postId).values({ title: 'Committed' });

  await tx.$drizzle.select().from(posts);
});

Errors

DBX errors are exported and support instanceof checks:

import { DbxModelNotFoundError } from '@kmacute/vin-db';

try {
  await dbx.findOrFail(posts, 999);
} catch (error) {
  if (error instanceof DbxModelNotFoundError) {
    console.log(error.table, error.primaryKey, error.value);
  }
}

| Error | Meaning | |---|---| | DbxError | Base class for DBX errors | | DbxModelNotFoundError | A required row was not found | | DbxPrimaryKeyNotFoundError | A primary-key shortcut was used on a table without a primary key | | DbxInvalidCompositeKeyError | A composite key value is incomplete or has the wrong shape | | DbxUnsupportedOperationError | The requested operation is not supported by the table schema |

Database and driver errors pass through unchanged.

Type safety

DBX is generic over the bound Drizzle schema and preserves inferred row, insert, and primary-key types:

const post = await dbx.find(posts, 1);
//    ^ Post | null

await dbx.insert(posts).values({
  invalidField: true,
});
//                        ^ TypeScript error

await dbx.find(posts, '1');
//                   ^ TypeScript error when the primary key is numeric

Existing createVinDb helpers

createVinDb remains exported for existing applications using the original helper surface, including filtered retrieval, pagination, chunking, increments, and upserts. It targets MySQL and MariaDB only; the upsert and result-handling helpers encode MySQL semantics. New code that needs convention-driven timestamps, userstamps, primary-key shortcuts, soft deletes, or DBX transactions should use createDbx.

Both APIs expose Drizzle rather than replacing it, so they can be adopted incrementally.

Development

Run these commands from packages/vin-db:

npm run typecheck   # TypeScript plus compile-time API tests
npm test            # Unit, integration, and type-level tests
npm run build       # Emit dist/

The MySQL/MariaDB integration suite reads DATABASE_URL and the PostgreSQL integration suite reads POSTGRES_URL from the repository .env.local/.env files. Each suite skips its database tests when the corresponding server is unavailable. They use dedicated vin_db_* tables and remove them afterward.