Getting started

This page takes you from an empty directory to a working model with real
queries against it, in about ten minutes. Every step runs as written — SQLite
needs no server, so there is nothing to install beyond the package itself.

When you finish, you will know how to connect, define a model, create the
table, and read and write rows. The learning path at the
bottom says what to read next and why.

[!NOTE]
Every example below is complete. If a snippet does not run as written, that
is a bug in these docs — the SQL shown alongside each example is what prorm
actually generates, recorded on SQLite.

What you will build, and the calls that get you there:

By the end the project is two files:

my-app/
  package.json
  index.ts        # everything on this page, in order

1. Install

npm install ts-prorm-orm better-sqlite3

pnpm add ts-prorm-orm better-sqlite3

yarn add ts-prorm-orm better-sqlite3

bun add ts-prorm-orm better-sqlite3

Two packages, deliberately. ts-prorm-orm ships its own TypeScript types, so
there is no @types package to add — and it ships no database drivers, so
the second package is the one that actually talks to SQLite. Swap it for pg,
mysql2 or another driver to target a different engine — see
Dialects.

That split is why installing prorm is 3 packages rather than 779: it supports
77 SQL dialects and 105 other stores, and a project uses a handful. If you would
rather be asked than look them up:

which lists the databases, asks which package manager you use, and installs
exactly the drivers you tick. Connecting without one fails with the package
name and the command to install it.

[!TIP]
None of this needs a build step if you are working in a browser. One
script tag from https://prod-orm.io/cdn/prorm.min.js gives you the same
models and queries against SQLite in the page — see
In the browser.

2. Connect

import { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'sqlite',
  storage: ':memory:',   // a file path keeps the data; ':memory:' does not
});

await db.connect();

storage: ':memory:' gives you a throwaway database that disappears when the
process exits — ideal while you are learning. Point it at a file, or swap in
another dialect when you want it to persist:

import { Prorm } from 'ts-prorm-orm';

