Models from an existing database

DATABASE_URL=postgres://app:secret@localhost:5432/app

$ prorm models pull --schema public --out src/models

$ prorm models pull --dialect sqlite --database ./shop.sqlite --out src/models
✓ Generated 4 model(s) from sqlite (catalog: sqlite):
  src/models/Authors.ts
  src/models/BookTags.ts
  src/models/Books.ts
  src/models/Tags.ts
  src/models/index.ts

src/
  models/
    Authors.ts    # timestamps: true, createdAt: 'created_at'
    BookTags.ts   # the join table
    Books.ts      # paranoid: true, the CHECK read back as an ENUM
    Tags.ts
    index.ts      # initModels(): every model plus the associations

export function initModels(prorm: Prorm) {
  const Authors = initAuthors(prorm);
  const BookTags = initBookTags(prorm);
  const Books = initBooks(prorm);
  const Tags = initTags(prorm);

  BookTags.belongsTo(Tags, { foreignKey: 'tag_id', as: 'tag' });
  BookTags.belongsTo(Books, { foreignKey: 'book_id', as: 'book' });
  Books.belongsTo(Authors, { foreignKey: 'author_id', as: 'author', onDelete: 'CASCADE' });
  Authors.hasMany(Books, { foreignKey: 'author_id', as: 'books', onDelete: 'CASCADE' });
  Tags.belongsToMany(Books, { through: { model: BookTags, otherKey: 'book_id' }, foreignKey: 'tag_id', as: 'books' });
  Books.belongsToMany(Tags, { through: { model: BookTags, otherKey: 'tag_id' }, foreignKey: 'book_id', as: 'tags' });

  return { Authors, BookTags, Books, Tags };
}

import { Prorm } from 'ts-prorm-orm';
import { initModels } from './models';

export const prorm = new Prorm({ dialect: 'sqlite', storage: './shop.sqlite' });
export const models = initModels(prorm); // before connect(): see "Using the models on an empty database"
await prorm.connect();

const { Authors, Books } = models;
const author = await Authors.findOne({ where: { email: 'ada@example.com' } });
const books = await Books.findAll({ where: { author_id: author?.get('id') } });

$ prorm models pull --dialect sqlite --database ./shop.sqlite --out src/models
✗ Refusing to overwrite 5 existing file(s) (pass --force to overwrite):
  /app/src/models/Authors.ts
  /app/src/models/BookTags.ts
  /app/src/models/Books.ts
  /app/src/models/Tags.ts
  /app/src/models/index.ts
$ echo $?
1

$ prorm models pull --dialect sqlite --database ./shop.sqlite --tables authors --out src/models --force
! authors.verified: kept native type BOOLEAN (DataTypes.BOOLEAN() would create INTEGER on sqlite).
✓ Generated 1 model(s) from sqlite (catalog: sqlite):
  src/models/Authors.ts
  src/models/index.ts

       email: {
         type: DataTypes.STRING(255),
         allowNull: false,
         unique: true,
       },
+      bio: {
+        type: DataTypes.TEXT(),
+      },
+      verified: {
+        type: 'boolean', // native: BOOLEAN — DataTypes.BOOLEAN() would create INTEGER on sqlite
+        allowNull: false,
+        defaultValue: false,
+      },
     },

$ prorm models pull --dialect sqlite --database ./shop.sqlite --tables nope --dry-run
! Table 'nope' does not exist, skipping.
! Table 'nope' does not exist.
! No tables found; nothing to generate.
$ echo $?
5

// src/models/Books.ts
import { DataTypes } from 'ts-prorm-orm';
import type { Prorm } from 'ts-prorm-orm';

export interface BooksAttributes {
  id: number;
  author_id: number;
  title: string;
  price?: number | string | null;
  deleted_at?: Date | null;
}

export let Books: ReturnType<Prorm['define']>;

export function initBooks(prorm: Prorm): ReturnType<Prorm['define']> {
  Books = prorm.define<BooksAttributes>(
    'Books',
    {
      id: { type: DataTypes.INTEGER(), primaryKey: true },
      author_id: { type: DataTypes.INTEGER(), allowNull: false },
      title: { type: DataTypes.STRING(200), allowNull: false },
      price: { type: DataTypes.DECIMAL(12, 4), defaultValue: 1.5, comment: 'Net, in EUR' },
    },
    {
      tableName: 'books',
      timestamps: false,
      paranoid: true,
      deletedAt: 'deleted_at',
      uniqueKeys: [{ fields: ['author_id', 'title'] }],
      indexes: [{ name: 'ix_books_title', fields: ['title'] }],
    },
  );
  return Books;
}

