A SurrealDB driver with export and import capability

SurrealDB_driver_with_export_and_import

A (very hacky!) surreal db driver with export/import capability!
I had a lot of fun making this! It's for an angular project I'm working on as a hobby.
I am by no means a professional, so feedback and suggestions are welcome. 
Let me know if you have any questions!

After injecting the service:
constructor(private surreal: SurrealService)

You can construct queries like this, returned as an observable: (This is just a simple example)
this.surreal
  .from('tablename')
  .where('age > 10')
  .orderBy([{ field: 'name', direction: 'ASC'}])
  .limit(1)
  .asObservable()
  .subscribe(values => console.log(values))

It also constructs transactions, returned as an observable:
this.surreal.trans()
  .set(eventObj.id, eventObj)
  .var(
    '$last',
    this.sur
      .doc('timeline', 'timeOfTimelineEvent')
      .where(`typeOfTimelineEvent = "end" && user = ${user.id}`)
      .orderBy({field: 'timeOfTimelineEvent', direction: 'DESC'})
      .limit(1)
      .asString()
  )
  .merge(user.id, '{time: $last, lastUpdated: time::now()}')
  .run$()

The real exciting thing for me to make was a way to export/import my database from code,
and with SurrealDB it was really simple. It basically just takes the info from the 'INFO FOR DB' command
and uses the values to generate a query to return all the definitions and records into a single json file.

I also set up a google cloud function to make daily backups and save them to firebase storage, neatly divided into folders by month.

y. surrealdb-service.ts

import {Injectable, Injector} from '@angular/core'
import {map, switchMap, take} from 'rxjs/operators'
import {combineLatest, EMPTY, from, Observable, of, ReplaySubject, Subscription} from 'rxjs'
import Surreal from 'surrealdb.js'
import * as dayjs from 'dayjs'
import {saveAs} from 'file-saver'

type queryVar = `${'$'}${string}`

interface ISubfield {
  parentField: string
  fieldType: 'arr' | 'obj'
  childFields: string[]
}

export interface ITableDef {
  name: string
  def: string
  records: any[]
}

export interface IOrder {
  field: string
  direction: 'ASC' | 'DESC'
}

export interface IDatabase {
  tables: {name: string; def: string; records: any[]}[]
  scopes: string[]
}

@Injectable({
  providedIn: 'root'
})
export class SurrealService {
  db = new Surreal('endpoint-here')
  
  constructor() {}

  async rootSignIn(password: string, ns: string, db: string) {
    await this.db.signin({user: 'root', pass: password})
    await this.db.use(ns, db)
  }

  async signIn(id: string, namespace: string, database: string, scope: string) {
    let token = await this.db.signin({NS: namespace, DB: database, SC: scope, id})
    await this.db.use(namespace, database)
  }

  signup() {}

  // Creates a random id
  createId(table: string) {
    return table + ':' + crypto.randomUUID().replace(/-/g, '').substring(0, 20)
  }

  // Returns a new instance of the query builder class
  from(tableOrId: string = '', fields: string | string[] = ['*']) {
    return new query(tableOrId, fields, this.db)
  }

  // Returns a new instance of the query builder class and configures it to return a single record
  doc(tableOrId: string = '', fields: string | string[] = ['*']) {
    return new query(tableOrId, fields, this.db, 'obj')
  }

  // Returns a new instance of the transaction builder
  trans() {
    return new transaction(this.db)
  }

  // Runs a query and maps the response to just the result
  run$<T>(queryText) {
    return from(this.db.query(queryText)).pipe(
      map((result) => {
        return result[0]['result']
      }),
      take(1)
    ) as Observable<T>
  }

  // Runs a query without mapping the response
  runVanillaCommand$<T>(queryText) {
    return from(this.db.query(queryText)).pipe(take(1)) as Observable<T>
  }

  // Sets or overwrites a record
  set$(id: string, object: any) {
    return from(this.db.update(id, object).catch((err) => console.log(err))).pipe(take(1))
  }

  // Merges contents into a record
  merge$(id: string, data: any): Observable<any> {
    if (!id.includes(':')) {
      // id shouldn't be a table.
      // @ts-ignore
      return
    }
    return from(this.db.change(id, data).catch((err) => console.log(err))).pipe(take(1))
  }

  // Moves a record to another table (the default is the 'trash' table which should be defined before running this...)
  move$(id: string, toTable: string = 'trash', userId: string) {
    return this.run$(
      `
      BEGIN TRANSACTION;  
      UPDATE ${id} SET moveInfo = { "oldId": ${id}, "timeMoved": time::now(), "movedBy": ${userId}};
      LET $doc = (SELECT * FROM ${id}); 
      INSERT INTO ${toTable} $doc;
      DELETE ${id};
      COMMIT TRANSACTION;
      `
    )
  }

  delete(id) {
    this.run$(`DELETE ${id}`).subscribe()
  }

  async export(fileName: `${string}${'.json'}`, ns: string, db: string) {
    await this.db.use(ns, db)
    // Get info to parse
    this.run$('INFO FOR DB')
      .pipe(
        map((info) => {
          // Tables (no records for now):
          // For each table defined in 'info', map the name and definition to an object
          let tables = Object.entries(info['tb']).map(([key, value]: [string, string]) => {
            return {name: key, def: value, records: []}
          })
          // Scope definition:
          // For each scope defined in 'info', map the definition to a string
          let scopeDefs = Object.values(info['sc'] as string)

          // return the mapped tables and scopes as a single object (referred to as 'db' further down)
          return {tables: tables, scopes: scopeDefs} as IDatabase
        }),
        switchMap((db) => {
          // create a blank transaction
          let transaction = this.trans()
          
          // For each table in the new db object, add a line to the transaction: (SELECT * FROM [tablename];)
          db.tables.forEach((table: ITableDef) => {
            transaction.query('SELECT * FROM ' + table.name)
          })
          
          // send the db object and transaction object down the stream as observables
          return combineLatest([of(db), transaction.run$()])
        }),
        map(([db, allTablesAndRecords]) => {
          // insert records arrays retrieved from the transactions into each table object
          db.tables = db.tables.map((table, index) => {
            return {...table, records: allTablesAndRecords[index].result as any[]}
          })
          return db
        }),
        take(1)
      )
      .subscribe((db) => {
        // download the file in the browser
        let blob = new Blob([JSON.stringify(db)], {type: 'application/json'})
        saveAs(blob, fileName)
      })
  }

  async import(json: string, ns: string, db: string) {
    await this.db.use(ns, db)
    // Make sure json is valid
    if (this.isJson(json)) {
    
      // convert json to IDatabase object
      let db = JSON.parse(json) as IDatabase
      
      // Create new empty transaction builder
      let transaction = this.trans()
      
      // Create lines in transaction to define scopes in new database
      db.scopes.forEach((scopeDef) => transaction.query(scopeDef))
      
      // Create lines in transaction to define tables in new database
      db.tables.forEach((table) => transaction.query(table.def))
      
      // Create lines in transaction to insert records into new database
      db.tables.forEach((table) => transaction.query(`INSERT INTO ${table.name} ${JSON.stringify(table.records)};`))
      
      // Run the transaction to import the data!
      transaction.run$().subscribe()
    
    }
  }

  isJson(str) {
    try {
      JSON.parse(str)
    } catch (e) {
      return false
    }
    return true
  }

class query {
  #tableOrIdText = ''
  #fieldsArr: string | string[] = ['*']
  #subFieldsArr: ISubfield[] = []
  #searchText = ''
  #fieldsToSearch: string | string[] = ''
  #orderByArr: IOrder | IOrder[]
  #whereArr: string[] = []
  #fetchArr: string | string[] = ''
  #limitNum: number = undefined
  #startNum: number = undefined
  #log: string
  #type: 'obj' | 'arr'
  #return: string

  constructor(
    tableOrId: string,
    fields: string | string[] = ['*'],
    private db: Surreal,
    type: 'obj' | 'arr' = 'arr'
  ) {
    this.#tableOrIdText = tableOrId
    this.#fieldsArr = fields
    this.#type = type
  }

  // constructs a subquery when you only want to return specific fields from a linked record. 
  // For example, it constructs the following part between the parenthesis:
  // SELECT id, (SELECT first, last FROM $parent.name LIMIT 1) FROM user:123
  subField(parentField: string, fieldType: 'arr' | 'obj', childFields: string[] = ['*']) {
    this.#subFieldsArr.push({parentField, fieldType, childFields})
    return this
  }

  // Search text
  search(text: string, fields: string | string[]) {
    this.#searchText = text
    this.#fieldsToSearch = fields
    return this
  }

  // Make sure to put double quotes around strings!
  where(textArr: string | string[]) {
    // If called multiple times, you need to build the array out.
    if (this.#whereArr.length === 0) {
      if (Array.isArray(textArr)) {
        this.#whereArr = textArr
      } else if (textArr) {
        this.#whereArr.push(textArr)
      }
    } else {
      if (Array.isArray(textArr)) {
        this.#whereArr.push(...textArr)
      } else if (textArr) {
        this.#whereArr.push(textArr)
      }
    }
    return this
  }

  orderBy(order: IOrder | IOrder[]) {
    this.#orderByArr = order
    return this
  }

  limit(number: number) {
    this.#limitNum = number
    return this
  }

  start(number: number) {
    this.#startNum = number
    return this
  }

  fetch(fields: string[] | string) {
    this.#fetchArr = fields
    return this
  }

  return(text: string) {
    this.#return = text
    return this
  }

  log(componentLocation: string) {
    this.#log = componentLocation
    return this
  }

  asString(): string {
    return this.#generateQuery()
  }

  asObservable<T>() {
    return from(this.db.query(this.#generateQuery())).pipe(
      map((result) => {
        return this.#type === 'arr' ? result[0]['result'] : result[0]['result'][0]
      }),
      take(1)
    ) as Observable<T>
  }

  subscribe<T>(fn: any): Subscription {
    return from(this.db.query(this.#generateQuery())).pipe(
      map((result) => {
        return this.#type === 'arr' ? result[0]['result'] : result[0]['result'][0]
      }),
      map(fn),
      take(1)
    )
    .subscribe()
  }

  #createList(array: string[], separator: string): string {
    return array.reduce((wholeString, field, i) => {
      // If there are multiple fields, you need to add a comma in front of the second one onwards.
      let comma = i > 0 ? separator : ''
      return wholeString + comma + field
    }, '')
  }

  #convertToStringArray(thing: any): string[] {
    return typeof thing === 'string' ? [thing] : thing
  }

  #generateQuery() {
    return this.#generator(
      this.#tableOrIdText,
      this.#fieldsArr,
      this.#subFieldsArr,
      this.#searchText,
      this.#fieldsToSearch,
      this.#whereArr,
      this.#orderByArr,
      this.#limitNum,
      this.#startNum,
      this.#fetchArr,
      this.#log,
      this.#type,
      this.#return
    )
  }

  #generator<T>(
    tableOrId: string,
    fields: string | string[] = ['*'],
    subFields: ISubfield[] = [],
    searchText: string = '',
    fieldsToSearch: string | string[] = [],
    where: string | string[] = [],
    orderBy: IOrder | IOrder[],
    limit: string | number = '',
    start: string | number = '',
    fetchFields: string | string[] = [],
    log: string,
    type: 'obj' | 'arr',
    _return: string
  ) {
    fields = this.#convertToStringArray(fields)
    fieldsToSearch = this.#convertToStringArray(fieldsToSearch)
    where = this.#convertToStringArray(where)
    fetchFields = this.#convertToStringArray(fetchFields)
    let fieldsQuery = this.#createList(fields, ', ')

    subFields.forEach((field) => {
      let txt1 = field.fieldType === 'arr' ? '.*' : ''
      let txt2 = field.fieldType === 'arr' ? '' : 'LIMIT 1'
      fieldsQuery =
        fieldsQuery +
        `, (SELECT ${this.#createList(field.childFields, ', ')} FROM $parent.${field.parentField}${txt1} ${txt2}) AS ${
          field.parentField
        }`
    })

    let searchQuery = ''
    if (searchText && fieldsToSearch.length > 0) {
      searchQuery =
        fieldsToSearch.reduce((wholeString, field, i) => {
          return wholeString + `, string::trim(string::lowercase(${field}))`
        }, ' WHERE string::join(" "') + `) CONTAINS string::lowercase('${searchText}')`
    }

    let whereQuery = ''
    if (where[0] !== '' && where.length > 0) {
      whereQuery =
        searchQuery === ''
          ? ' WHERE (' + this.#createList(where, ' AND ') + ')'
          : ' AND (' + this.#createList(where, ' AND ') + ')'
    }

    let orderByQuery = ''
    if (orderBy) {
      if (orderBy['field']) {
        orderByQuery = ' ORDER BY ' + orderBy['field'] + ' ' + orderBy['direction']
      } else {
        orderByQuery =
          ' ORDER BY ' +
          this.#createList(
            (orderBy as IOrder[]).map((ord) => ord.field + ' ' + ord.direction),
            ', '
          )
      }
    }

    if (limit === 0) {
      limit = ''
    } else if (limit) {
      limit = ' LIMIT ' + limit
    }
    if (type === 'obj') limit = ' LIMIT 1'
    if (start) {
      start = ' START ' + start
    } else {
      start = ''
    }

    let fetchQuery = ''
    if (fetchFields[0] !== '' && fetchFields.length > 0) {
      fetchQuery = ' FETCH ' + this.#createList(fetchFields, ', ')
    }

    let returnQuery = ''
    if (_return) {
      returnQuery = _return
    }

    const queryText =
      'SELECT ' +
      fieldsQuery +
      ' FROM ' +
      tableOrId +
      searchQuery +
      whereQuery +
      orderByQuery +
      limit +
      start +
      fetchQuery +
      returnQuery +
      ';'

    if (log) console.log(`(${log}) ${queryText}`)

    return queryText
  }
}

class transaction {
  #allText = `BEGIN TRANSACTION;`
  #log = ''
  constructor(private db: Surreal) {}

  #addLine(line: string) {
    this.#allText =
      this.#allText +
      `
    ${line}`
  }

  var(name: queryVar, query: string) {
    // If query builder is used, it will add a semicolon at the end, but that should be removed in a subquery
    if (query[query.length - 1] === ';') {
      query = query.substring(0, query.length - 1)
    }
    this.#addLine(`LET ${name} = (${query});`)
    return this
  }

  set(id: string, thing: object | string) {
    if (typeof thing === 'string') {
      this.#addLine(`UPDATE ${id} CONTENT ${thing};`)
    } else {
      this.#addLine(`UPDATE ${id} CONTENT ${JSON.stringify(thing)};`)
    }
    return this
  }

  merge(id: string, thing: object | string) {
    if (!id.includes(':')) {
      // id shouldn't be a table.
      // @ts-ignore
      return
    }
    if (typeof thing === 'string') {
      this.#addLine(`UPDATE ${id} MERGE ${thing};`)
    } else {
      this.#addLine(`UPDATE ${id} MERGE ${JSON.stringify(thing)};`)
    }

    return this
  }

  if(condition: string, thenQuery: string, elseQuery?: string) {
    this.#addLine('IF ' + condition)
    if (thenQuery[thenQuery.length - 1] === ';') {
      thenQuery = thenQuery.substring(0, thenQuery.length - 1)
    }
    this.#addLine('THEN (' + thenQuery + ')')
    if (elseQuery) {
      if (elseQuery[elseQuery.length - 1] === ';') {
        elseQuery = elseQuery.substring(0, elseQuery.length - 1)
      }
      this.#addLine('ELSE (' + elseQuery + ')')
    }
    this.#addLine('END;')
    return this
  }

  query(text: string) {
    this.#addLine(text + ';')
    return this
  }

  move(docId: string, userId: string) {
    let now = dayjs().format('YYYY-MM-DDTHH:mm:ss')
    this.#addLine(`UPDATE ${docId} SET moveInfo = { "oldId": ${docId}, "timeMoved": "${now}", "movedBy": ${userId}};
    LET $doc = (SELECT * FROM ${docId}); 
    INSERT INTO trash $doc;
    DELETE ${docId};`)
    return this
  }

  define(what: 'SCOPE' | 'TABLE', name: string, definition: string) {
    this.#addLine(`DEFINE ${what} ${name} ${definition};`)
    return this
  }

  log(text: string) {
    this.#log = text
    return this
  }

  test() {
    this.#allText =
      this.#allText +
      `
    COMMIT TRANSACTION;`
    console.log(this.#allText)
  }

  run$() {
    this.#allText =
      this.#allText +
      `
    COMMIT TRANSACTION;`
    if (this.#log) {
      console.log('(' + this.#log + ')', this.#allText)
    }
    return from(this.db.query(this.#allText)).pipe(
      take(1)
    )
  }
}

z. cloud-function-backup.ts

import {combineLatest, firstValueFrom, from, map, Observable, of, switchMap, take} from 'rxjs';
import * as functions from 'firebase-functions';
import 'firebase-functions';
import * as stream from 'stream';
import * as dayjs from 'dayjs';

const {storage, db} = require('./../admin');

exports.backup = functions
  .runWith({secrets: ['secret-here']})
  .pubsub.schedule('00 12 * * *')
  .timeZone('America/New_York')
  .onRun(async () => {
    await exportDatabase('test', 'test');
  });

interface ITableDef {
  name: string;
  def: string;
  records: any[];
}
interface IDatabase {
  tables: {name: string; def: string; records: any[]}[];
  scopes: string[];
}

async function exportDatabase(namespace: string, database: string) {
  await db.signin({
    user: 'root',
    pass: process.env.secret-here
  });
  await db.use(namespace, database);

  const jsonString = await getDatabaseAsJson$();

  const folder = 'database-backups/' + dayjs().format('YYYY-MM') + '/';
  const fileName = namespace + '-' + database + '-backup-' + dayjs().format('YYYY-MM-DD');
  const filePath = folder + fileName;

  const passthroughStream = new stream.PassThrough();
  passthroughStream.write(jsonString);
  passthroughStream.end();
  passthroughStream.pipe(storage.bucket('bucket-name-here').file(filePath).createWriteStream());
}

async function getDatabaseAsJson$(): Promise<string> {
  // Get info to parse
  return firstValueFrom(
    query$('INFO FOR DB').pipe(
      map((info: any) => {
        // Tables (no records for now):
        // For each table defined in 'info', map the name and definition to an object
        const tables = Object.entries(info['tb']).map(([key, value]) => {
          return {name: key, def: value, records: []};
        });
        // Scope definition:
        // For each scope defined in 'info', map the definition to a string
        const scopeDefs = Object.values(info['sc'] as string);

        // return the mapped tables and scopes as a single object (referred to as 'db' further down)
        return {tables: tables, scopes: scopeDefs} as IDatabase;
      }),
      switchMap((db) => {
        // create a blank transaction
        const trans = new Transaction();
        db.tables.forEach((table: ITableDef) => {
          // For each table in the new db object, add a line to the transaction: ('SELECT * FROM [tablename];')
          trans.query('SELECT * FROM ' + table.name);
        });
        // send the db object and transaction object down the stream
        return combineLatest([of(db), trans.run$() as Observable<any>]);
      }),
      map(([db, allTablesAndRecords]) => {
        // insert records arrays retrieved from the transacations into each table object
        db.tables = db.tables.map((table, index) => {
          return {...table, records: allTablesAndRecords[index].result as any[]};
        });
        return JSON.stringify(db);
      }),
      take(1)
    )
  );
}

function query$(queryText: string) {
  return from(db.query(queryText)).pipe(
    map((result: any) =>  result[0]['result']),
    take(1)
  );
}

class Transaction {
  #allText = 'BEGIN TRANSACTION;';

  #addLine(line: string) {
    this.#allText =
      this.#allText +
      `
    ${line}`;
  }

  query(text: string) {
    this.#addLine(text + ';');
    return this;
  }

  run$() {
    this.#allText =
      this.#allText +
      `
    COMMIT TRANSACTION;`;

    return from(db.query(this.#allText));
  }
}