Security Best Practices

// Safe: Parameterized query (automatic)
const user = await userRepo
  .find()
  .where("email = ?", userInput)
  .execute(orm.client);

// Safe: Raw query with parameters - the value is bound, not pasted in
const result = await orm.rawQuery(
  "SELECT * FROM users WHERE email = ?",
  [userInput]
);

// Safe: Raw write - same parameter binding
await orm.rawExec(
  "UPDATE users SET locked = ? WHERE email = ?",
  [true, userInput]
);

// Dangerous: concatenating user input into the SQL string
// The ORM cannot protect a value that never reaches it as a parameter.
const result = await orm.rawQuery(
  "SELECT * FROM users WHERE email = '" + userInput + "'"
);

DATABASE_URL=postgresql://user:password@localhost:5432/myapp
REDIS_URL=redis://localhost:6379
ORM_ENCRYPTION_KEY=...

.env
.env.local
.env.*.local

const dbConfig: DBConfig = {
  type: DBType.Postgres,
  connectionString: process.env.DATABASE_URL!, // From environment
};

// Built-in validation
const User = defineModel({
  tableName: "users",
  columns: {
    email: {
      type: DataTypes.STRING,
      required: true,
      pattern: /^[^@]+@[^@]+\.[^@]+$/,
      customValidator: (val) => val.length <= 255 || "Email too long",
    },
    name: {
      type: DataTypes.STRING,
      minLength: 2,
      maxLength: 100,
    },
  },
});

// With zod (external)
import { z } from "zod";
const userSchema = z.object({
  email: z.string().email(),
  name: z.string().min(1).max(100),
});
const validated = userSchema.parse(input);
await userRepo.create(validated);

// Verify ownership before returning data
async function getPost(postId: string, userId: string) {
  const post = await postRepo.findOneBy({ id: postId, authorId: userId });
  if (!post) throw new Error("Not found or access denied");
  return post;
}

// Or use scopes
const Post = defineModel({
  tableName: "posts",
  columns: { /* ... */ },
  scopes: {
    ownedBy: (qb, userId: string) => qb.where("authorId = ?", userId),
  },
});

const userPosts = await postRepo.scope("ownedBy", currentUserId).execute(orm.client);

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true },
    ssn: { type: DataTypes.STRING, encrypted: true }, // stored as ciphertext
    email: { type: DataTypes.STRING, required: true },
  },
});

// Reads return plaintext: findOne, findBy, first, paginate and pluck all
// pass the row through the decrypting transform.
const user = await userRepo.findOne(id);
console.log(user.ssn); // the original value

# 64 hex characters (32 bytes), or a 32-byte utf8 string.
# Omit it entirely and the ORM generates one into .stabilize/encryption.key.
ORM_ENCRYPTION_KEY=9f2c1d7a4b8e35061c9a7d2f4e8b1a3c5d7f9b2e4a6c8d0f1b3e5a7c9d1f3b5e

import { hash, verify } from "@node-rs/argon2";

const hashedPassword = await hash(password);
await userRepo.create({ id: generateUUID(), email, password: hashedPassword });

// Verify on login
const isValid = await verify(user.password, inputPassword);

await orm.transaction(async (txClient) => {
  const sender = await accountRepo.findOne(fromId, {}, txClient);
  if (!sender || sender.balance < amount) {
    throw new Error("Insufficient funds");
  }

  await accountRepo.update(
    fromId,
    { balance: sender.balance - amount },
    txClient,
  );
  const receiver = await accountRepo.findOne(toId, {}, txClient);
  await accountRepo.update(
    toId,
    { balance: receiver.balance + amount },
    txClient,
  );

  // Returning commits. A throw anywhere above rolls the whole thing back.
});

import { Ratelimit } from "@upstash/ratelimit";
import { Redis } from "@upstash/redis";

const ratelimit = new Ratelimit({
  redis: Redis.fromEnv(),
  limiter: Ratelimit.slidingWindow(5, "1 m"), // 5 attempts per minute
});

async function login(email: string, password: string, ip: string) {
  const { success } = await ratelimit.limit(ip);
  if (!success) {
    throw new Error("Too many login attempts. Please try again later.");
  }

  // Proceed with login
}

import { defineModel, registerHooks } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  columns: { /* ... */ },
});

registerHooks(User, {
  afterCreate: async (user) => {
    await auditRepo.create({
      action: "USER_CREATED",
      userId: user.id,
      timestamp: new Date().toISOString(),
    });
  },
  afterUpdate: async (user) => {
    await auditRepo.create({
      action: "USER_UPDATED",
      userId: user.id,
      timestamp: new Date().toISOString(),
    });
  },
  afterDelete: async (user) => {
    await auditRepo.create({
      action: "USER_DELETED",
      userId: user.id,
      timestamp: new Date().toISOString(),
    });
  },
});

import { Stabilize, LogLevel, type LoggerConfig } from "stabilize-orm";

const loggerConfig: LoggerConfig = {
  level: LogLevel.Debug,
  filePath: "logs/stabilize.log",
  maxFileSize: 5 * 1024 * 1024, // rotate at 5MB
  maxFiles: 3,                  // keep 3 files
};

export const orm = new Stabilize(dbConfig, { enabled: false, ttl: 60 }, loggerConfig);

-- Read-only user for reporting
CREATE USER reporting_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

-- Application user: row data only
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Do not grant DROP, TRUNCATE or ALTER to the application user, and do not
-- let it connect as the database owner or a superuser.