@notty/database

Database abstraction layer for Notty with multi-database support via Drizzle ORM.

Features

• 🎯 Database Agnostic: One schema works with PostgreSQL, MySQL, and SQLite
• 🔄 Schema-First: Define content types in JSON, convert to Drizzle tables
• 🛡️ Type Safe: Full TypeScript support with Drizzle ORM
• 📦 Modular: Database adapters as separate modules

Architecture

NottySchema (JSON)
    ↓
SchemaAdapter (postgres/mysql/sqlite)
    ↓
Drizzle ORM Table
    ↓
Database (PostgreSQL/MySQL/SQLite)

Usage

Define a Schema

import type { NottySchema } from '@notty/types';

const articleSchema: NottySchema = {
  kind: 'collectionType',
  info: {
    singularName: 'article',
    pluralName: 'articles',
    displayName: 'Article'
  },
  options: {
    draftAndPublish: true,
    timestamps: true
  },
  attributes: {
    title: {
      type: 'string',
      required: true,
      maxLength: 255
    },
    content: {
      type: 'richtext',
      required: true
    },
    publishedAt: {
      type: 'datetime'
    }
  }
};

Convert to Database Table

import { SchemaAdapterFactory } from '@notty/database';

// PostgreSQL
const pgAdapter = SchemaAdapterFactory.create('postgres');
const pgTable = pgAdapter.toTable(articleSchema);

// MySQL
const mysqlAdapter = SchemaAdapterFactory.create('mysql');
const mysqlTable = mysqlAdapter.toTable(articleSchema);

// SQLite
const sqliteAdapter = SchemaAdapterFactory.create('sqlite');
const sqliteTable = sqliteAdapter.toTable(articleSchema);

Save/Load Schemas

import { SchemaManager } from '@notty/database';

const manager = new SchemaManager('./schemas');

// Save
manager.saveSchema(articleSchema);

// Load
const schema = manager.loadSchema('articles');

// List all
const all = manager.listSchemas();

// Delete
manager.deleteSchema('articles');

Development

Install Dependencies

pnpm install

Build

pnpm build

Development Mode

pnpm dev  # Watch mode with auto-rebuild

Testing

# Run all tests
pnpm test

# Watch mode
pnpm test:watch

# Coverage report
pnpm test:coverage

Type Checking

pnpm type-check

Testing Strategy

Unit Tests

Located in src/adapters/__tests__/*.test.ts

Test individual adapters:

• Schema to table conversion
• Field type mapping
• Constraints (required, unique, default)
• System fields (id, timestamps)

pnpm test mysql  # Test specific adapter

Integration Tests

Located in src/adapters/__tests__/integration.test.ts

Test real database operations using in-memory SQLite:

• Table creation
• CRUD operations
• Constraint validation
• Data type handling

pnpm test integration

Database Support

| Database | Status | Adapter | |----------|--------|---------| | PostgreSQL | ✅ Ready | PostgreSQLSchemaAdapter | | MySQL | ✅ Ready | MySQLSchemaAdapter | | SQLite | ✅ Ready | SQLiteSchemaAdapter |

Field Types

| Notty Type | PostgreSQL | MySQL | SQLite | |-----------|-----------|-------|--------| | string | VARCHAR | VARCHAR | TEXT (with length) | | text | TEXT | TEXT | TEXT | | richtext | TEXT | TEXT | TEXT | | integer | INTEGER | INT | INTEGER | | bigint | BIGINT | BIGINT | INTEGER (64-bit) | | decimal | DECIMAL | DECIMAL | REAL | | boolean | BOOLEAN | BOOLEAN | INTEGER (0/1) | | datetime | TIMESTAMP | TIMESTAMP | TEXT (ISO 8601) | | json | JSONB | JSON | TEXT (JSON mode) |

Schema Options

• timestamps: true - Adds createdAt and updatedAt fields
• draftAndPublish: true - Adds publishedAt field for content drafts
• softDelete: true - Adds deletedAt for soft deletion (coming soon)

License

MIT

Part of Notty CMS