fireql

FireQL

FireQL is the Golang library and interactive CLI tool to query the Google Firestore database using SQL syntax.

It is built on top of the official Google Firestore Client SDK that will allow running queries Cloud Firestore database using SQL syntax. Inspired by Firebase FireSQL.

Usage

FireQL can be used as Go library or interactive command-line tool.

Go Library

An example of querying collections using SQL syntax:

import (
    "github.com/pgollangi/fireql"
)

func main() {
    fql, err := fireql.New("<GCP_PROJECT_ID>")
    //OR
    fql, err = fireql.New("<GCP_PROJECT_ID>", fireql.OptionServiceAccount("<SERVICE_ACCOUNT_JSON>"))
    if err != nil {
        panic(err)
    }
    
	// Now, execute SELECT query
    result, err := fql.Execute("SELECT * `users` order by id desc limit 10")
    if err != nil {
        panic(err)
    }
    _ = result
}

Command-Line

fireql [flags]

Example

$ fireql --project $PROJECT_ID
Welcome! Use SQL to query Firestore.
Use Ctrl+D, type "exit" to exit.
Visit github.com/pgollangi/FireQL for more details.
fireql>select id, name from users limit 2
+------+------------+
|  ID  |    NAME    |
+------+------------+
| 1046 | bob        |
| 1047 | smith      |
+------+------------+
(2 rows)
fireql>

Read the documentation for more information on CLI usage.

Examples

Some cool SELECT queries that are possible with FireQL:

select * from users
select * from `[contacts]` // To query collection group. enclose subcollect name in square brackets.
select *, id as user_id from users
select id, email as email_address, `address.city` AS city from `users`
select * from users order by 'address.city' desc limit 10
select * from `users` where id > 50
select id, LENGTH(contacts) as total_contacts from `users`
select id, (age > 100) as centenarian as total_contacts from `users`
select __name__ from users // to select document id

FireQL depend on govaluate to evaluate expressions in SELECT. See list of possible expressions and operators here.

See Wiki for more examples.

Authentication

fireql.New assume Google Application Default Credentials to authenticate to Firestore database if serverAccount not passed. Otherwise use service account for authentication.

Installation

Homebrew

brew install pgollangi/tap/fireql

Updating:

brew upgrade fireql

Scoop (for windows)

scoop bucket add pgollangi-bucket https://github.com/pgollangi/scoop-bucket.git
scoop install fireql

Docker

docker run pgollangi/fireql

Go

go install github.com/pgollangi/fireql@latest

Manual

You can alternately download a suitable binary for your OS at the releases page.

Limitations

All of firestore query limitations are applicable when running queries using FireQL.

In addition to that:

• Only SELECT queries for now. Support for INSERT, UPDATE, and DELETE might come in the future.
• Only AND conditions supported in WHERE clause.
• No support for JOINs.
• LIMIT doesn't accept an OFFSET, only a single number.
• No support of GROUP BY and aggregate function COUNT.

Future scope

• Create an interactive command-line shell to run queries
• Expand support for all logical conditions in WHERE clause by internally issuing multiple query requests to Firestore and merging results locally before returning.
• GROUP BY support
• Support other DML queries: INSERT, UPDATE, and DELETE

Run

Tests

$ go test ./...

Contributing

Thanks for considering contributing to this project!

Please read the Contributions and Code of conduct.

Feel free to open an issue or submit a pull request!

Licence

FireQL is open-sourced software licensed under the MIT license.