Multiple database types support

[lgtti]

Hi all,

I have a microservice backend that must support different types of database (postgres, mariadb and sqlite). The problem is sqlite, because it doesn't support DATE/TIME fields.

This is the SQlite table definition:

CREATE TABLE TEMPLATE (
      NAME              TEXT PRIMARY KEY,
      CREATED_AT        TEXT NOT NULL,
      CREATED_BY        TEXT NOT NULL,
      UPDATED_AT        TEXT,
      UPDATED_BY        TEXT,
      TENANT_ID         TEXT NOT NULL,
      PROJECT_ID        TEXT NOT NULL,
      CONTENT           TEXT NOT NULL,
      DEFAULT_END_DATE  TEXT,

      UNIQUE (TENANT_ID, PROJECT_ID, NAME)
)WITHOUT ROWID;

and this is the same for Postgres

CREATE TABLE TEMPLATE (
      NAME              serial not null primary key,
      CREATED_AT        timestamp with time zone not null,
      CREATED_BY        varchar(256) NOT NULL,
      UPDATED_AT        timestamp with time zone,
      UPDATED_BY        varchar(256),
      TENANT_ID         varchar(256) NOT NULL,
      PROJECT_ID        varchar(256) NOT NULL,
      CONTENT           varchar(256) NOT NULL,
      DEFAULT_END_DATE  timestamp with time zone
);

As you can see, some fields are degined as timestamp in postgres and text in sqlite.

When I generate models for each database, the go structure are different:

Sqlite

type Template struct {
    Name           string `sql:"primary_key"`
    CreatedAt      string
    CreatedBy      string
    UpdatedAt      *string
    UpdatedBy      *string
    TenantID       string
    ProjectID      string
    Content        string
    DefaultEndDate *string
}

Postgres:

type Template struct {
    Name           int32 `sql:"primary_key"`
    CreatedAt      time.Time
    CreatedBy      string
    UpdatedAt      *time.Time
    UpdatedBy      *string
    TenantID       string
    ProjectID      string
    Content        string
    DefaultEndDate *time.Time
}

Now, the question: How can I write a query definition using go-jet syntax if I have different models?

[houten11]

The problem is sqlite, because it doesn't support DATE/TIME fields.

You can still use standard sql types as a hint: https://www.sqlite.org/datatypes.html.

This sqlite table should generate the same model as postgres.

CREATE TABLE TEMPLATE (
      NAME              INTEGER PRIMARY KEY,
      CREATED_AT        TIMESTAMP NOT NULL,
      CREATED_BY        TEXT NOT NULL,
      UPDATED_AT        TIMESTAMP,
      UPDATED_BY        TIMESTAMP,
      TENANT_ID         TEXT NOT NULL,
      PROJECT_ID        TEXT NOT NULL,
      CONTENT           TEXT NOT NULL,
      DEFAULT_END_DATE  TIMESTAMP ,

      UNIQUE (TENANT_ID, PROJECT_ID, NAME)
)WITHOUT ROWID;

Now, the question: How can I write a query definition using go-jet syntax if I have different models?

Not sure what you are trying to achieve. Are you trying to use a single query and single model to update all three databases?

[lgtti]

Not sure what you are trying to achieve. Are you trying to use a single query and single model to update all three databases?

No, my microservice must be able to run in different environment and customers. Every customer is able to install the DB they want (mariadb, mysql, postgres, sqlite in the 'edge computing' installation mode).

I don't want to repeat sql queries for each db version and dialect (for example using sqlx) and I need complex query composition (I cannot use gorm for example).

jet, with its query language, is perfect to achieve the result but I need the same model for each database type :)

I was thinking that may be useful to specify some tags or information regarding the model generation, such as forcing some column types. But i cannot find documentation for this in jet.

[go-jet]

I don't want to repeat sql queries for each db version and dialect (for example using sqlx) and I need complex query composition (I cannot use gorm for example). jet, with its query language, is perfect to achieve the result but I need the same model for each database type :)

Although it is possible to reuse the same model types, I don't think you can always avoid writing different sql queries for different databases. Unless sql queries are plain simple. Sooner or later you'll encounter some dialect differences, which will force you to write different queries.

I was thinking that may be useful to specify some tags or information regarding the model generation, such as forcing some column types. But i cannot find documentation for this in jet.

Hmm, you right, sqlite mapping is missing in the documentation - https://github.com/go-jet/jet/wiki/Model. Use postgres maping for now. I don't think there is any difference to sqlite.