• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

teableio / teable / 19137837366

06 Nov 2025 01:49PM UTC coverage: 74.89% (-0.03%) from 74.917%
19137837366

push

github

web-flow
Merge pull request #2097 from teableio/fix/link-date-formattin

fix: fix link date formatting

10659 of 11524 branches covered (92.49%)

60 of 92 new or added lines in 3 files covered. (65.22%)

12 existing lines in 3 files now uncovered.

52390 of 69956 relevant lines covered (74.89%)

4287.7 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

72.24
/apps/nestjs-backend/src/features/base-sql-executor/base-sql-executor.service.ts
1
import { BadRequestException, Injectable, Logger } from '@nestjs/common';
1✔
2
import { ConfigService } from '@nestjs/config';
3
import type { IDsn } from '@teable/core';
4
import { DriverClient, parseDsn } from '@teable/core';
5
import { Prisma, PrismaService, PrismaClient } from '@teable/db-main-prisma';
6
import { Knex } from 'knex';
7
import { InjectModel } from 'nest-knexjs';
8
import { BASE_READ_ONLY_ROLE_PREFIX, BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME } from './const';
9
import { checkTableAccess, validateRoleOperations } from './utils';
10

11
@Injectable()
12
export class BaseSqlExecutorService {
1✔
13
  private db?: PrismaClient;
91✔
14
  private readonly dsn: IDsn;
91✔
15
  readonly driver: DriverClient;
91✔
16
  private hasPgReadAllDataRole?: boolean;
91✔
17
  private readonly logger = new Logger(BaseSqlExecutorService.name);
91✔
18

19
  constructor(
91✔
20
    private readonly prismaService: PrismaService,
91✔
21
    private readonly configService: ConfigService,
91✔
22
    @InjectModel('CUSTOM_KNEX') private readonly knex: Knex
91✔
23
  ) {
91✔
24
    this.dsn = parseDsn(this.getDatabaseUrl());
91✔
25
    this.driver = this.dsn.driver as DriverClient;
91✔
26
  }
91✔
27

28
  private getDatabaseUrl() {
91✔
29
    return (
91✔
30
      this.configService.get<string>('PRISMA_DATABASE_URL_FOR_SQL_EXECUTOR') ||
91✔
31
      this.configService.getOrThrow<string>('PRISMA_DATABASE_URL')
91✔
32
    );
33
  }
91✔
34

35
  private getDisablePreSqlExecutorCheck() {
91✔
36
    return this.configService.get<string>('DISABLE_PRE_SQL_EXECUTOR_CHECK') === 'true';
91✔
37
  }
91✔
38

39
  private async getReadOnlyDatabaseConnectionConfig(): Promise<string | undefined> {
91✔
40
    if (this.driver === DriverClient.Sqlite) {
91!
41
      return;
×
42
    }
×
43
    if (!this.hasPgReadAllDataRole) {
91!
44
      return;
×
45
    }
×
46
    const isExistReadOnlyRole = await this.roleExits(BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME);
91✔
47
    if (!isExistReadOnlyRole) {
91✔
48
      await this.prismaService.$tx(async (prisma) => {
1✔
49
        try {
1✔
50
          await prisma.$executeRawUnsafe(
1✔
51
            this.knex
1✔
52
              .raw(
1✔
53
                `CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION`,
1✔
54
                [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME, this.dsn.pass]
1✔
55
              )
56
              .toQuery()
1✔
57
          );
58
          await prisma.$executeRawUnsafe(
1✔
59
            this.knex
1✔
60
              .raw(`GRANT pg_read_all_data TO ??`, [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME])
1✔
61
              .toQuery()
1✔
62
          );
63
        } catch (error) {
1!
UNCOV
64
          if (
×
UNCOV
65
            error instanceof Prisma.PrismaClientKnownRequestError &&
×
UNCOV
66
            (error?.meta?.code === '42710' || error?.meta?.code === '23505')
×
UNCOV
67
          ) {
×
UNCOV
68
            this.logger.warn(
×
UNCOV
69
              `read only role ${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME} already exists, error code: ${error?.meta?.code}`
×
70
            );
UNCOV
71
            return;
×
UNCOV
72
          }
×
73
          throw error;
×
74
        }
×
75
      });
1✔
76
    }
1✔
77
    return `postgresql://${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME}:${this.dsn.pass}@${this.dsn.host}:${this.dsn.port}/${this.dsn.db}${
91✔
78
      this.dsn.params
91✔
79
        ? `?${Object.entries(this.dsn.params)
91✔
80
            .map(([key, value]) => `${key}=${value}`)
91✔
81
            .join('&')}`
91✔
82
        : ''
×
83
    }`;
91✔
84
  }
91✔
85

86
  async onModuleInit() {
91✔
87
    if (this.driver !== DriverClient.Pg) {
91!
88
      return;
×
89
    }
×
90
    if (this.getDisablePreSqlExecutorCheck()) {
91!
91
      return;
×
92
    }
×
93
    // if pg_read_all_data role not exist, no need to create read only role
91✔
94
    this.hasPgReadAllDataRole = await this.roleExits('pg_read_all_data');
91✔
95
    if (!this.hasPgReadAllDataRole) {
91!
96
      return;
×
97
    }
×
98
    this.db = await this.createConnection();
91✔
99
  }
91✔
100

101
  async onModuleDestroy() {
91✔
102
    await this.db?.$disconnect();
90✔
103
  }
90✔
104

105
  private async createConnection(): Promise<PrismaClient | undefined> {
91✔
106
    if (this.db) {
91!
107
      return this.db;
×
108
    }
×
109
    const connectionConfig = await this.getReadOnlyDatabaseConnectionConfig();
91✔
110
    if (!connectionConfig) {
91!
111
      return;
×
112
    }
×
113
    const connection = new PrismaClient({
91✔
114
      datasources: {
91✔
115
        db: {
91✔
116
          url: connectionConfig,
91✔
117
        },
91✔
118
      },
91✔
119
    });
91✔
120
    await connection.$connect();
91✔
121

122
    // validate connection
91✔
123
    try {
91✔
124
      await connection.$queryRawUnsafe('SELECT 1');
91✔
125
      return connection;
91✔
126
      // eslint-disable-next-line @typescript-eslint/no-explicit-any
91✔
127
    } catch (error: any) {
91!
128
      await connection.$disconnect();
×
129
      throw new Error(`database connection failed: ${error.message}`);
×
130
    }
×
131
  }
91✔
132

133
  private getReadOnlyRoleName(baseId: string) {
91✔
134
    return `${BASE_READ_ONLY_ROLE_PREFIX}${baseId}`;
6✔
135
  }
6✔
136

137
  async createReadOnlyRole(baseId: string) {
91✔
138
    const roleName = this.getReadOnlyRoleName(baseId);
2✔
139
    await this.prismaService
2✔
140
      .txClient()
2✔
141
      .$executeRawUnsafe(
2✔
142
        this.knex
2✔
143
          .raw(
2✔
144
            `CREATE ROLE ?? WITH NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION`,
2✔
145
            [roleName]
2✔
146
          )
147
          .toQuery()
2✔
148
      );
149
    await this.prismaService
2✔
150
      .txClient()
2✔
151
      .$executeRawUnsafe(
2✔
152
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
2✔
153
      );
154
    await this.prismaService
2✔
155
      .txClient()
2✔
156
      .$executeRawUnsafe(
2✔
157
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
2✔
158
      );
159
    await this.prismaService
2✔
160
      .txClient()
2✔
161
      .$executeRawUnsafe(
2✔
162
        this.knex
2✔
163
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
2✔
164
            baseId,
2✔
165
            roleName,
2✔
166
          ])
2✔
167
          .toQuery()
2✔
168
      );
