How to write to SQLite database from other fly.io app through LifteFS Proxy

Questions / Help
1 
import axios from 'axios';

// Assuming the other app is called 'app2'
const LITEFS_ENDPOINT = 'https://app.fly.dev.internal:8080';

async function upsertTable(tableName: string, name: string) {
  const query = `INSERT INTO ${tableName}(name) VALUES('${name}') ON CONFLICT(name) DO UPDATE SET name=excluded.name RETURNING id;`;

  const response = await axios.post(LITEFS_ENDPOINT, {
    query: query,
  });
  console.log('Reponses below:');
  console.log(response.data[0].id);

  return response.data[0].id;
}

async function upsertData(job: any) {
  const cityId = await upsertTable('city', job.city);
  const stateId = await upsertTable('state', job.state);
  const countryId = await upsertTable('country', job.country);

  const addressQuery = `INSERT INTO address(cityId, stateId, countryId) VALUES(${cityId}, ${stateId}, ${countryId}) ON CONFLICT(cityId, stateId, countryId) DO UPDATE SET cityId=excluded.cityId, stateId=excluded.stateId, countryId=excluded.countryId RETURNING id;`;

  const response = await axios.post(LITEFS_ENDPOINT, {
    query: addressQuery,
  });

  console.log(response.data[0].id);

  return response.data[0].id;
}

async function go(job: any) {
  await upsertData(job);
}

const job = { city: 'New York', state: 'NY', country: 'USA' };

go(job);

I am trying to write to LiteFS from another app. I am getting the error below when running this:
AxiosError: getaddrinfo ENOTFOUND app.fly.dev.internal

I am not sure what the correct way to go about this. I am trying to write from my webscraper to my epic stack app that has the sqlite db. I would think this would work since litefs has a built in proxy Built-in HTTP Proxy · Fly Docs

I’ve seen some blogs and other information say that we should be able to to do some amount of writes and not have issues through this. It’s a webscraper that is not going superfast so it shouldn’t be anything at most at around 10 requests per second to write to the DB. I believe this shouldn’t be an issue?

2

This address seems incorrect. You can try (assuming it’s called app2):

  • app2.fly.dev if your app has services and public IPs, this will go through our proxy.
  • app2.internal:PORT if you want to bypass the proxy, but then you’d need to figure out your litefs port.
  • app2.flycast:PORT if your app uses auto stop machines you will not be able to use .internal domains when they’re stopped, luckily another customer found how to use flycast to solve this.

For litefs I’d recommend using our proxy (first option).

3

The Sqlite DB is on my remix app. It seems like I should make a route in remix and just have prisma insert this as a transaction and make sure nothing messes up… issue is that I sharp module in my webscraper to format images I plug into aws S3. That would be too much for my remix app to handle.

I’m going to drawing board for this one. So far have an idea of using redis to store info that is gonna be stored by webscraper. Then from there the remix app reads it. When it has to insert image data I make a request back to my webscraper app that has an express server running and then runs the necessary code to put my images in s3. Then gives that back to remix

…It’s a lot but don’t have a choice since my app is used to getting avif’s, webp’s and jpeg’s that I format to send in the DB. And I can’t wait until LiteFS gets a database url that we can send write requests too. Hopefully soon!

Will get back with the code I did to get this rolling.

1 Like
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.