📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!
TypeScript chartsclickhousedockergdprgoogle-analyticsmetricsmonitoringmysqlnestjsprivacyredisself-hostedselfhostedstatisticstailwindcssweb-analyticswebanalyticswebsite
import { generateKeyPairSync } from 'crypto'
import { mkdtemp, writeFile, rm } from 'fs/promises'
import { join } from 'path'
import { tmpdir } from 'os'
import { BigQueryService } from './bigquery.service'
import {
BigQueryConfig,
parseServiceAccountCredentials,
WAREHOUSE_SCHEMA,
WarehouseExportError,
} from './data-export.schema'
jest.mock('../common/integrations/clickhouse', () => ({ clickhouse: {} }))
jest.mock('timers/promises', () => ({
setTimeout: jest.fn().mockResolvedValue(undefined),
}))
const config: BigQueryConfig = {
projectId: 'abcdefghijkl',
gcpProjectId: 'example-project',
datasetId: 'analytics',
location: 'EU',
tablePrefix: 'swetrix_abcdefghijkl',
}
describe('BigQueryService', () => {
let service: any
beforeEach(() => {
service = new BigQueryService({} as any)
jest.restoreAllMocks()
})
const table = () => ({
type: 'TABLE',
description:
'Swetrix warehouse export v1; project=abcdefghijkl; kind=pageviews',
timePartitioning: { type: 'DAY', field: 'event_date' },
schema: { fields: WAREHOUSE_SCHEMA },
})
it.each([
{ projectId: "' OR 1=1 --" },
{ gcpProjectId: 'example-project` UNION SELECT 1 --' },
{ datasetId: 'analytics`; DROP TABLE other; --' },
{ tablePrefix: 'events` WHERE TRUE; --' },
{ location: 'EU&projectId=other-project' },
])(
'rejects injected destination settings %j before authentication',
async (invalid) => {
const auth = jest.spyOn(service, 'auth')
await expect(
service.validate({ ...config, ...invalid }, {}),
).rejects.toThrow('Invalid BigQuery destination settings')
await expect(
service.exportDay(
{ ...config, ...invalid },
{},
'2026-10-26',
'run-id',
),
).rejects.toThrow('Invalid BigQuery destination settings')
expect(auth).not.toHaveBeenCalled()
},
)
it.each([
{ description: 'An unrelated table' },
{ timePartitioning: { type: 'MONTH', field: 'event_date' } },
{ schema: { fields: [] } },
{ type: 'VIEW' },
])(
'refuses to overwrite incompatible existing tables: %j',
async (override) => {
jest.spyOn(service, 'json').mockResolvedValue({ ...table(), ...override })
await expect(
service.ensureTable({}, config, 'pageviews'),
).rejects.toThrow('unused table prefix')
},
)
it('accepts only its owned schema and partition', async () => {
jest.spyOn(service, 'json').mockResolvedValue(table())
await expect(
service.ensureTable({}, config, 'pageviews'),
).resolves.toBeUndefined()
})
it('rechecks table ownership after a concurrent table creation conflict', async () => {
const json = jest
.spyOn(service, 'json')
.mockRejectedValueOnce(new WarehouseExportError('Missing', 404))
.mockRejectedValueOnce(new WarehouseExportError('Conflict', 409))
.mockResolvedValueOnce({
...table(),
description: 'Someone else created this',
})
await expect(service.ensureTable({}, config, 'pageviews')).rejects.toThrow(
'unused table prefix',
)
expect(json).toHaveBeenCalledTimes(3)
})
it('loads only a single date partition with no partial-row acceptance', async () => {
const request = jest.spyOn(service, 'request').mockResolvedValue(
new Response(null, {
headers: {
location:
'https://bigquery.googleapis.com/upload/bigquery/v2/projects/example-project/jobs?upload_id=123',
},
}),
)
jest.spyOn(service, 'upload').mockResolvedValue(undefined)
await service.submitJob(
{},
config,
{ kind: 'pageviews', bytes: 12 },
'2026-10-26',
'job-1',
)
const body = JSON.parse(
(request.mock.calls[0][2] as RequestInit).body as string,
)
expect(body.configuration.load.destinationTable.tableId).toBe(
'swetrix_abcdefghijkl_pageviews$20261026',
)
expect(body.configuration.load.writeDisposition).toBe('WRITE_TRUNCATE')
expect(body.configuration.load.createDisposition).toBe('CREATE_NEVER')
expect(body.configuration.load.maxBadRecords).toBe(0)
expect(body.jobReference).toEqual({
projectId: config.gcpProjectId,
location: 'EU',
jobId: 'job-1',
})
})
it('clears an empty day with a parameterized partition delete', async () => {
const json = jest.spyOn(service, 'json').mockResolvedValue({})
await service.submitJob(
{},
config,
{ kind: 'custom_events', bytes: 0 },
'2026-10-26',
'job-1',
)
const body = JSON.parse(
(json.mock.calls[0][2] as RequestInit).body as string,
)
expect(body.configuration.query.query).toBe(
'DELETE FROM `example-project.analytics.swetrix_abcdefghijkl_custom_events` WHERE event_date = @day',
)
expect(
body.configuration.query.queryParameters[0].parameterValue.value,
).toBe('2026-10-26')
})
it('recovers an accepted job after the submit response is lost', async () => {
jest
.spyOn(service, 'findJob')
.mockResolvedValueOnce(null)
.mockResolvedValueOnce({ status: { state: 'RUNNING' } })
const submit = jest
.spyOn(service, 'submitJob')
.mockRejectedValue(new WarehouseExportError('Network timeout'))
jest.spyOn(service, 'waitForJob').mockResolvedValue({
status: { state: 'DONE' },
statistics: { load: { outputRows: '12' } },
})
await expect(
service.loadPartition(
{},
config,
{ kind: 'pageviews' },
'2026-10-26',
'stable-run',
),
).resolves.toBe(12)
expect(submit).toHaveBeenCalledTimes(1)
})
it('does not resubmit a completed job on retry', async () => {
jest
.spyOn(service, 'findJob')
.mockResolvedValue({ status: { state: 'DONE' } })
const submit = jest.spyOn(service, 'submitJob')
jest.spyOn(service, 'waitForJob').mockResolvedValue({
status: { state: 'DONE' },
statistics: { load: { outputRows: '12' } },
})
await service.loadPartition(
{},
config,
{ kind: 'pageviews' },
'2026-10-26',
'stable-run',
)
expect(submit).not.toHaveBeenCalled()
})
it('keeps the same job identity when persisted progress changes between retries', async () => {
const find = jest
.spyOn(service, 'findJob')
.mockResolvedValue({ status: { state: 'DONE' } })
jest
.spyOn(service, 'waitForJob')
.mockResolvedValue({ status: { state: 'DONE' } })
await service.loadPartition(
{},
{ ...config, nextDate: '2026-10-25', status: 'running' },
{ kind: 'pageviews' },
'2026-10-26',
'stable-run',
)
await service.loadPartition(
{},
{
...config,
nextDate: '2026-10-26',
status: 'error',
lastSyncedAt: new Date(),
},
{ kind: 'pageviews' },
'2026-10-26',
'stable-run',
)
expect(find.mock.calls[0][2]).toBe(find.mock.calls[1][2])
})
it('rejects upload session URLs outside the fixed Google hosts', () => {
expect(
service.isGoogleUploadUrl(
'http://bigquery.googleapis.com/upload/bigquery/jobs',
),
).toBe(false)
expect(
service.isGoogleUploadUrl(
'https://bigquery.googleapis.com.evil.test/upload/bigquery/jobs',
),
).toBe(false)
expect(
service.isGoogleUploadUrl(
'https://[email protected]/upload/bigquery/jobs',
),
).toBe(false)
expect(
service.isGoogleUploadUrl(
'https://www.googleapis.com/upload/bigquery/v2/projects/test/jobs?upload_id=1',
),
).toBe(true)
})
it('resumes from the server acknowledged byte offset', async () => {
const directory = await mkdtemp(join(tmpdir(), 'swetrix-export-test-'))
const path = join(directory, 'rows.ndjson')
await writeFile(path, '1234567890')
const request = jest
.spyOn(service, 'request')
.mockRejectedValueOnce(new WarehouseExportError('Timeout'))
.mockResolvedValueOnce(
new Response(null, { status: 308, headers: { range: 'bytes=0-3' } }),
)
.mockResolvedValueOnce(new Response('{}'))
try {
await service.upload(
{},
'https://bigquery.googleapis.com/upload/bigquery/v2/test',
{ path, bytes: 10 },
)
expect(
new Headers((request.mock.calls[1][2] as RequestInit).headers).get(
'content-range',
),
).toBe('bytes */10')
expect(
new Headers((request.mock.calls[2][2] as RequestInit).headers).get(
'content-range',
),
).toBe('bytes 4-9/10')
expect(
new Headers((request.mock.calls[2][2] as RequestInit).headers).get(
'content-length',
),
).toBe('6')
} finally {
await rm(directory, { force: true, recursive: true })
}
})
it('never exposes provider response content in errors', async () => {
jest
.spyOn(global, 'fetch')
.mockResolvedValue(
new Response('secret private_key=PRIVATE', { status: 403 }),
)
const auth = {
getRequestHeaders: async () =>
new Headers({ authorization: 'Bearer secret' }),
}
await expect(
service.request(
auth,
'https://bigquery.googleapis.com/bigquery/v2/test',
{},
),
).rejects.toThrow('access was denied')
try {
await service.request(
auth,
'https://bigquery.googleapis.com/bigquery/v2/test',
{},
)
} catch (error) {
expect(JSON.stringify(error)).not.toContain('PRIVATE')
}
})
it.each([
'https://attacker.invalid/bigquery/v2/projects/test/jobs',
'http://bigquery.googleapis.com/bigquery/v2/projects/test/jobs',
'https://bigquery.googleapis.com.attacker.invalid/bigquery/v2/test',
'https://[email protected]/bigquery/v2/test',
'https://[email protected]/bigquery/v2/test',
'https://bigquery.googleapis.com:8443/bigquery/v2/test',
'http://169.254.169.254/latest/meta-data/',
'https://127.0.0.1/bigquery/v2/test',
'https://[::1]/bigquery/v2/test',
'//bigquery.googleapis.com/bigquery/v2/test',
'file:///etc/passwd',
'https://www.googleapis.com/storage/v1/b',
'https://bigquery.googleapis.com/bigquery/v2/../../../other-api',
'https://bigquery.googleapis.com/bigquery/v2/%2f%2fattacker.invalid',
'https://bigquery.googleapis.com/bigquery/v2/%252f%252fattacker.invalid',
'https://bigquery.googleapis.com/bigquery/v2/test#fragment',
'https://bigquery.googleapis.com\n/bigquery/v2/test',
'https://bigquery.googleapis.com\\bigquery\\v2\\test',
'not-a-url',
])(
'rejects unsafe request targets before authentication: %s',
async (url) => {
const fetch = jest
.spyOn(global, 'fetch')
.mockResolvedValue(new Response('{}'))
const auth = {
getRequestHeaders: jest
.fn()
.mockResolvedValue(new Headers({ authorization: 'Bearer secret' })),
}
await expect(service.request(auth, url, {})).rejects.toThrow(
'Invalid BigQuery request URL',
)
expect(auth.getRequestHeaders).not.toHaveBeenCalled()
expect(fetch).not.toHaveBeenCalled()
},
)
it.each([
'https://bigquery.googleapis.com/bigquery/v2/projects/example-project/datasets/analytics',
'https://bigquery.googleapis.com/bigquery/v2/projects/example-project/datasets/analytics/tables/swetrix_pageviews',
'https://bigquery.googleapis.com/bigquery/v2/projects/example-project/jobs/swetrix_123?location=EU',
'https://bigquery.googleapis.com/upload/bigquery/v2/projects/example-project/jobs?uploadType=resumable',
'https://www.googleapis.com/upload/bigquery/v2/projects/example-project/jobs?upload_id=opaque%2Btoken%2Fid&upload_protocol=resumable',
'https://bigquery.googleapis.com/resumable/upload/bigquery/v2/projects/example-project/jobs?upload_id=opaque%3D',
])(
'keeps supported API and upload requests on fixed Google origins: %s',
async (url) => {
const fetch = jest
.spyOn(global, 'fetch')
.mockResolvedValue(new Response('{}'))
const auth = {
getRequestHeaders: jest
.fn()
.mockResolvedValue(new Headers({ authorization: 'Bearer secret' })),
}
await service.request(auth, url, { redirect: 'follow' })
const [target, options] = fetch.mock.calls[0]
expect(target).toBeInstanceOf(URL)
expect(String(target)).toBe(url)
expect(new Headers(options.headers).get('authorization')).toBe(
'Bearer secret',
)
expect(options.redirect).toBe('error')
},
)
it('does not follow redirects to an external host', async () => {
const fetch = jest.spyOn(global, 'fetch').mockResolvedValue(
new Response(null, {
status: 302,
headers: { location: 'https://attacker.invalid/token' },
}),
)
const auth = { getRequestHeaders: async () => new Headers() }
await expect(
service.request(
auth,
'https://bigquery.googleapis.com/bigquery/v2/test',
{},
),
).rejects.toThrow('BigQuery could not complete the request')
expect(fetch).toHaveBeenCalledTimes(1)
expect(fetch.mock.calls[0][1].redirect).toBe('error')
})
it('uploads large files as bounded chunks aligned to Google requirements', async () => {
const directory = await mkdtemp(join(tmpdir(), 'swetrix-export-test-'))
const path = join(directory, 'rows.ndjson')
const chunk = 8 * 1024 * 1024
await writeFile(path, Buffer.alloc(chunk + 5))
const request = jest
.spyOn(service, 'request')
.mockResolvedValueOnce(
new Response(null, {
status: 308,
headers: { range: `bytes=0-${chunk - 1}` },
}),
)
.mockResolvedValueOnce(new Response('{}'))
try {
await service.upload(
{},
'https://bigquery.googleapis.com/upload/bigquery/v2/test',
{ path, bytes: chunk + 5 },
)
expect(
new Headers((request.mock.calls[0][2] as RequestInit).headers).get(
'content-range',
),
).toBe(`bytes 0-${chunk - 1}/${chunk + 5}`)
expect(
new Headers((request.mock.calls[1][2] as RequestInit).headers).get(
'content-range',
),
).toBe(`bytes ${chunk}-${chunk + 4}/${chunk + 5}`)
} finally {
await rm(directory, { force: true, recursive: true })
}
})
it('whitelists key fields and discards custom credential URLs and impersonation', () => {
const { privateKey } = generateKeyPairSync('rsa', { modulusLength: 2048 })
const credentials = {
type: 'service_account',
project_id: config.gcpProjectId,
client_email: '[email protected]',
private_key: privateKey
.export({ format: 'pem', type: 'pkcs8' })
.toString(),
}
expect(
parseServiceAccountCredentials({
...credentials,
token_uri: 'https://attacker.invalid/token',
universe_domain: 'attacker.invalid',
subject: '[email protected]',
}),
).toEqual(credentials)
expect(() =>
parseServiceAccountCredentials({
...credentials,
private_key: 'invalid',
}),
).toThrow('valid Google service account')
})
})