169
  }
2✔
170

171
  async dropReadOnlyRole(baseId: string) {
91✔
172
    const roleName = this.getReadOnlyRoleName(baseId);
×
173
    await this.prismaService
×
174
      .txClient()
×
175
      .$executeRawUnsafe(
×
176
        this.knex.raw(`REVOKE USAGE ON SCHEMA ?? FROM ??`, [baseId, roleName]).toQuery()
×
177
      );
178
    await this.prismaService
×
179
      .txClient()
×
180
      .$executeRawUnsafe(
×
181
        this.knex
×
182
          .raw(`REVOKE SELECT ON ALL TABLES IN SCHEMA ?? FROM ??`, [baseId, roleName])
×
183
          .toQuery()
×
184
      );
185
    await this.prismaService
×
186
      .txClient()
×
187
      .$executeRawUnsafe(
×
188
        this.knex
×
189
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [
×
190
            baseId,
×
191
            roleName,
×
192
          ])
×
193
          .toQuery()
×
194
      );
195
    await this.prismaService
×
196
      .txClient()
×
197
      .$executeRawUnsafe(this.knex.raw(`DROP ROLE IF EXISTS ??`, [roleName]).toQuery());
×
198
  }
×
199

200
  async grantReadOnlyRole(baseId: string) {
91✔
201
    const roleName = this.getReadOnlyRoleName(baseId);
×
202
    await this.prismaService
×
203
      .txClient()
×
204
      .$executeRawUnsafe(
×
205
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
×
206
      );
207
    await this.prismaService
×
208
      .txClient()
×
209
      .$executeRawUnsafe(
×
210
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
×
211
      );
212
    await this.prismaService
×
213
      .txClient()
×
214
      .$executeRawUnsafe(
×
215
        this.knex
×
216
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
×
217
            baseId,
×
218
            roleName,
×
219
          ])
