sql-engine
A technology proof-of-concept of using MySQL and SQL to query a levelup/leveldb database.
Implemented as part of a hack project for campjs 2013.
Pull requests encouraged!
This will eventually become a plugin for the level-queryengine pluggable query engine system.
Quick start
To play around with this, git clone
and npm install
and start the example server and specify a port to listen to:
$ node server.js 3307
The example Server adds the following non-relational JSON documents to the local leveldb:
Connect to the levelup MySQL server (authentication and database selection not currently implemented)
$ mysql -P 3308 --protocol=tcp
Execute some queries against the test 'users' table:
mysql> select * from users;
+-------+------+---------+------+----------+-----------+
| name | num | awesome | x | car.make | car.model |
+-------+------+---------+------+----------+-----------+
| Bob | 42 | goodbye | 99 | NULL | NULL |
| Jane | 43 | blah | NULL | Toyota | Camry |
| Peter | 88 | true | NULL | Toyota | Corolla |
+-------+------+---------+------+----------+-----------+
3 rows in set (0.00 sec)
mysql> select name, num from users where car.make = 'Toyota';
+-------+------+
| name | num |
+-------+------+
| Jane | 43 |
| Peter | 88 |
+-------+------+
2 rows in set (0.00 sec)
How it works
- sql-engine uses the awesome mysql2 library to do the MySQL proxying to look like a MySQL server protocol speaking server.
- sql-engine parses the SQL using simpleSqlParser into an AST.
- the SQL AST is then parsed into the mongodb query language and ran using jsonquery-engine against the levelup/leveldb instance.
Notes
This is highly experimental, and needs a lot more work to support the full suite of SQL operations.
- Currently only AND and OR are implemented for SQL queries.
- Currently only equality is implemented for SQL where clauses (eg.
colA = colB
) - the 'FROM' statemenet will query a levelup "sublevel" by that name.
- No database authentication is implemented.
- No joins are currently implemented. I plan to use foreign-key and/or level-join to implement this.
- No sorting is implemented. I plan to use sort-stream to do that.
- simpleSqlParser also parses INSERT, DELETE and UPDATE statements, but these haven't been implemented.