Vitess: Initialize keyspace schema using SQL file

Question

I am using helm and the file 101_initial_cluster.yaml from the Vitess example to setup my initial cluster. The example has a schema initialization using SQL string as shown below:

schema:
        initial: |-
          create table product(
            sku varbinary(128),
            description varbinary(128),
            price bigint,
            primary key(sku)
          );
          create table customer(
            customer_id bigint not null auto_increment,
            email varbinary(128),
            primary key(customer_id)
          );
          create table corder(
            order_id bigint not null auto_increment,
            customer_id bigint,
            sku varbinary(128),
            price bigint,
            primary key(order_id)
          );

I would like to replace this with a file initial: my_initial_keyspace_schema.sql. From the Vitess documentation I can see Vitess does allow for this using ApplySchema -sql_file=user_table.sql user, but I would like to initialize using the helm file.

This would be very helpful as it is very tedious to organize and paste the schema as a string. Tables that depend on others have to be pasted first and the rest follow. Forgetting makes Vitess throw an error.

Answer 1

Welcome to StackOverflow.

I'm afraid there is no out-of-the-box feature to enable initializing DbSchema directly from SQL file in the current state of Vitess Helm chart. You can identify any of its configurable parameters via helm inspect <chart_name> command.

However, you can try customizing it to match your needs in following ways:

1. Stay with ApplySchema {-sql=} mode
   
   but let the SQL schema be slurped from static file as a part of Helm chart template
   (e.g. from static/initial_schema.sql location):

   So just add a piece of control flow code like this one:

{{ if .Values.initialSchemaSqlFile.enabled }}
        {{- $files := .Files }}
        {{- range tuple "static/initial_schema.sql" }}
        {{ $schema := $files.Get . }}
{{- end }}
{{ else }}
   # Default inline schema from Values.topology.cells.keyspaces[0].schema
{{ end }}

Check more on using Helm built-in Objects like File here

2. Use ApplySchema {-sql-file=} mode
   
   Adapt a piece of code here, where vtctlclient command is constructed.
   This would require also to introduce a new Kubernetes Volume object (nfs or Git repo based are good options here), which you could mount on Job, under specific path, from where initial_schema.sql file would be used.