This project demonstrates usage of Drizzle to generate MySQL-syntax queries for Manticore Search.
Because Manticore supports a limited subset of MySQL syntax, it doesn't always parse the queries that Drizzle generates, as the examples in this project demonstrate. The main syntax alignment issues I've noticed have been outlined in this issue.
Drizzle is a Typescript ORM library and migration tool. It's fully type-safe,
based on the database schema defined in schema.ts
. (More commentary at the top
of that file.)
A Drizzle ORM object is initialized in dbClient.ts
, given the schema above and
a connection. Drizzle provides numerous adapters for different databases and
connection mechanisms. Drizzle has MySQL adapters for the mysql2 and Planetscale
libraries.
As seen in dbClient.ts
, this project uses the Planetscale adapter because the
mysql2 adapter tries to connect to the database on initialization, but this
project uses Drizzle only to generate SQL strings, and the connection URL
passed to Drizzle is fake (execution is instead handled by POSTing to
Manticore's HTTP API).
Note: It's possible that Manticore's MySQL API is compatible with Drizzle's mysql2 adapter, but I haven't tested this.
Examples of ORM usage live in index.ts
. The ORM-generated queries are printed
to console and executed.
# the HTTP API
MANTICORE_URL=http://...:9308
npm run demo
[Insert single]
insert into `Post` (`id`, `user`, `txt`, `isDraft`) values (1, 'alice', 'lorem ipsum', false)
[Insert single with default]
insert into `Post` (`id`, `user`, `txt`, `isDraft`) values (1, 'alice', 'lorem ipsum', default)
[Insert multi]
insert into `Post` (`id`, `user`, `txt`, `isDraft`) values (0, 'user0', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', false), (0, 'user1', 'Proin venenatis ante sit amet neque auctor eleifend.', false), (0, 'user2', 'Morbi et massa non risus pulvinar malesuada in non nibh.', false), (0, 'user3', 'Nulla non elit id diam semper malesuada.', false), (0, 'user4', 'Nulla vel nisl ut quam rhoncus lacinia.', false), (0, 'user5', 'Fusce vitae eros sed ex aliquam finibus ultricies ut nibh.', false), (0, 'user6', 'Integer suscipit nulla ut massa dignissim pretium.', false), (0, 'user7', 'Curabitur sit amet lacus vel enim malesuada scelerisque.', false), (0, 'user8', 'Donec interdum risus sit amet viverra suscipit.', false)
[Select all]
select `id`, `user`, `txt`, `isDraft` from `Post`
[Select count]
select count(*) as `rowCount` from `Post`
[Select where]
select `id`, `user`, `txt`, `isDraft` from `Post` where `Post`.`user` = 'alice'
[Delete simple]
delete from `Post` where `Post`.`id` = 1
[Delete multi-condition]
delete from `Post` where (`Post`.`id` = 4 and `Post`.`user` = 'alice')
Note: for the reasons outlined in this issue, many of these queries
are expected to fail. Workarounds are currently required to make these Drizzle
queries compatible with Manticore - these are documented in index.ts
.