Shadow Endpoints with database calls - more examples

[half-metal]

Hi, I think the docs could use some updating to discuss with examples how shadow endpoints can be used for directly calling a database without having any backend server, which is fantastic and allows greater flexibility getting data. I was hoping Sveltekit would have a solution for this one day! There is a very generic example on docs that could use another more detailed example https://kit.svelte.dev/docs/routing#endpoints. I actually got stuck trying to make this use case work and was about to use Svemix after reading endless forums and getting errors like 'ReferenceError: process is not defined at node_modules/oracledb/lib/util.js (http://localhost:4000/node_modules/.vite/' from Vite. But I finally got this working, and I wanted to share in case someone else ends up on endless forums with unclear examples.

Let's say you have the following files: queryDatabase.js > data-result.js > data-result.svelte where we want the queryDatabase results to be displayed on the data-result.svelte page. Since data-result.js has the same name as data-result.svelte it will work as a shadow endpoint that passes properties to the page. For this example, I am using oracledb for querying from an oracle database. Instead of using a module like oracledb for Oracle, you could use pg for Postgresql or equivalent for other databases like MongoDb and beyond. When running just the data-result.js from code runner you should see the query result with data in case you are troubleshooting.

So let's say you query with query-database.js below (where I've removed db host connection, user, pw info):

import oracledb from 'oracledb';
let result
try {
    oracledb.initOracleClient({libDir: 'C:\\oracle\\instant-client\\instantclient_21_3'});
  } catch (err) {
    console.error('Whoops!');
    console.error(err);
    //process.exit(1);
  }

oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;

const mypw = '**user removed**'
const myuser = '**pw removed**'

export default async function sendOracleResult() {

  let connection;

  try {
    connection = await oracledb.getConnection( {
      user          : myuser,
      password      : mypw,
      connectString : "**ip:port**/**service**"
    });

    result = await connection.execute(
      `SELECT *
       FROM schema_name.settings
       where param_name like '%field%'`,
    );
    console.log(result.rows);
  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
  return result
}

then data-result.js will import and use queryDatabase with:

//import queryDatabase from '$lib/queryDatabase.js'
import queryDatabase from '../lib/queryDatabase.js'

export async function get() {
    let result = await queryDatabase()
    console.log('result is', result)
    return {
        body:{
            result: JSON.stringify(result),
            }
    };
}
get()

and then we have data-result.svelte route able to display that data when the promise is resolved via the shadow endpoint data-result.js, which can take some time to load depending on the query and connection.

<script> 
//this file will get data from shadow endpoint
		export let result
</script>

<div class="content">
	<p>{result}</p>
	<pre>this gives data above</pre>
</div>

Now if you view this page you should see your data from your database to your Sveltekit client webpage with no extra server.

[half-metal]

I didn't realize this will prevent a Page with Endpoints from loading until the data is loaded which if the database response is slow, your page loading will be slow #4458