Ultimate Guide to Using Trilogy for Effective Data Management with Practical API Examples

Trilogy is a powerful and flexible ORM (Object-Relational Mapper) library for Node.js that simplifies database interactions. This guide provides an introduction to Trilogy, followed by several useful API explanations with code snippets and a complete app example to help you get started.

Introduction to Trilogy

Trilogy is designed to interact with SQLite databases. It offers a straightforward API for CRUD (Create, Read, Update, Delete) operations and supports advanced features like relationships and validation. Let’s explore some of the key APIs of Trilogy.

APIs and Code Snippets

Setting Up Trilogy

  const { connect } = require('trilogy')
  const db = connect('database.sqlite')

Creating a Table

  await db.model('users', {
    id: 'increments',
    name: 'string',
    email: 'string',
    created_at: 'date',
  })

Inserting Data

  await db.users.create({
    name: 'John Doe',
    email: 'john.doe@example.com',
    created_at: new Date(),
  })

Fetching Data

  const user = await db.users.findOne({ name: 'John Doe' })
  console.log(user)

Updating Data

  await db.users.update({ name: 'John Doe' }, { email: 'john.new@example.com' })

Deleting Data

  await db.users.remove({ name: 'John Doe' })

Validations

  const User = await db.model('users', {
    id: 'increments',
    name: 'string',
    email: 'string',
    created_at: 'date'
  }, {
    validator: {
      email: value => {
        return value.includes('@')
      }
    }
  })
  await User.create({
    name: 'Jane Doe',
    email: 'jane.doe@example'
  })  // This will fail validation

Relationships

  const User = db.model('users', {
    id: 'increments',
    name: 'string'
  })
  const Post = db.model('posts', {
    id: 'increments',
    user_id: 'integer',
    content: 'string'
  })

  User.hasMany('posts', { local: 'id', foreign: 'user_id' })
  Post.belongsTo('user', { local: 'user_id', foreign: 'id' })

Complete App Example

Here is a basic example of a Node.js application using Trilogy to manage a simple user-post relationship.

  const { connect } = require('trilogy')
  const db = connect('blog.sqlite')

  const User = await db.model('users', {
    id: 'increments',
    name: 'string'
  })
  const Post = await db.model('posts', {
    id: 'increments',
    user_id: 'integer',
    content: 'string'
  })

  User.hasMany('posts', { local: 'id', foreign: 'user_id' })
  Post.belongsTo('user', { local: 'user_id', foreign: 'id' })

  // Creating a user
  const user = await User.create({
    name: 'Alice'
  })

  // Creating a post
  const post = await Post.create({
    user_id: user.id,
    content: 'Hello, world!'
  })

  // Fetching user's posts
  const posts = await user.getPosts()
  console.log(posts)