Examples

CREATE TABLE tenants (
  tenant_id TEXT PRIMARY KEY,
  config_json JSONB NOT NULL,
  enabled BOOLEAN DEFAULT true
);

INSERT INTO tenants VALUES
  ('acme-corp', '{"api_url": "https://acme.api.com", "max_requests": 1000}', true),
  ('globex', '{"api_url": "https://globex.api.com", "max_requests": 500}', true);

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: tenant-configs
  namespace: tenants-system
spec:
  pollInterval: "2m"
  prune: true
  
  database:
    type: postgres
    connectionSecretRef:
      name: postgres-credentials
      namespace: tenants-system
      uriKey: fqdn-uri  # CloudNativePG
  
  query: |
    SELECT tenant_id, config_json
    FROM tenants
    WHERE enabled = true
  
  template: |
    apiVersion: v1
    kind: ConfigMap
    metadata:
      name: tenant-{{ .Row.tenant_id }}-config
      namespace: tenants-system
      labels:
        tenant: {{ .Row.tenant_id | quote }}
    data:
      config.json: {{ .Row.config_json | toJson | quote }}

CREATE TABLE applications (
  app_name TEXT PRIMARY KEY,
  repo_url TEXT NOT NULL,
  target_revision TEXT DEFAULT 'main',
  path TEXT NOT NULL,
  namespace TEXT NOT NULL,
  project TEXT DEFAULT 'default',
  auto_sync BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO applications (app_name, repo_url, path, namespace) VALUES
  ('app-digitaltwins-env-dbqr-test', 
   'https://github.com/my-org/manifests',
   'apps/digitaltwins/test',
   'digitaltwins-test');

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: argocd-apps
  namespace: argocd
spec:
  pollInterval: "1m"
  prune: true
  
  database:
    type: postgres
    connectionSecretRef:
      name: postgres-creds
      uriKey: fqdn-uri
  
  query: |
    SELECT app_name, repo_url, target_revision, path, namespace, project, auto_sync
    FROM applications
    WHERE created_at IS NOT NULL
  
  template: |
    apiVersion: argoproj.io/v1alpha1
    kind: Application
    metadata:
      name: {{ .Row.app_name }}
      namespace: {{ .Metadata.Namespace }}
    spec:
      project: {{ .Row.project }}
      source:
        repoURL: {{ .Row.repo_url | quote }}
        targetRevision: {{ .Row.target_revision | quote }}
        path: {{ .Row.path | quote }}
      destination:
        server: https://kubernetes.default.svc
        namespace: {{ .Row.namespace }}
      {{- if .Row.auto_sync }}
      syncPolicy:
        automated:
          prune: true
          selfHeal: true
      {{- end }}

CREATE TABLE services (
  service_name TEXT PRIMARY KEY,
  image TEXT NOT NULL,
  replicas INT DEFAULT 1,
  port INT DEFAULT 80,
  env_vars JSONB DEFAULT '{}',
  namespace TEXT NOT NULL
);

INSERT INTO services VALUES
  ('api-server', 'nginx:1.25', 2, 8080, '{"LOG_LEVEL": "info"}', 'production');

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: dynamic-deployments
  namespace: apps-system
spec:
  pollInterval: "5m"
  prune: false  # Keep deployments even if removed from DB
  
  database:
    type: postgres
    connectionSecretRef:
      name: db-secret
      uriKey: fqdn-uri
  
  query: |
    SELECT service_name, image, replicas, port, env_vars, namespace
    FROM services
  
  template: |
    apiVersion: apps/v1
    kind: Deployment
    metadata:
      name: {{ .Row.service_name }}
      namespace: {{ .Row.namespace }}
    spec:
      replicas: {{ .Row.replicas }}
      selector:
        matchLabels:
          app: {{ .Row.service_name }}
      template:
        metadata:
          labels:
            app: {{ .Row.service_name }}
        spec:
          containers:
          - name: {{ .Row.service_name }}
            image: {{ .Row.image | quote }}
            ports:
            - containerPort: {{ .Row.port }}
            env:
            {{- range $key, $value := (.Row.env_vars | fromJson) }}
            - name: {{ $key }}
              value: {{ $value | quote }}
            {{- end }}

CREATE TABLE deployments (
  deployment_name TEXT PRIMARY KEY,
  image TEXT NOT NULL,
  namespace TEXT NOT NULL,
  ready_replicas INT DEFAULT 0,
  last_updated TIMESTAMP DEFAULT NOW()
);

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: deployment-sync
  namespace: sync-system
spec:
  pollInterval: "1m"
  prune: true
  
  database:
    type: postgres
    connectionSecretRef:
      name: db-creds
      uriKey: fqdn-uri
  
  query: |
    SELECT deployment_name, image, namespace
    FROM deployments

  template: |
    apiVersion: apps/v1
    kind: Deployment
    metadata:
      name: {{ .Row.deployment_name }}
      namespace: {{ .Row.namespace }}
    spec:
      replicas: 2
      selector:
        matchLabels:
          app: {{ .Row.deployment_name }}
      template:
        metadata:
          labels:
            app: {{ .Row.deployment_name }}
        spec:
          containers:
          - name: app
            image: {{ .Row.image | quote }}
  
  # Update database with current ready replicas
  statusUpdateQueryTemplate: |
    UPDATE deployments
    SET ready_replicas = {{ .Status.readyReplicas | default 0 }},
        last_updated = NOW()
    WHERE deployment_name = {{ .Metadata.Name | squote }}
      AND namespace = {{ .Metadata.Namespace | squote }}

CREATE TABLE configs (
  config_id TEXT PRIMARY KEY,
  data JSONB NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Trigger to update timestamp
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER configs_updated_at
BEFORE UPDATE ON configs
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: efficient-configs
  namespace: default
spec:
  pollInterval: "30s"  # Check frequently but only query if changed
  prune: true
  
  database:
    type: postgres
    connectionSecretRef:
      name: db-secret
      uriKey: fqdn-uri
  
  changeDetection:
    enabled: true
    tableName: "configs"
    timestampColumn: "updated_at"
  
  query: |
    SELECT config_id, data
    FROM configs
  
  template: |
    apiVersion: v1
    kind: ConfigMap
    metadata:
      name: config-{{ .Row.config_id }}
    data:
      config.json: {{ .Row.data | toJson | quote }}

apiVersion: konnektr.io/v1alpha1
kind: DatabaseQueryResource
metadata:
  name: cross-namespace-secrets
  namespace: secrets-controller
spec:
  pollInterval: "5m"
  prune: true
  
  database:
    type: postgres
    connectionSecretRef:
      name: db-creds
      uriKey: fqdn-uri
  
  query: |
    SELECT secret_name, target_namespace, secret_data
    FROM shared_secrets
    WHERE enabled = true
  
  template: |
    apiVersion: v1
    kind: Secret
    metadata:
      name: {{ .Row.secret_name }}
      namespace: {{ .Row.target_namespace }}  # Target namespace from database
    type: Opaque
    stringData:
      data: {{ .Row.secret_data | quote }}