We need to create a SQL commandline tool for querying a proprietary json based datastore. I'll be using java. Any suggestions on the primary OOP design pattern I should use for building this API?

I've thought about using the builder pattern in the backend that would compose the json nodes based on statements built up by the builder but that doesn't seem very ground breaking for this use-case.

Could you suggest any other pattern you think would be more useful for this use-case? Also please provide the reason why you think the particular pattern applies.

  • If you're querying a JSON-based (so, documents) data store, then surely you'd be better off implementing queries the way document databases like Mongo do? – Andy Hunt Nov 14 '14 at 13:16
  • totally, but i want to take this opportunity to learn opp and implement a sql api – Tazo Man Nov 14 '14 at 13:22

You're basically writing a compiler, which translates from SQL to whatever your datastore understands. In the end, you'll probably have the following parts:

  • a Parser which takes SQL and produces an Abstract Syntax Tree
  • a bunch of Visitors which do semantic analysis and/or rewriting on the AST
  • an Interpreter which finally executes the (processed) query against the datastore
  • a REPL which reads input and formats output

But before you even start implementation, you should consider that SQL is defined on relations (tables), which are fundamentally unlike document collections. First, each row in a table has the same columns as all other rows in that table, and nothing else. Second, each row is a flat tuple, while a document is a tree.

If I were you, I'd first solve this mapping problem by taking something like PostgreSQL, defining the tables you expect to have, and then solving how to map your data into said tables (by actually doing it). You can use the result as a prototype for whomever wishes to have SQL, it will give you some ideas on how to implement the compiler, because semantics will be known, and you will have a solid reference for testing/verification purposes.

You may also end up deciding that copying data to an actual RDBMS is good enough; SQL queries are a fairly complex thing to implement efficiently, with joins, subqueries, aggregation, projections and whatnot.. It is even more difficult if you have to do it "from outside" of data store..


The Command pattern is usually used in these situations. You can easily create an implementation for each SQL command.

I would suggest to look into CLI frameworks like Spring Shell or Commons CLI.


Primarily Adapter to adapt native datastore commands to SQL dialect.

IMHO I would advise you against exposing SQL API in such case. You are better off delivering native datastore-like commands (possibly wrapped somehow) that are supported by datastore developers. Simply - do you create costly overhead that won't deliver much value?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.