NestJS Airtable SDK

This package provides a seamless integration between NestJS and Airtable, allowing you to easily interact with Airtable databases in your NestJS applications.

🚀 Benefits of the NestJS Airtable Integration SDK

This powerful SDK offers game-changing advantages over traditional Airtable integration methods:

1. ✅ Reduced Code Duplication:

   • Say goodbye to multiple axios calls! Centralize all your Airtable interactions.
   • Enjoy a consistent interface for all operations, slashing boilerplate code.

2. ✅ Entity Abstraction:

   • Define Airtable tables as sleek TypeScript classes with decorators.
   • Benefit from rock-solid type safety and improved code organization.

3. ✅ Leaps Ahead of Deprecated Airtable.js:

   • Embrace modern TypeScript support and seamless NestJS integration.
   • Implement offset-based pagination, solving old package limitations.

4. ✅ Entity Relations:

   • Effortlessly define and query relations between Airtable entities.
   • Fetch related data in a single, lightning-fast operation.

5. ✅ Advanced Caching:

   • Integrate with Redis or local cache for mind-blowing performance.
   • Slash latency from 200-600ms to a mere 3ms per cached entity!
   • Minimize Airtable API calls, keeping you safely under rate limits.

6. ✅ Backend-Centric Architecture:

   • Decouple frontend (Flutter, FlutterFlow, React, etc.) from direct Airtable calls for enhanced security.
   • Centralize complex business logic on the backend, boosting maintainability.

7. 🚧 Multi-Nested Relations (Coming Soon):

   • Support deep, nested relations for even the most complex data structures.

8. ✅ Typed Query Filters:

   • Introduce TypeORM-like typed query filters for unparalleled developer experience.

9. 🚧 Advanced Relationship Types (Coming Soon):

   • Support for One-to-One, One-to-Many, and Many-to-Many relationships.
   • Seamlessly model complex data structures mirroring traditional relational databases.

🌟 Harness the power of this SDK to create blazing-fast, rock-solid, and easily maintainable applications that interact with Airtable. Leverage the robust features of NestJS and TypeScript while taking your Airtable integration to the next level! 🚀

Table of Contents

1. Installation
2. Setup
3. Usage
   • Defining Entities
   • Module Configuration
   • Injecting and Using Repositories
4. API Reference
   • AirtableModule
   • AirEntity Decorator
   • Field Decorator
   • Repository Methods
5. Advanced Usage
   • Caching
   • Relation Handling
   • Query Options and WhereClauseBuilder
6. Error Handling and Retries
7. Uploading Files to Airtable
   • Usage Uploading
   • Parameters
8. Best Practices
9. Limitations

Installation

To install the package, run the following command in your NestJS project:

npm install @singulardesign/airtable

Setup

1. First, you need to set up your Airtable API key and Base ID. You can find these in your Airtable account settings.

2. Create a .env file in your project root and add the following:

AIRTABLE_API_KEY=your_api_key_here
AIRTABLE_BASE_ID=your_base_id_here

3. Make sure to add .env to your .gitignore file to keep your credentials secure.

Module Configuration

In your app.module.ts, import and configure the AirtableModule:

import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { AirtableModule } from 'airtable-singular';
import { User } from './entities/user.entity';
@Module({
    imports: [
    ConfigModule.forRoot({
      envFilePath: '.env.development',
      isGlobal: true, // this flag is important
      load: [airtableConfig]
    }),
    CacheModule.registerAsync<RedisClientOptions>({ // Redis is important if you need shared DB across multiple instances
      imports: [ConfigModule],
      isGlobal: true,
      useFactory: async (config: ConfigService) => {
      const store = await redisStore({
          socket: {
              host: config.get<string>('REDIS_HOST'),
              port: config.get<number>('REDIS_PORT'),
          },
      });
      return {
          store: store as unknown as CacheStore,
      };
      },
      inject: [ConfigService],
    }),
    AirtableModule.forRootAsync({
      inject: [ConfigService, CACHE_MANAGER],
      useFactory: (configService: ConfigService) => ({
        apiKey: configService.get<string>('AIRTABLE_API_KEY'),
        baseId: configService.get<string>('AIRTABLE_BASE_ID'),
      }),
    }),
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
}),
AirtableModule.forFeature([User]),
],
})
export class AppModule {}

Usage

Defining Entities

Define your Airtable tables as entities using the @AirEntity and @Field decorators:

@AirEntity('roles')
export class Role {
  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  id: string;

  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  name: string;
}


@AirEntity('users')
export class User {
  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  id: string;

  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  status: string;

