Support for other databases

[ImVexed]

After trying to spin up the example project with postgres, I was met with the error: panic: cannot warm up SecondLevelCache: cannot warm up SecondLevelCache. table is user_logins: failed show create table : failed to execute 'SHOW CREATE TABLEuser_logins': pq: syntax error at or near "CREATE"

Upon looking inside of first_lelvel_cache.go it seems showCreateTable unconditionally executes SHOW CREATE TABLE which seems to be a mySQL specific syntax.

From what I can gather, it looks like you're attempting to dump the DDL of a given table. If so, something like:

SELECT *
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'user_logins'
ORDER BY ordinal_position;

Will give you:

table_catalog|table_schema|table_name |column_name    |ordinal_position|column_default                         |is_nullable|data_type             |character_maximum_length|character_octet_length|numeric_precision|numeric_precision_radix|numeric_scale|datetime_precision|interval_type|interval_precision|character_set_catalog|character_set_schema|character_set_name|collation_catalog|collation_schema|collation_name|domain_catalog|domain_schema|domain_name|udt_catalog|udt_schema|udt_name|scope_catalog|scope_schema|scope_name|maximum_cardinality|dtd_identifier|is_self_referencing|is_identity|identity_generation|identity_start|identity_increment|identity_maximum|identity_minimum|identity_cycle|is_generated|generation_expression|is_updatable|
-------------|------------|-----------|---------------|----------------|---------------------------------------|-----------|----------------------|------------------------|----------------------|-----------------|-----------------------|-------------|------------------|-------------|------------------|---------------------|--------------------|------------------|-----------------|----------------|--------------|--------------|-------------|-----------|-----------|----------|--------|-------------|------------|----------|-------------------|--------------|-------------------|-----------|-------------------|--------------|------------------|----------------|----------------|--------------|------------|---------------------|------------|
postgres     |public      |user_logins|id             |               1|nextval('user_logins_id_seq'::regclass)|NO         |bigint                |                        |                      |               64|                      2|            0|                  |             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|int8    |             |            |          |                   |1             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|user_id        |               2|                                       |NO         |bigint                |                        |                      |               64|                      2|            0|                  |             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|int8    |             |            |          |                   |2             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|user_session_id|               3|                                       |NO         |bigint                |                        |                      |               64|                      2|            0|                  |             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|int8    |             |            |          |                   |3             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|login_param_id |               4|                                       |NO         |bigint                |                        |                      |               64|                      2|            0|                  |             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|int8    |             |            |          |                   |4             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|name           |               5|                                       |NO         |character varying     |                     255|                  1020|                 |                       |             |                  |             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|varchar |             |            |          |                   |5             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|created_at     |               6|                                       |NO         |time without time zone|                        |                      |                 |                       |             |                 6|             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|time    |             |            |          |                   |6             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |
postgres     |public      |user_logins|updated_at     |               7|                                       |NO         |time without time zone|                        |                      |                 |                       |             |                 6|             |                  |                     |                    |                  |                 |                |              |              |             |           |postgres   |pg_catalog|time    |             |            |          |                   |7             |NO                 |NO         |                   |              |                  |                |                |NO            |NEVER       |                     |YES         |

However I'm concerned there are more places that mySQL specific SQL is hiding throughout the codebase.

Are there any ongoing efforts to allow more generic queries to be executed to support more databases?

[kanataxa]

@ImVexed

Thanks for reporting!

Rapidash uses queries that assumes MySQL. So, we try to modify code to support postgres.

Thank you!!

[ImVexed]

@kanataxa

Thank you for considering it!

The folks over at SQLBoiler also have an issue tracking the consideration of using rapidash for caching here: https://github.com/volatiletech/sqlboiler/issues/639 just so you're aware.

[nadilas]

@kanataxa Nice work! Could you squeeze in mssql as well? :)

[ImVexed]

@nadilas I believe it currently only supports mysql this issue is to expand it's support to postgres aswell.

[kanataxa]

@nadilas Yes. We try to support using Rapidash with other databases (postgres, mssql ..., etc.) as well as MySQL.