Coming from Sequelize

Sequelize is an active-record ORM: a Model class both describes a table and is its rows, with statics like findAll and create and instances that save() themselves. kick/db keeps the table declaration in TypeScript but drops the active-record half. Queries are built on a typed client and return plain rows whose types are inferred from the schema — no Model.init attributes to keep in sync with an interface, no InferAttributes — and nothing saves itself.

The closest thing to a model class is the base-class table form. class User extends TableBase('users', { … }) declares the table, and the class is the row type. User.table is the table itself — what you pass to relations(), foreign keys and insertSchema. Queries return plain objects; User.from(row) turns one into a User when you want its instance methods.

Concepts at a glance

Sequelize	kick/db
class User extends Model + User.init({ … }, { sequelize })	TableBase('users', { … }) class, or table() — Table Forms
DataTypes.STRING(255), DECIMAL(12, 2), DATE, JSON	varchar(255), decimal(12, 2), timestamp(), json<T>() — Tables & Columns
allowNull: false	.notNull() — columns are nullable by default, as in Sequelize
autoIncrement: true / defaultValue: DataTypes.UUIDV4	serial().primaryKey() / uuid().primaryKey().defaultRandom() (generated by the database)
timestamps: true (createdAt, updatedAt)	createdAt: timestamp().notNull().defaultNow(), updatedAt: timestamp().notNull().defaultNow().onUpdateNow() — maintained columns
paranoid: true	deletedAt: timestamp().softDelete() — relational reads skip deleted rows; you delete by setting it (maintained columns)
hasMany / belongsTo / hasOne	a foreign key with .references(), plus relations() with many / one — Keys & Constraints
belongsToMany(Tag, { through })	a junction table, and many(tags, { through: postTags }) — Many-to-many
include: [{ model: Post, where, limit }]	db.query.users.findMany({ with: { posts: { where, limit } } }) — one query — Relational Queries
user.getPosts() (lazy association getters)	none — query the related rows
findAll / findOne / findByPk / findAndCountAll	selectFrom(…).where(…).execute() / .executeTakeFirst(); db.query.X.findManyAndCount() → { data, total } — Queries
create / bulkCreate / update(…, { where }) / destroy	insertInto(…).values(row or rows) / updateTable / deleteFrom
instance.save() / instance.update()	none — write with updateTable
upsert / findOrCreate	db.upsert(table, { values, target }) / db.findOrCreate(table, { where, create }) — race-safe — Upsert and find-or-create
Op.gte, Op.in, Op.like, Op.or, { [Op.is]: null }	where('col', '>=', v), 'in', 'like', eb.or([…]), 'is', null
sequelize.transaction(async (t) => …) + { transaction: t }	db.transaction(async () => …) — the plain client joins it, no option to pass — Transactions
Sequelize.useCLS(namespace)	built in: transactions follow the async call chain with no setup
t.afterCommit(fn)	db.afterCommit(fn)
hooks (beforeCreate, afterUpdate, …)	no per-row hooks: use the service, a custom column codec, or query events / plugins
model validate: { isEmail: true }	table rules + insertSchema(User.table) — Validation from Tables
scopes / defaultScope	none — named functions in a repository, or $extends per-table methods
instance / class methods	methods on the TableBase class (with X.from(row)), or $extends
UniqueConstraintError, ForeignKeyConstraintError	UniqueViolationError, ForeignKeyViolationError, … — Errors
sequelize.query(sql, { replacements })	sql`…${param}`.execute(db.qb) — Raw SQL
sequelize.sync({ alter: true })	none on purpose — every change is a reviewed migration
sequelize-cli migration:generate (empty skeleton)	kick db generate <name> — diffs your schema and writes the SQL — Migrations
db:migrate / db:migrate:undo	kick db migrate latest / kick db migrate down (one) or rollback (last batch)
seeders (db:seed)	kick db seed — files in db/seeds, run in name order; nothing is tracked, so make them re-runnable

Side by side

Define a model

// Sequelize
class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  declare id: CreationOptional<number>
  declare email: string
  declare name: string
  declare createdAt: CreationOptional<Date>

  get domain() {
    return this.email.split('@')[1]
  }
}
User.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true,
      validate: { isEmail: true },
    },
    name: { type: DataTypes.TEXT, allowNull: false },
    createdAt: DataTypes.DATE,
  },
  { sequelize, tableName: 'users', updatedAt: false },
)

