Schemaless SQLite ORM

A type-safe, schemaless ORM for SQLite that automatically creates tables and columns on-the-fly. Built with TypeScript and designed for rapid development.

Features

• 🚀 Zero Configuration - No schema definitions required, tables and columns are created automatically
• 🔒 Type-Safe - Full TypeScript support with generic types
• 🆔 Auto-Generated IDs - UUID v4 primary keys automatically injected
• ⏰ Timestamp Tracking - createdAt, updatedAt, and deletedAt fields managed automatically
• 🗑️ Soft Delete - Records are soft-deleted by default, with restore capability
• 📦 Special Types - Native support for boolean, Date, and JSON types
• 🔍 Powerful Queries - Filter, order, paginate, and select specific fields
• 🛠️ Column Management - Rename and delete columns at runtime
• ⚡ Dynamic Schema - Add new fields to existing tables without migrations

Installation

npm install schemaless-sqlite

Quick Start

import { SqliteOrm } from 'schemaless-sqlite';

// Initialize the ORM
const db = new SqliteOrm('mydb.sqlite');

// Create a record - table is created automatically!
const user = await db.create('users', {
  name: 'Alice',
  email: '[email protected]',
  age: 30,
  isActive: true,
  metadata: { role: 'admin' },
  lastLogin: new Date(),
});

console.log(user.data?.id); // Auto-generated UUID
console.log(user.data?.createdAt); // Auto-generated timestamp

// Find records
const users = await db.find('users', {
  filter: { isActive: { eq: true } },
  orderBy: { age: 'desc' },
  limit: 10,
});

// Update a record
await db.update('users', userId, {
  email: '[email protected]',
});

// Soft delete (default)
await db.delete('users', userId);

// Close the database
db.close();

API Reference

Creating Records

create(tableName, data)

Creates a new record in the specified table. If the table doesn't exist, it will be created automatically.

const result = await db.create('posts', {
  title: 'Hello World',
  content: 'My first post',
  published: true,
  tags: ['intro', 'welcome'],
  publishedAt: new Date(),
});

if (result.success) {
  console.log('Created:', result.data?.id);
} else {
  console.error('Error:', result.error);
}

Auto-Injected Fields:

• id - UUID v4 string
• createdAt - Creation timestamp
• updatedAt - Last update timestamp
• deletedAt - Soft delete timestamp (null by default)

Querying Records

find(tableName, options?)

Find multiple records with optional filtering, ordering, and pagination.

const users = await db.find('users', {
  filter: {
    age: { gte: 18 },
    isActive: { eq: true },
  },
  orderBy: { createdAt: 'desc' },
  limit: 20,
  offset: 0,
  select: ['name', 'email', 'age'],
  includeSoftDeleted: false, // Exclude soft-deleted records (default)
});

console.log(users.data); // Array of matching records

Filter Operators:

• eq - Equal to
• ne - Not equal to
• gt - Greater than
• gte - Greater than or equal to
• lt - Less than
• lte - Less than or equal to

Query Options:

• filter - Filter conditions (optional)
• orderBy - Sort by field(s) with 'asc' or 'desc' (optional)
• limit - Maximum number of records (optional)
• offset - Number of records to skip (optional)
• select - Array of field names to return (optional)
• includeSoftDeleted - Include soft-deleted records (default: false)

findOne(tableName, options)

Find a single record. Returns the first match or null.

const user = await db.findOne('users', {
  filter: { email: { eq: '[email protected]' } },
});

if (user.data) {
  console.log('Found:', user.data.name);
}

findById(tableName, id)

Find a record by its ID.

const user = await db.findById('users', userId);

if (user.data) {
  console.log('Found:', user.data.name);
}

Updating Records

update(tableName, id, data)

Update an existing record. You can also add new fields dynamically.

// Update existing fields
await db.update('users', userId, {
  email: '[email protected]',
  age: 31,
});

// Add new fields dynamically
await db.update('users', userId, {
  phoneNumber: '555-1234', // New field, column created automatically
});

Deleting Records

delete(tableName, id)

Soft delete a record (sets deletedAt timestamp). Soft-deleted records are excluded from queries by default.

const result = await db.delete('users', userId);

if (result.success) {
  console.log('User soft deleted');
}

restore(tableName, id)

Restore a soft-deleted record.

const result = await db.restore('users', userId);

if (result.success) {
  console.log('User restored');
}

hardDelete(tableName, id)

Permanently delete a record from the database.

const result = await db.hardDelete('users', userId);

if (result.success) {
  console.log('User permanently deleted');
}

Column Management

