Sequelize 6 associations and transactions on SQLite: working versions (tested 2026-10-15)

{
  "name": "sequelize-sqlite-associations",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "sequelize": "6.37.8",
    "sqlite3": "6.0.1"
  },
  "devDependencies": {
    "@types/node": "26.6.5",
    "tsx": "4.23.15"
  }
}

import { Sequelize, DataTypes, Model, type InferAttributes, type InferCreationAttributes, type CreationOptional } from "sequelize";

export class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  declare id: CreationOptional<number>;
  declare email: string;
}
export class Post extends Model<InferAttributes<Post>, InferCreationAttributes<Post>> {
  declare id: CreationOptional<number>;
  declare title: string;
  declare userId: number;
}

export async function createDb(storage = ":memory:") {
  const sequelize = new Sequelize({ dialect: "sqlite", storage, logging: false });
  User.init(
    { id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true }, email: { type: DataTypes.STRING, allowNull: false, unique: true } },
    { sequelize, tableName: "users" },
  );
  Post.init(
    { id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true }, title: { type: DataTypes.STRING, allowNull: false }, userId: { type: DataTypes.INTEGER, allowNull: false } },
    { sequelize, tableName: "posts" },
  );
  User.hasMany(Post, { foreignKey: "userId", as: "posts" });
  Post.belongsTo(User, { foreignKey: "userId", as: "author" });
  await sequelize.sync({ force: true });
  return sequelize;
}

export async function createAuthorWithPosts(email: string, titles: string[]) {
  return User.sequelize!.transaction(async (t) => {
    const user = await User.create({ email }, { transaction: t });
    await Post.bulkCreate(titles.map((title) => ({ title, userId: user.id })), { transaction: t });
    return user;
  });
}

export async function postsForEmail(email: string) {
  const user = await User.findOne({
    where: { email },
    include: [{ model: Post, as: "posts" }],
  });
  return user;
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { createAuthorWithPosts, createDb, postsForEmail, Post } from "../src/db.ts";

test("transaction creates user and posts with include", async () => {
  const db = await createDb();
  await createAuthorWithPosts("[email protected]", ["First", "Second"]);
  const user = await postsForEmail("[email protected]");
  assert.ok(user);
  assert.equal(user!.email, "[email protected]");
  const posts = (user as any).posts as Post[];
  assert.deepEqual(posts.map((p) => p.title).sort(), ["First", "Second"]);
  await db.close();
});

test("rollback leaves no rows when nested create fails", async () => {
  const db = await createDb();
  await createAuthorWithPosts("[email protected]", ["Ok"]);
  await assert.rejects(async () => {
    await Post.sequelize!.transaction(async (t) => {
      await Post.create({ title: "x", userId: 999 }, { transaction: t });
    });
  });
  assert.equal(await Post.count(), 1);
  await db.close();
});