// npm install pg
const db = new Prorm({
  dialect: 'postgres',
  host: 'localhost',
  port: 5432,
  database: 'app',
  username: 'app',
  password: process.env.PGPASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

// npm install mysql2
const db = new Prorm({
  dialect: 'mysql',
  host: 'localhost',
  port: 3306,
  database: 'app',
  username: 'app',
  password: process.env.MYSQL_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

// npm install better-sqlite3
const db = new Prorm({ dialect: 'sqlite', storage: './app.db' });

import { Prorm } from 'ts-prorm-orm';

// npm install mssql
const db = new Prorm({
  dialect: 'mssql',
  host: 'localhost',
  port: 1433,
  database: 'app',
  username: 'sa',
  password: process.env.MSSQL_PASSWORD,
});

3. Define a model

A model maps a class-like definition to a table. Each attribute becomes a
column.

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id:    { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  name:  { type: DataTypes.STRING(100), allowNull: false },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  age:   { type: DataTypes.INTEGER() },
});

The model is named User (singular); the table is users (pluralised) unless
you set tableName. Types are covered in Data types.

4. Create the table

await db.sync();

That emits:

createdAt and updatedAt appear because timestamps are on by default; pass
{ timestamps: false } to the model to drop them.

[!NOTE]
On an open connection, define() already creates the table in the
background; sync() waits for that and creates anything still missing. That
is why, in a real app, you define every model (and its associations) first
and call connect() and sync() once at startup — see step 8.

[!WARNING]
sync({ force: true }) drops the table first. It is a convenience for
development and tests, not a migration tool. For anything whose data you care
about, use migrations.

5. Write a row

const ada = await User.create({
  name:  'Ada Lovelace',
  email: 'ada@example.com',
  age:   36,
});

console.log(ada.id);   // 1

The stamps are not in the statement: the columns' DEFAULT CURRENT_TIMESTAMP
fills them in.

6. Read rows

import { Op } from 'ts-prorm-orm';

// everyone
const all = await User.findAll();

// one, by primary key
const one = await User.findByPk(1);

// filtered, ordered, limited
const adults = await User.findAll({
  where: { age: { [Op.gte]: 18 } },
  order: [['name', 'ASC']],
  limit: 10,
});

The three reads become:

On SQL Server the limit moves to the front:
SELECT TOP(10) * FROM [users] WHERE [age] >= 18 ORDER BY [name] ASC;.

Op holds the comparison operators. A bare value means equality; anything
else — ranges, IN, LIKE, null checks — is an operator object. See
Query operators.

[!IMPORTANT]
order takes an array of [column, direction] pairs. A bare string like
order: 'name DESC' is pasted into the SQL as it is —
SELECT * FROM "users" ORDER BY name DESC — unquoted and unchecked. It works
for a literal you wrote, and is an injection hole for anything that came from
a request. Use the array form.

7. Update and delete

// on an instance you already hold
ada.age = 37;
await ada.save();

// or in one statement, without loading anything
await User.update({ age: 37 }, { where: { email: 'ada@example.com' } });

await User.destroy({ where: { id: 1 } });

save() writes the whole row back; the static update() writes only the
columns you name — the better choice when you know what changed.

8. Relate two models

const Post = db.define('Post', {
  id:     { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  title:  { type: DataTypes.STRING(200), allowNull: false },
  userId: { type: DataTypes.INTEGER() },   // the foreign key column
});

User.hasMany(Post);        // a user has many posts
Post.belongsTo(User);      // and each post belongs to one user

await db.sync();

const post = await Post.create({ title: 'On computing', userId: ada.id });

// fetch users with their posts: one query for users, one for all their posts
const withPosts = await User.findAll({ include: [Post] });
withPosts[0].posts; // [{ id: 1, title: 'On computing', userId: 1, … }]

hasMany puts the foreign key on the target (posts.userId);
belongsTo declares the same relationship from the other end so you can
traverse it both ways. Which side holds the key decides which table is written
— see Associations.

[!WARNING]
Declare the userId column yourself. Because Post is defined on an open
connection, its table is created at once — before hasMany runs — so the
association cannot add the column, and without it the INSERT fails with
table posts has no column named userId. For the same reason there is no
REFERENCES clause here. In an app, define every model and association
before connect(), and sync() creates the tables with their foreign keys.

The whole thing

import { Prorm, DataTypes, Op } from 'ts-prorm-orm';

const db = new Prorm({ dialect: 'sqlite', storage: ':memory:' });
await db.connect();

const User = db.define('User', {
  id:    { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  name:  { type: DataTypes.STRING(100), allowNull: false },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  age:   { type: DataTypes.INTEGER() },
});

await db.sync({ force: true });

await User.bulkCreate([
  { name: 'Ada Lovelace', email: 'ada@example.com', age: 36 },
  { name: 'Alan Turing',  email: 'alan@example.com', age: 41 },
]);

const adults = await User.findAll({
  where: { age: { [Op.gte]: 40 } },
  order: [['name', 'ASC']],
});

console.log(adults.map((u) => u.name));   // [ 'Alan Turing' ]

await db.close();

Where to go next

The documentation is ordered as a path, not an index. Read it in this order and
each page assumes only what came before it.

1. Models — Everything a model definition can express, beyond the four columns above.

2. Data types — Which type to reach for, and how each maps per engine.

3. Querying — The finders, and how one FindOptions becomes SQL.

4. Query operators — Comparisons, ranges, pattern matching, JSON access.

5. Associations — The four relationship shapes and which side owns the key.

6. Eager loading — Fetching related rows without the N+1 problem.

7. Validation — Rejecting bad data before it reaches the database.

8. Hooks — Running logic around the lifecycle.

9. Transactions — Making several writes succeed or fail together.

10. Migrations — Evolving a schema you cannot simply drop.

Prefer another style for the same journey? The
JavaScript, TypeScript and
decorators tracks cover it with runnable examples.

After those ten you can use the ORM for real work. Everything beyond them —
caching, replication,
streams, the
dialect pages — is reference material to reach for when
a specific need comes up, not reading you have to do first.

Overview — what the library covers

Running databases in Docker — when you outgrow SQLite

API reference — every exported symbol, from the source

1. Install#

2. Connect#

3. Define a model#

4. Create the table#

5. Write a row#

6. Read rows#

7. Update and delete#

8. Relate two models#

The whole thing#

Where to go next#

Related reading#