  // name property should be exactly as column name set on Airtable (columns in snake case)
  @Field({ name: 'phone_number', type: FieldType.SINGLE_LINE_TEXT }) 
  phoneNumber: string;

  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  email: string;

  @Field({ type: FieldType.SINGLE_LINE_TEXT })
  name: string;

  @Field({ type: FieldType.MULTIPLE_RECORD_LINK })
  tasks: string[];

  @Field({ type: FieldType.MULTIPLE_RECORD_LINK, relatedEntity: Role })
  role: string[];

  @Field({ name: 'created_at', type: FieldType.CREATED_TIME })
  createdAt: string;

  @Field({ name: 'updated_at', type: FieldType.SINGLE_LINE_TEXT })
  updatedAt: string;
}

Injecting and Using Repositories

In your Module:

@Module({
  imports: [
    AirtableModule.forFeature([User]),
    // ... other imports
  ],
  providers: [UserService],
  // ... other module configuration
})
export class UserModule {}

In your services, inject the repository for your entity:

@Injectable()
export class UserService {
  constructor(
    @InjectAirRepository(User)
    private userRepository: AirRepository<User>
  ) {}

  async findAllUsers() {
    return this.userRepository.findAll();
  }

  async createUser(userData: Partial<User>) {
    return this.userRepository.create(userData);
  }

  async updateUser(id: string, userData: Partial<User>) {
    return this.userRepository.update(id, userData);
  }

  async deleteUser(id: string) {
    return this.userRepository.delete(id);
  }
}

API Reference

AirtableModule

The AirtableModule provides two static methods for configuration:

1. forRoot(options: AirtableModuleOptions): For synchronous configuration.
2. forRootAsync(options: AirtableModuleAsyncOptions): For asynchronous configuration, typically used with ConfigModule.

AirEntity Decorator

The @AirEntity(tableName: string) decorator is used to mark a class as an Airtable entity and specify the corresponding table name.

Field Decorator

The @Field(options: FieldOptions) decorator is used to mark class properties as Airtable fields. Options include:

• name: The field name in Airtable (if different from the property name)
• type: The FieldType of the Airtable field

Repository Methods

The Repository<T> interface provides the following methods:

• findAll(options?: QueryOptions<T>): Promise<QueryResult<T>>
• findOneById(id: string, options?: Partial<Record<Relations<T>, boolean>>): Promise<T>
• findOne(options?: SingleQueryOptions<T>): Promise<T>
• create(entity: Partial<T>): Promise<T>
• update(id: string, entity: Partial<T>): Promise<T>
• updateMany(entities: { id: string; fields: Partial<T> }[]): Promise<T[]>
• delete(id: string): Promise<void>

Advanced Usage

Caching

The integration includes built-in caching for improved performance. Cached results are automatically used when available, and the cache is updated with new data when fetched from Airtable.

Relation Handling

To handle related records, use the MULTIPLE_RECORD_LINK field type and specify relations in query options:

const user = await this.userRepository.findOneById('user_id', { tasks: true });

This will fetch the user and populate the tasks field with related task records.

Query Options and WhereClauseBuilder

The WhereClauseBuilder is a utility that helps construct query strings for filtering data in Airtable. It supports various operators to perform complex queries, including LIKE, FIND, and comparison operators.

Usage

To use the WhereClauseBuilder, define a where clause using the WhereCondition and WhereOperators types. Here's how you can use it in your code:

Examples

Basic Equality

To filter records where a field matches a specific value:

const tasks = await this.taskRepository.findAll({
  where: {
    price: 200, // Direct equality check
  },
});

Using LIKE for Pattern Matching

To filter records where a field matches a pattern:

const tasks = await this.taskRepository.findAll({
  where: {
    status: { LIKE: 'completed' }, // Matches any status containing "completed"
  },
});

Using FIND for Substring Search

To filter records where a field contains a specific substring:

const users = await this.userRepository.findAll({
  where: {
    role: { FIND: 'rece2sTN0kSRw1uTT' }, // Finds "admin Id" within the role field useful for Ids records linked
  },
});

Using Comparison Operators

To filter records based on numerical comparisons:

const tasks = await this.taskRepository.findAll({
  where: {
    price: { GT: 100 }, // Price greater than 100
  },
});

Combining Conditions with AND and OR

To combine multiple conditions:

const tasks = await this.taskRepository.findAll({
  where: {
    AND: [
      { status: { LIKE: 'completed' } },
      { price: { GT: 100 } },
    ],
  },
});

Date Comparisons

To filter records based on date comparisons:

• Same Day:

const events = await this.eventRepository.findAll({
  where: {
    eventDate: { IS_SAME_DAY: new Date('2023-10-15') }, // Events on October 15, 2023
  },
});