class Post extends Model {}
Post.init(
  {
    title: { type: DataTypes.STRING(200), allowNull: false },
    publishedAt: DataTypes.DATE,
  },
  { sequelize, tableName: 'posts', timestamps: false },
)

User.hasMany(Post, { foreignKey: 'authorId', as: 'posts', onDelete: 'CASCADE' })
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' })

// kick/db — src/db/schema.ts
import { TableBase, integer, relations, serial, text, timestamp, varchar } from '@forinda/kickjs-db'

export class User extends TableBase(
  'users',
  {
    id: serial().primaryKey(),
    email: varchar(255).notNull().unique(),
    name: text().notNull(),
    createdAt: timestamp().notNull().defaultNow(),
  },
  { rules: { email: { format: 'email' } } },
) {
  get domain() {
    return this.email.split('@')[1]
  }
}

export class Post extends TableBase('posts', {
  id: serial().primaryKey(),
  authorId: integer()
    .notNull()
    .references(() => User.table.id, { onDelete: 'cascade' }),
  title: varchar(200).notNull(),
  publishedAt: timestamp(),
}) {}

export const userRelations = relations(User.table, ({ many }) => ({
  posts: many(Post.table),
}))

export const postRelations = relations(Post.table, ({ one }) => ({
  author: one(User.table, { fields: [Post.table.authorId], references: [User.table.id] }),
}))

The attributes are declared once and the TypeScript type comes from them — no declare fields restating each column. Associations become two things: the foreign-key column on the table, and a relations() entry naming it for reads.

Find and write

// Sequelize
const user = await User.findOne({ where: { email: 'ada@example.com' } })
const ada = await User.create({ email: 'ada@example.com', name: 'Ada' })
await User.update({ name: 'Ada L.' }, { where: { id: ada.id } })
await User.destroy({ where: { id: ada.id } })

// kick/db
const row = await db
  .selectFrom('users')
  .selectAll()
  .where('email', '=', 'ada@example.com')
  .executeTakeFirst()
const user = row ? User.from(row) : null // only when you want `user.domain`

const ada = await db
  .insertInto('users')
  .values({ email: 'ada@example.com', name: 'Ada' })
  .returningAll()
  .executeTakeFirstOrThrow()
await db.updateTable('users').set({ name: 'Ada L.' }).where('id', '=', ada.id).execute()
await db.deleteFrom('users').where('id', '=', ada.id).execute()

A row isn't an instance you mutate and save(): to change it, run an update. If you liked User.findByEmail() as a static, write it once in a repository — a factory that takes the client and returns the methods your services call.

Eager loading with include

// Sequelize
const user = await User.findOne({
  where: { id: 1 },
  include: [
    { model: Post, as: 'posts', where: { publishedAt: { [Op.ne]: null } }, required: false },
  ],
  order: [[{ model: Post, as: 'posts' }, 'publishedAt', 'ASC']],
})

// kick/db
const user = await db.query.users.findFirst({
  where: (u, eb) => eb('id', '=', 1),
  with: {
    posts: {
      where: (_p, eb) => eb('publishedAt', 'is not', null),
      orderBy: (_p, eb) => eb.ref('publishedAt'),
      limit: 5,
    },
  },
})
// user.posts: Post[] — an empty array, never a dropped parent

const posts = await db.query.posts.findMany({ with: { author: true } })
// posts[0].author: User | null

with compiles to one query, and a filtered relation never removes the parent row — there is no required: true inner-join surprise, and limit inside a relation works without separate: true. orderBy sorts ascending; wrap the column in desc() (from @forinda/kickjs-db) for descending: orderBy: (_p, eb) => desc(eb.ref('publishedAt')). db.query is read-only; writes go through insertInto / updateTable / deleteFrom.

Op operators

// Sequelize
await User.findAll({
  where: {
    createdAt: { [Op.gte]: since },
    id: { [Op.in]: [1, 2, 3] },
    name: { [Op.like]: 'Ada%' },
    [Op.or]: [{ name: 'Ada L.' }, { email: { [Op.like]: '%@example.org' } }],
  },
  order: [['createdAt', 'DESC']],
  limit: 20,
  offset: 0,
})