column.rename(tableName, oldName, newName)

Rename a column in a table.

const result = await db.column.rename('users', 'age', 'yearsOld');

if (result.success) {
  console.log('Column renamed');
}

column.delete(tableName, columnName)

Delete a column from a table.

const result = await db.column.delete('users', 'oldField');

if (result.success) {
  console.log('Column deleted');
}

Type Safety

Define your schema for better type inference:

interface User {
  name: string;
  email: string;
  age: number;
  isActive: boolean;
}

interface Post {
  title: string;
  content: string;
  authorId: string;
  published: boolean;
}

interface Schema {
  users: User;
  posts: Post;
}

// Initialize with schema
const db = new SqliteOrm<Schema>('mydb.sqlite');

// Now you get full type checking
const user = await db.create('users', {
  name: 'Alice',
  email: '[email protected]',
  age: 30,
  isActive: true,
});

// TypeScript will enforce the correct types
const users = await db.find('users', {
  filter: {
    age: { gte: 18 }, // Type-safe!
  },
});

Special Type Handling

Boolean

Booleans are stored as integers (0/1) in SQLite and automatically converted back to boolean values.

await db.create('users', {
  isActive: true, // Stored as 1
  isVerified: false, // Stored as 0
});

const user = await db.findById('users', userId);
console.log(typeof user.data?.isActive); // "boolean"

Date

Date objects are stored as ISO 8601 strings and automatically converted back to Date objects.

await db.create('events', {
  name: 'Launch Party',
  date: new Date('2026-06-01'),
});

const event = await db.findById('events', eventId);
console.log(event.data?.date instanceof Date); // true

JSON (Objects and Arrays)

Objects and arrays are automatically serialized to JSON strings and deserialized back.

await db.create('users', {
  name: 'Alice',
  settings: {
    theme: 'dark',
    notifications: true,
  },
  tags: ['admin', 'developer'],
});

const user = await db.findById('users', userId);
console.log(user.data?.settings.theme); // "dark"
console.log(Array.isArray(user.data?.tags)); // true

Response Format

All mutation operations return a standardized response:

interface OrmMutationResponse<T> {
  success: boolean;
  data: T | null;
  error: { message: string } | null;
}

Query operations return:

interface OrmQueryResponse<T> {
  data: T[];
  error: { message: string } | null;
}

Complete Example

import { SqliteOrm } from 'schemaless-sqlite';

interface User {
  name: string;
  email: string;
  age: number;
  isActive: boolean;
  metadata: { role: string };
}

interface Schema {
  users: User;
}

async function main() {
  const db = new SqliteOrm<Schema>('app.db');

  // Create users
  const alice = await db.create('users', {
    name: 'Alice',
    email: '[email protected]',
    age: 30,
    isActive: true,
    metadata: { role: 'admin' },
  });

  const bob = await db.create('users', {
    name: 'Bob',
    email: '[email protected]',
    age: 25,
    isActive: true,
    metadata: { role: 'user' },
  });

  // Query users
  const activeUsers = await db.find('users', {
    filter: { isActive: { eq: true } },
    orderBy: { age: 'desc' },
  });

  console.log('Active users:', activeUsers.data?.length);

  // Update user
  if (alice.data) {
    await db.update('users', alice.data.id, {
      age: 31,
      phoneNumber: '555-1234', // New field added dynamically
    });
  }

  // Soft delete
  if (bob.data) {
    await db.delete('users', bob.data.id);
  }

  // Find without soft-deleted
  const remaining = await db.find('users', {});
  console.log('Remaining users:', remaining.data?.length); // 1

  // Find including soft-deleted
  const all = await db.find('users', {
    includeSoftDeleted: true,
  });
  console.log('All users (including deleted):', all.data?.length); // 2

  // Restore soft-deleted user
  if (bob.data) {
    await db.restore('users', bob.data.id);
  }

  // Column operations
  await db.column.rename('users', 'age', 'yearsOld');

  db.close();
}

main();

Error Handling

All operations include error information in the response:

const result = await db.create('users', { name: 'Alice' });

if (!result.success) {
  console.error('Operation failed:', result.error?.message);
}

Errors are also logged to the console with the [ORM Error] prefix for debugging.

Best Practices

1. Always check success or data before using the result
2. Use TypeScript interfaces for better type safety
3. Close the database when you're done with db.close()
4. Use soft delete by default - it's safer and allows recovery
5. Leverage dynamic schema - add fields as needed without migrations
6. Use filters efficiently - the ORM automatically excludes soft-deleted records

License

ISC

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Repository

https://github.com/eralvarez/schemaless-sqlite