TypeScript Client.query方法代码示例

async function testSchema(sql: string) {
  const client = new Client(connectionDetails)
  await client.connect()
  await client.query('DROP SCHEMA IF EXISTS DatabaseIntrospector cascade;')
  await client.query('CREATE SCHEMA DatabaseIntrospector;')
  await client.query('SET search_path TO DatabaseIntrospector;')
  await client.query(sql)

  expect(await introspect()).toMatchSnapshot()

  await client.end()
}

function createPersons() {
    const persons = createArray(numberOfPersons, i => {
        return [
            i,
            faker.name.findName(),
            faker.address.streetAddress(),
            faker.phone.phoneNumber(),
            faker.date.past(),
        ]
    })
    return client
        .query(
            'create table person (personid decimal(9), name varchar(400), address varchar(400), phone varchar(100), stamp TIMESTAMP, primary key(personid))'
        )
        .then(
            commentColumns('person', [
                ['personid', 'Person id test'],
                ['name', 'Full name'],
                ['address', 'Home address'],
                ['phone', 'Phonenumber'],
                ['stamp', 'Timestamp'],
            ])
        )
        .then(
            innsertArray('insert into person values($1,$2,$3,$4,$5)', persons)
        )
}

export async function query<T>(config: ConnectionConfig, queryText: string, values: any[] = []) {
  const client = new Client(config)
  await client.connect()
  const result = await client.query(queryText, values)
  await client.end()
  return result as QueryResult<T>
}

      .then((location) => {
        let client = new Client({
          host: location.hostname,
          port: location.port || 5432,
          database: database,
          user: user,
          password: password,

          parseInputDatesAsUTC: true // not in the type
        } as any);

        client.on('drain', client.end.bind(client)); // disconnect client when all queries are finished
        client.connect();

        //query is executed once connection is established and PostgreSQL server is ready for a query
        let q = client.query(new (Query as any)(query) as any);

        // ToDo: use node-pg-cursor or node-pg-query-stream here instead
        q.on('row', function(row: any) {
          stream.push(row);
        });

        q.on('error', function(err: any) {
          stream.emit('error', err);  // Pass on any errors
        });

        q.on('end', function() {
          stream.push(null);  // pushing null, indicating EOF
        });
      })

  socket.on('save_map', function(data) {
    var map;

    console.log('Saving map');

    if (!data) {
      console.error('empty data')
      return;
    }

    data = JSON.parse(data);
    if (!data.id) {
      console.error('Tried to save map without ID');
      return;
    }
    map = maps[data.id-1];

    map.tile_data = JSON.stringify(data.tile_data);
    map.name = data.name;
    map.music = data.music;

    io.sockets.emit('load_map', JSON.stringify({id: map.id, name: map.name, tile_data: JSON.parse(map.tile_data), music: map.music}));

    var q = util.format('UPDATE maps SET name = \'%s\', tile_data = \'%s\', tile_set = \'%s\', music = \'%s\' WHERE id = %d', data.name, JSON.stringify(data.tile_data), data.tile_set, data.music, data.id);

    client.query(q, function(error, result) {
        if (error) {
          console.log(error);
        }
        // result = result.rows[0];
        // socket.emit('load_map', JSON.stringify({id: result.id, name: result.name, tile_data: JSON.parse(result.tile_data), music: result.music}));
    });
  });

 async queryPrimaryKeys(schemaName: string): Promise<PrimaryKey[]> {
   return this.client
     .query(
       `SELECT tc.table_name, kc.column_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage kc 
        ON kc.table_name = tc.table_name 
        AND kc.table_schema = tc.table_schema
        AND kc.constraint_name = tc.constraint_name
      WHERE tc.constraint_type = 'PRIMARY KEY'
      AND tc.table_schema = $1::text
      AND kc.ordinal_position IS NOT NULL
      ORDER BY tc.table_name, kc.position_in_unique_constraint;`,
       [schemaName.toLowerCase()],
     )
     .then(keys => {
       const grouped = _.groupBy(keys.rows, 'table_name')
       return _.map(grouped, (pks, key) => {
         return {
           tableName: key,
           fields: pks.map(x => x.column_name),
         } as PrimaryKey
       })
     })
 }

 return new Promise<boolean>((resolve, reject) => {
   connection.query('SELECT 1', (err, results) => {
     if (err) {
       resolve(false);
     }
     resolve(true);
   });
 });

const run = async () => {
    const dbCreate = new Client({})
    await dbCreate.connect()
    try {
        await dbCreate.query('drop database gandalf_test')
    } catch (err) {
        console.log('drop err', err.message)
    }
    await dbCreate.query('create database gandalf_test')
    await dbCreate.end()
    await client.connect()
    await createPersons()
        .then(createCompanies)
        .then(createProducts)
        .then(createOrders)
        .then(createViews)
    await client.end()
}

 return new Promise<string>((resolve, reject) => {
     const client = new pg.Client(conString)            
     client.connect()
     client.query('INSERT INTO places (description, location, place_id) VALUES ($1, $2, $3)',
         [place.description, '('+place.location.lat+','+place.location.lng+')', place.id], ( error, result) => {
         if(error) reject(error)
         if(result) resolve('OK')
         client.end()
     })
 })

  async querySchemas() {
    const res = await this.client.query(
      `SELECT schema_name 
       FROM information_schema.schemata 
       WHERE schema_name NOT LIKE 'pg_%' 
       AND schema_name NOT LIKE 'information_schema';`,
    )

    return res.rows.map(x => x.schema_name)
  }