DEV Community

Cover image for FireQL - Query Google Firestore database using SQL syntax.
Prasanna Kumar
Prasanna Kumar

Posted on

FireQL - Query Google Firestore database using SQL syntax.

#github #opensource #go #showdev

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.

GitHub logo pgollangi / FireQL

Interactive CLI and Go library to query Google Firestore database using SQL syntax.

build Codecov Go Report Card GitHub go.mod Go version GitHub release (latest by date) PkgGoDev License: MIT

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.

fireql

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
…
Enter fullscreen mode Exit fullscreen mode
View on GitHub

Usage

FireQL can be used as a 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
}
Enter fullscreen mode Exit fullscreen mode

Command-Line 

fireql [flags]
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

Read the documentation for more information on CLI usage.

Examples

Some cool SELECT queries that are possible with FireQL:

select * from users
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`
Enter fullscreen mode Exit fullscreen mode

See Wiki for more examples.

Installation

Homebrew 

brew install pgollangi/tap/fireql
Enter fullscreen mode Exit fullscreen mode

Updating:

brew upgrade fireql
Enter fullscreen mode Exit fullscreen mode

Scoop (for windows) 

scoop bucket add pgollangi-bucket https://github.com/pgollangi/scoop-bucket.git
scoop install fireql
Enter fullscreen mode Exit fullscreen mode

Docker 

docker run pgollangi/fireql
Enter fullscreen mode Exit fullscreen mode

Go 

go install github.com/pgollangi/fireql@latest
Enter fullscreen mode Exit fullscreen mode

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

  • 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

Thanks for the read! Try using this if you are working with Firestore and share feedback.

Originally posted on my blog https://me.p11r.dev/fireql

Top comments (2)

Collapse
 
jcubic profile image
Jakub T. Jankiewicz • • Edited

Nice project. I would love to see something like this written in JavaScript that can run in the browser.

I would update the README and remove bash from code snippet that show how use the tool, since it's for bash shell scripts not bash commands, and it shows text as part of bash syntax.

Collapse
 
p11r profile image
Prasanna Kumar •

Thanks Jakub for the feedback. I'll update README