Provide specialized query methods for improved performance

[dyedgreen]

The common patterns in other libraries are:

• execute (query none; although inserts are already query none, so just using the existing query is probably fine/ has no performance difference)
• query one
• query all
• query cursor / iterator (the current query)

This would be a performance only feature; since the existing query already fills all the existing cases:

• execute: db.query(...) // ignores return value
• query one: let [firstRow] = db.query(...)
• query all: [...db.query(...)]
• query cursor / iterator: that just the existing db.query(...)

Originally posted by @dyedgreen in https://github.com/dyedgreen/deno-sqlite/issues/14#issuecomment-847397847

[dyedgreen]

Since this is strictly for performance only, we'd need to check if there are performance improvements, since otherwise the additional API surface is not worth anything, since the functionality already exists.

[dyedgreen]

I had a few thoughts about this, and with the recent issue #135, I believe the current API can be improved as follows:

• query method on DB objects which returns an array (equivalent to prepareQuery, queryAll, finalize)
• prepareQuery method on DB object with returns a PreparedQuery object
• query method on PreparedQuery which returns an iterator over rows (possibly returning the PreparedQuery itself, to avoid object allocations)
• queryAll method on PreparedQuery which returns an array
• queryOne method on PreparedQuery which returns exactly one item (or throws)
• execute method on PreparedQuery which ignores any rows
• all of the meta data accessors that are currently on rows should be moved to prepared query
• possibly have an option to return objects instead of arrays for the rows when creating the prepared query (and when using the query method on DB)

[dyedgreen]

To sketch the interface a bit more:

class DB {
  // ...

  // equivalent to prepareQuery(); queryAll(); finalize();
  query(sql: string, values?: Record<string, QueryParam> | Array<QueryParam>): Array<Array<any>>

  prepareQuery(sql: string): PreparedQuery

  // ...
}

interface RowIterator {
  next(): IteratorResult<Array<any>>;
  return(): IteratorResult<Array<any>>;
}

class PreparedQuery {
  // ...

  query(values?: Record<string, QueryParam> | Array<QueryParam>): RowIterator

  queryAll(values?: Record<string, QueryParam> | Array<QueryParam>): Array<Array<any>>

  queryOne(values?: Record<string, QueryParam> | Array<QueryParam>): Array<any>

  execute(values?: Record<string, QueryParam> | Array<QueryParam>)

  get columns(): Array<ColumnName>
}