// kick/db
await db
  .selectFrom('users')
  .selectAll()
  .where('createdAt', '>=', since)
  .where('id', 'in', [1, 2, 3])
  .where('name', 'like', 'Ada%')
  .where((eb) => eb.or([eb('name', '=', 'Ada L.'), eb('email', 'like', '%@example.org')]))
  .orderBy('createdAt', 'desc')
  .limit(20)
  .offset(0)
  .execute()

await db.selectFrom('posts').select('title').where('publishedAt', 'is', null).execute()

Chained where calls are AND; eb.or([...]) and eb.and([...]) group. Column names and value types are checked against the schema. A user-supplied LIKE pattern needs escaping; see Searching with LIKE. For filters that are only sometimes present, $if keeps the chain flat — Optional filters.

Transactions

// Sequelize — every call needs { transaction: t }, unless you set up CLS
await sequelize.transaction(async (t) => {
  const user = await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t })
  await Post.create({ authorId: user.id, title: 'First' }, { transaction: t })
  t.afterCommit(() => mailer.sendWelcome(user.email))
})

// kick/db — the plain client joins the transaction its caller opened
await db.transaction(async () => {
  const user = await db
    .insertInto('users')
    .values({ email: 'grace@example.com', name: 'Grace' })
    .returningAll()
    .executeTakeFirstOrThrow()
  await db.insertInto('posts').values({ authorId: user.id, title: 'First' }).execute()
  await db.afterCommit(() => mailer.sendWelcome(user.email))
})

What Sequelize.useCLS() gives you after installing cls-hooked and wiring a namespace is the default here: transactions follow the call chain through AsyncLocalStorage, so repositories and services holding the injected client take part without being handed anything. Forget { transaction: t } in Sequelize and a write quietly escapes the transaction; there's no option to forget in kick/db.

Errors and validation

// Sequelize
try {
  await User.create({ email, name })
} catch (err) {
  if (err instanceof UniqueConstraintError) {
    // duplicate email
  }
}

// kick/db
import { UniqueViolationError } from '@forinda/kickjs-db'
import { insertSchema } from '@forinda/kickjs-db/schema'

try {
  await db.insertInto('users').values({ email, name }).execute()
} catch (err) {
  if (err instanceof UniqueViolationError && err.columns.includes('email')) {
    // duplicate email — same class on Postgres, MySQL and SQLite
  }
}

// The model's `validate` moves to request validation derived from the table.
export const createUser = insertSchema(User.table, { omit: ['id', 'createdAt'] })
createUser.safeParse({ email: 'nope', name: 'N' }) // fails: not an email

Sequelize validates inside create; kick/db validates at the edge, before the handler runs. Pass createUser as a route's body schema and the same rules reject bad requests with a 422 and document them in OpenAPI. The database still has the last word through NOT NULL, UNIQUE and check() constraints. Left unhandled in a KickJS route, a UniqueViolationError answers 409.

What kick/db doesn't have (yet)

• Active-record instances. No save(), reload(), increment() or getPosts() on a row. Rows are plain data; writes are statements. An atomic counter is one update — Increment a counter.
• Hooks. No beforeCreate / afterUpdate. Put the logic in the service that writes, use afterCommit for side effects, a customType codec to transform a value on write and read, or query events to observe every statement.
• Scopes. No defaultScope applied behind your back. Name the query in a repository function instead.
• sync(). Deliberately absent — schema changes ship as migrations someone has read.

Moving an existing Sequelize app

1. Point kick db introspect at the database to get a schema file, and baseline the migration history so kick/db sees the current schema as applied — Adopting on an Existing DB. Sequelize's SequelizeMeta table and kick/db's kick_migrations don't interact.
2. Check the timestamp columns. Sequelize names them createdAt / updatedAt, or created_at with underscored: true. With underscored, set casing: 'snake_case' (Column names) so the keys stay camelCase as they were.
3. Convert models to tables one at a time. Keep instance-method code by moving it into a TableBase subclass and calling X.from(row) where you need it; move hooks and scopes into the services and repositories that used them.
4. Run both side by side: keep the Sequelize instance for unported modules and inject the kick/db client into new ones. Behind repositories, a service doesn't care which one answers.
5. Move schema changes to kick db generate once the first table is ported, so there is one source of migrations — and you stop writing migration bodies by hand.

Related

• How kick/db Works — schema, snapshot, diff, migrations, typed client
• Testing — an in-memory database per test file, a rolled-back transaction per test (instead of sync({ force: true }))
• Queries, Transactions, Errors