node-invite-system/db/sql-create.js

const pgp = require('pg-promise')();

// 连接到数据库
const db = pgp({
    user: 'web3',
    password: '666',
    host: 'web2023.fmode.cn',
    port: 25432,
    database: 'dev'
});

// 执行SELECT语句并返回结果的函数
async function main() {
    try {
        // 查询：数据库版本信息
        let sql = `
        CREATE TABLE "Member" (
            "objectId" text PRIMARY KEY,
            "name" text,
            "mobile" text,
            "registerDate" timestamp with time zone,
            "invite" text REFERENCES "Member" ("objectId"),
            "invitePath" jsonb,
            "inviteDate" timestamp with time zone
        );
        CREATE TABLE "InviteEvent" (
            "objectId" text PRIMARY KEY,
            "member" text REFERENCES "Member" ("objectId"),
            "type" text,
            "click" text,
            "share" text,
            "count" double precision DEFAULT 1
        );

        INSERT INTO "Member" ("objectId", "name", "mobile", "registerDate", "invite", "invitePath", "inviteDate") 
        VALUES 
        ('1', '张三', '13812345678', '2024-06-16 12:00:00', NULL, '[]', NULL),
        ('2', '李四', '13987654321', '2024-06-16 12:30:00', '1', '["1"]', '2024-06-16 13:00:00'),
        ('3', '王五', '13611112222', '2024-06-16 13:00:00', '2', '["1", "2"]', '2024-06-16 13:30:00'),
        ('4', '赵六', '13533334444', '2024-06-16 14:00:00', '2', '["1", "2"]', '2024-06-16 14:30:00'),
        ('5', '刘七', '13755556666', '2024-06-16 15:00:00', '4', '["1", "2", "4"]', '2024-06-16 15:30:00');

        INSERT INTO "InviteEvent" ("objectId", "member", "type", "click", "share", "count") 
        VALUES 
        ('101', '1', '邀请注册', '10', '5', 1),
        ('102', '2', '邀请注册', '8', '3', 1),
        ('103', '3', '邀请注册', '6', '2', 1),
        ('104', '4', '邀请注册', '5', '1', 1),
        ('105', '5', '邀请注册', '4', '1', 1);
        `
        const data = await db.any(sql);
        console.log(data)
        return data;
    } catch (error) {
        console.error('Error executing SELECT query:', error);
        return null;
    }
}

// 调用函数并处理结果
main()