• Before a Specific Date:

const tasks = await this.taskRepository.findAll({
  where: {
    dueDate: { IS_BEFORE: new Date('2023-12-31') }, // Tasks due before December 31, 2023
  },
});

• After or on a Specific Date:

const tasks = await this.taskRepository.findAll({
  where: {
    startDate: { IS_AFTER_OR_SAME: new Date('2023-01-01') }, // Tasks starting on or after January 1, 2023
  },
});

• Between Two Dates:

To filter records that fall between two dates:

const tasks = await this.taskRepository.findAll({
  where: {
    AND: [
      { startDate: { IS_AFTER_OR_SAME: new Date('2023-01-01') } }, // Start date on or after January 1, 2023
      { endDate: { IS_BEFORE_OR_SAME: new Date('2023-12-31') } }, // End date on or before December 31, 2023
    ],
  },
});

Supported Operators

• LIKE, NOT_LIKE: Pattern matching for any sequence of characters.
• FIND: Checks if a substring exists within a field.
• LT, LTE, GT, GTE: Less than, less than or equal, greater than, greater than or equal.
• IN, NOT_IN: Checks if a value is in a list of values.
• IS_SAME_DAY, IS_SAME_MONTH, IS_SAME_YEAR: Date comparisons.
• IS_BEFORE, IS_AFTER, IS_BEFORE_OR_SAME, IS_AFTER_OR_SAME: Date comparisons with flexibility.

Implementation Details

The WhereClauseBuilder automatically determines whether to use FIND or direct equality based on the type of the field and the value. This makes it easy for developers to write queries without worrying about the underlying logic.

Conclusion

The WhereClauseBuilder provides a flexible and powerful way to construct queries for Airtable, supporting a wide range of operators and conditions. By using this utility, developers can easily filter and retrieve data based on complex criteria.

Error Handling and Retries

The integration includes a retry mechanism for API calls. If an operation fails, it will be retried up to 3 times with increasing delays between attempts.

Uploading Files to Airtable

The uploadFile method in the AirtableService allows you to upload files to a specific record and field in your Airtable base.

Usage Uploading

Inject AirtableService into your service or controller:

import { Injectable } from '@nestjs/common';
import { AirtableService } from './path/to/airtable.service';
import { Express } from 'express';

@Injectable()
export class YourService {
  constructor(private readonly airtableService: AirtableService) {}

  async uploadFileToAirtable(file: Express.Multer.File, recordId: string, fieldId: string, filename: string) {
    try {
      const response = await this.airtableService.uploadFile(file, recordId, fieldId, filename);
      console.log('File uploaded:', response);
    } catch (error) {
      console.error('Upload error:', error);
    }
  }
}

Parameters

• file: The file to upload (from Multer).
• recordId: The recordId of table (recXcojNoQYdUvTt6).
• fieldId: The name of column of the table (picture).
• filename: The desired filename (picture.png).

This version demonstrates how to inject AirtableService into another service using NestJS's dependency injection, and provides a method to upload a file to Airtable.

Best Practices

1. Always use environment variables for API keys and Base IDs.
2. Use the @InjectAirRepository decorator to inject repositories in your services.
3. Leverage the built-in caching mechanism for improved performance.
4. Use the QueryOptions interface to customize your queries, including filtering, sorting, and pagination.
5. When dealing with related records, use the relations option in your queries to fetch related data efficiently.

This documentation provides a comprehensive guide to using the NestJS Airtable Integration. For more detailed information on specific components, refer to the inline documentation in the source code.

Limitations

While the WhereClauseBuilder and query options provide powerful tools for filtering and querying data in Airtable, there are some limitations to be aware of:

Aggregations

Airtable's API does not natively support SQL-like aggregation functions such as AVG, SUM, COUNT, MIN, MAX, etc. This means that you cannot perform these operations directly within Airtable queries. Instead, you would need to:

• Fetch Data: Retrieve the relevant records from Airtable.
• Process Data: Perform the aggregation calculations in your application code after fetching the data.

Example: Summing Values

To calculate the sum of a field, you would need to fetch all relevant records and then sum the values in your application:

const tasks = await this.taskRepository.findAll({
  where: {
    // Add any necessary filters here
  },
});

const totalSum = tasks.reduce((sum, task) => sum + task.price, 0);
console.log(`Total Sum: ${totalSum}`);

Workarounds

• Rollup Fields: If you have linked records, you can use Airtable's rollup fields to perform some aggregations like SUM or AVERAGE on linked records. This requires setting up linked tables and configuring rollup fields manually in the Airtable UI.
• External Processing: For more complex aggregations, consider exporting data to a database or using a tool that supports SQL-like queries.