×
220
          .toQuery()
×
221
      );
222
  }
×
223

224
  private async roleExits(role: string): Promise<boolean> {
91✔
225
    const roleExists = await this.prismaService.$queryRaw<
184✔
226
      { count: bigint }[]
227
    >`SELECT count(*) FROM pg_roles WHERE rolname=${role}`;
184✔
228
    return Boolean(roleExists[0].count);
184✔
229
  }
184✔
230

231
  private async roleCheckAndCreate(baseId: string) {
91✔
232
    if (this.driver !== DriverClient.Pg) {
2!
233
      return;
×
234
    }
×
235
    const roleName = this.getReadOnlyRoleName(baseId);
2✔
236
    if (!(await this.roleExits(roleName))) {
2✔
237
      await this.createReadOnlyRole(baseId);
2✔
238
    }
2✔
239
  }
2✔
240

241
  private async setRole(prisma: Prisma.TransactionClient, baseId: string) {
91✔
242
    const roleName = this.getReadOnlyRoleName(baseId);
2✔
243
    await prisma.$executeRawUnsafe(this.knex.raw(`SET ROLE ??`, [roleName]).toQuery());
2✔
244
  }
2✔
245

246
  private async resetRole(prisma: Prisma.TransactionClient) {
91✔
247
    await prisma.$executeRawUnsafe(this.knex.raw(`RESET ROLE`).toQuery());
2✔
248
  }
1✔
249

250
  private async readonlyExecuteSql(sql: string) {
91✔
251
    return this.db?.$queryRawUnsafe(sql);
2✔
252
  }
2✔
253

254
  /**
91✔
255
   * check sql is safe
256
   * 1. role operations validation
257
   * 2. parse sql to valid table names
258
   * 3. read only role check table access
259
   */
91✔
260
  private async safeCheckSql(
91✔
261
    baseId: string,
2✔
262
    sql: string,
2✔
263
    opts?: { projectionTableDbNames?: string[]; projectionTableIds?: string[] }
2✔
264
  ) {
2✔
265
    const { projectionTableDbNames = [] } = opts ?? {};
2✔
266
    // 1. role operations keywords validation, only pg support
2✔
267
    if (this.driver == DriverClient.Pg) {
2✔
268
      validateRoleOperations(sql);
2✔
269
    }
2✔
270
    let tableNames = projectionTableDbNames;
2✔
271
    if (!projectionTableDbNames.length) {
2✔
272
      const tables = await this.prismaService.tableMeta.findMany({
1✔
273
        where: {
1✔
274
          baseId,
1✔
275
        },
1✔
276
        select: {
1✔
277
          dbTableName: true,
1✔
278
        },
1✔
279
      });
1✔
280
      tableNames = tables.map((table) => table.dbTableName);
1✔
281
    }
1✔
282
    // 2. parse sql to valid table names
2✔
283
    checkTableAccess(sql, {
2✔
284
      tableNames,
2✔
285
      database: this.driver,
2✔
286
    });
2✔
287
    // 3. read only role check table access, only pg and pg version > 14 support
2✔
288
    await this.readonlyExecuteSql(sql);
2✔
289
  }
2✔
290

291
  async executeQuerySql<T = unknown>(
91✔
292
    baseId: string,
2✔
293
    sql: string,
2✔
294
    opts?: {
2✔
295
      projectionTableDbNames?: string[];
296
      projectionTableIds?: string[];
297
    }
2✔
298
  ) {
2✔
299
    await this.safeCheckSql(baseId, sql, opts);
2✔
300
    await this.roleCheckAndCreate(baseId);
2✔
301
    return this.prismaService.$tx(async (prisma) => {
2✔
302
      try {
2✔
303
        await this.setRole(prisma, baseId);
2✔
304
        return await prisma.$queryRawUnsafe<T>(sql);
2✔
305
        // eslint-disable-next-line @typescript-eslint/no-explicit-any
2✔
306
      } catch (error: any) {
2✔
307
        throw new BadRequestException(error?.meta?.message || error?.message);
1!
308
      } finally {
2!
309
        await this.resetRole(prisma).catch((error) => {
2✔
310
          console.log('resetRole error', error);
1✔
311
        });
1✔
312
      }
2✔
313
    });
2✔
314
  }
2✔
315
}
91✔
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc