使用 Timescale 创建无服务器全球分布式时序 API

cd timescale-api

postgres://tsdbadmin:YOURPASSWORD@...

psql <SERVICEURL>

CREATE TABLE readings(
  ts timestamptz DEFAULT now() NOT NULL,
  sensor UUID NOT NULL,
  metadata jsonb,
  value numeric NOT NULL
 );

SELECT create_hypertable('readings', 'ts');

npx wrangler hyperdrive create hyperdrive --connection-string="SERVICEURL"

import { Client } from "pg";

export interface Env {
	HYPERDRIVE: Hyperdrive;
}

export default {
	async fetch(request, env, ctx): Promise<Response> {
		// Create a new client on each request. Hyperdrive maintains the underlying
		// database connection pool, so creating a new client is fast.
		const client = new Client({
			connectionString: env.HYPERDRIVE.connectionString,
		});
		await client.connect();

		const url = new URL(request.url);
		// Create a route for inserting JSON as readings
		if (request.method === "POST" && url.pathname === "/readings") {
			// Parse the request's JSON payload
			const productData = await request.json();

			// Write the raw query. You are using jsonb_to_recordset to expand the JSON
			// to PG INSERT format to insert all items at once, and using coalesce to
			// insert with the current timestamp if no ts field exists
			const insertQuery = `
      INSERT INTO readings (ts, sensor, metadata, value)
      SELECT coalesce(ts, now()), sensor, metadata, value FROM jsonb_to_recordset($1::jsonb)
      AS t(ts timestamptz, sensor UUID, metadata jsonb, value numeric)
  `;

			const insertResult = await client.query(insertQuery, [
				JSON.stringify(productData),
			]);

			// Collect the raw row count inserted to return
			const resp = new Response(JSON.stringify(insertResult.rowCount), {
				headers: { "Content-Type": "application/json" },
			});

			return resp;

			// Create a route for querying within a time-frame
		} else if (request.method === "GET" && url.pathname === "/readings") {
			const limit = url.searchParams.get("limit");

			// Query the readings table using the limit param passed
			const result = await client.query(
				"SELECT * FROM readings ORDER BY ts DESC LIMIT $1",
				[limit],
			);

			// Return the result as JSON
			const resp = new Response(JSON.stringify(result.rows), {
				headers: { "Content-Type": "application/json" },
			});

			return resp;
		}
	},
} satisfies ExportedHandler<Env>;

npx wrangler deploy

[
	{ "sensor": "6f3e43a4-d1c1-4cb6-b928-0ac0efaf84a5", "value": 0.3 },
	{ "sensor": "d538f9fa-f6de-46e5-9fa2-d7ee9a0f0a68", "value": 10.8 },
	{ "sensor": "5cb674a0-460d-4c80-8113-28927f658f5f", "value": 18.8 },
	{ "sensor": "03307bae-d5b8-42ad-8f17-1c810e0fbe63", "value": 20.0 },
	{ "sensor": "64494acc-4aa5-413c-bd09-2e5b3ece8ad7", "value": 13.1 },
	{ "sensor": "0a361f03-d7ec-4e61-822f-2857b52b74b3", "value": 1.1 },
	{ "sensor": "50f91cdc-fd19-40d2-b2b0-c90db3394981", "value": 10.3 }
]