This PR enables SQLite support for the query service. Since SQLx supports both PostgreSQL and SQLite, most of the code has been reused. An additional feature, embedded-db, has been introduced. When enabled, it activates the SQLite code; if not enabled, the PostgreSQL code is used.
The migrations for SQLite and PostgreSQL are separate, as some data types differ between the two. For example, SQLite does not support the bytea data type, so we use the Blob data type instead, whereas PostgreSQL uses BYTEA. Another example is of SERIAL data type which SQlite does not support and has INTEGER PRIMARY KEY AUTOINCREMENT in replacement
Migrations for SQLite are in./migrations/sqlite, whereas migrations for PostgreSQL have been moved to./migrations/postgres.
This PR also updates the CI build workflow to add another job with only the no-storage and testing features, which enables PostgreSQL and consequently disables the SQlite
The follow-up work on the sequencer side will also include a migration for the merklized state tables, as the migrations are in the sequencer repo. This will involve migrating the children_bitvec column from theBit varying (BIT(n))type to BYTEA/JSONB, since SQLite does not support bit strings. However, we need to convert the Bit(n) to bytes before storing it, rather than directly converting it to JSON, as that would take 1 byte for each bit in the bit string.
This change also requires the sequencer binary to be compiled separately for SQLite with the embedded feature for the query service.
This PR enables SQLite support for the query service. Since SQLx supports both PostgreSQL and SQLite, most of the code has been reused. An additional feature,
embedded-db, has been introduced. When enabled, it activates the SQLite code; if not enabled, the PostgreSQL code is used.The migrations for SQLite and PostgreSQL are separate, as some data types differ between the two. For example, SQLite does not support the
byteadata type, so we use theBlobdata type instead, whereas PostgreSQL usesBYTEA. Another example is ofSERIALdata type which SQlite does not support and hasINTEGER PRIMARY KEY AUTOINCREMENTin replacementMigrations for SQLite are in
./migrations/sqlite, whereas migrations for PostgreSQL have been moved to./migrations/postgres.This PR also updates the CI build workflow to add another job with only the
no-storageandtestingfeatures, which enables PostgreSQL and consequently disables the SQliteThe follow-up work on the sequencer side will also include a migration for the merklized state tables, as the migrations are in the sequencer repo. This will involve migrating the children_bitvec column from the
Bit varying (BIT(n))type toBYTEA/JSONB, since SQLite does not support bit strings. However, we need to convert the Bit(n) to bytes before storing it, rather than directly converting it to JSON, as that would take 1 byte for each bit in the bit string.This change also requires the sequencer binary to be compiled separately for SQLite with the embedded feature for the query service.