// src/models/index.ts
export { Authors, initAuthors } from './Authors';
export { Books, initBooks } from './Books';

export function initModels(prorm: Prorm) {
  const Authors = initAuthors(prorm);
  const Books = initBooks(prorm);

  Books.belongsTo(Authors, { foreignKey: 'author_id', as: 'author', onDelete: 'CASCADE' });
  Authors.hasMany(Books, { foreignKey: 'author_id', as: 'books', onDelete: 'CASCADE' });

  return { Authors, Books };
}

export interface TagsAttributes {
  id?: number;
  label: string;
}

export let Tags: ReturnType<Prorm['define']>;

export function initTags(prorm: Prorm): ReturnType<Prorm['define']> {
  Tags = prorm.define<TagsAttributes>(
    'Tags',
    {
      id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
      label: { type: DataTypes.STRING(40), allowNull: false, unique: true },
    },
    { tableName: 'tags', timestamps: false },
  );
  return Tags;
}

import { Model, DataTypes, Table, Attribute, PrimaryKey, AutoIncrement, NotNull, Unique } from 'ts-prorm-orm';
import type { CreationOptional, InferAttributes, InferCreationAttributes } from 'ts-prorm-orm';

@Table({
  tableName: 'tags',
  timestamps: false,
})
export class Tags extends Model<InferAttributes<Tags>, InferCreationAttributes<Tags>> {
  @Attribute(DataTypes.INTEGER())
  @PrimaryKey()
  @AutoIncrement()
  declare id: CreationOptional<number>;

  @Attribute(DataTypes.STRING(40))
  @NotNull()
  @Unique()
  declare label: string;
}

const attributes = {
  xmin_copy: {
    type: 'xid', // native: xid — no portable DataTypes equivalent
  },
};

Books.belongsToMany(Tags, {
  through: { model: BookTags, otherKey: 'tag_id' },
  foreignKey: 'book_id',
  as: 'tags',
});

import { Prorm } from 'ts-prorm-orm';
import { introspectSchema, generateModels, pullModels } from 'ts-prorm-orm/codegen';

const prorm = new Prorm({ dialect: 'postgres', url: process.env.DATABASE_URL });
await prorm.connect();

// 1. Read: a neutral SchemaModel.
const schema = await introspectSchema(prorm, { schema: 'public', tables: ['users', 'posts'] });
for (const table of schema.tables) {
  console.log(
    table.name,
    table.columns.map((c) => `${c.name}: ${c.nativeType}`),
  );
  if (!table.known.checks) console.log('  (CHECK constraints unknown on this engine)');
}

// 2. Generate: files in memory. Pass the dialect so types are checked against it.
const { files, warnings } = generateModels(schema, {
  style: 'define',
  lang: 'ts',
  dialect: prorm.getDialectInstance()!,
});

// Or both, plus writing (refuses to overwrite unless force: true):
const report = await pullModels(prorm, { out: 'src/models', style: 'class' });

import fs from 'node:fs';
import path from 'node:path';
import { Prorm } from 'ts-prorm-orm';
import { introspectSchema, generateModels } from 'ts-prorm-orm/codegen';

const OUT = 'src/models';

const prorm = new Prorm({ dialect: 'postgres', url: process.env.DATABASE_URL, logging: false });
await prorm.connect();
try {
  const schema = await introspectSchema(prorm, { schema: 'public' });
  const { files, warnings } = generateModels(schema, {
    style: 'define',
    lang: 'ts',
    dialect: prorm.getDialectInstance()!,
  });
  warnings.forEach((w) => console.warn(`! ${w}`));

  const stale = files.filter((f) => {
    const onDisk = path.join(OUT, f.path);
    return !fs.existsSync(onDisk) || fs.readFileSync(onDisk, 'utf8') !== f.contents;
  });
  if (stale.length) {
    console.error(`Models are out of date with the database: ${stale.map((f) => f.path).join(', ')}`);
    console.error('Run: prorm models pull --out src/models --force, and review the diff.');
    process.exitCode = 1;
  }
} finally {
  await prorm.close();
}

$ DATABASE_URL=postgres://ci:ci@localhost/app npx tsx scripts/check-models.ts
Models are out of date with the database: Authors.ts
Run: prorm models pull --out src/models --force, and review the diff.