Syncing Anvil Data Tables to MySQL

Anvil Q&A
1

Hello! I’m currently building a front end website that will handle creating accounts, resetting passwords, and managing account attributes for an online game.

The account information is in a MySQL DB and I am able to push code to it with an anvil uplink. It would be awesome if I could leverage the built in Anvil Users Service to make things like password management and email password resets easier and cleaner. Is there a way I can do this and somehow mirror back to my DB from the Anvil Data Table? If not, does anyone know any good Python libraries for handling things like PW resets?

2

Is your front end site built with Anvil?

Any reason why you’re not using the Data Tables to store account info instead of MySQL?

1 Like
3

The Backend Server game client also authenticates with that SQL DB Table. The front end server just gives players an easier way to register and manage their account vs. the clunky game client.

Making the game client authenticate to Anvil is an interesting approach, but would probably be harder, as I am not as familiar with C++.

4

If I understand correctly, it sounds like you have two apps:

  • The game, linked to a MySQL db with it’s own ‘Users’ table
  • The Anvil app

You want users to create, update, manage accounts in the Anvil app that’ll end up syncing with the ‘Users’ table in the MySQL db so that users can then log in through the game client to play.

Is that right?

1 Like
5

That is correct! Appreciate the responses!

I am able to use an Anvil Uplink to push SQL queries to the MySQL db, but for things like password resets over email, it would be a lot easier and cleaner to leverage the Anvil User Service and mirror from that Data Table. Sorry if I am overcomplicating this.

6

Ah I see.

Sounds like you either want a periodic sync or trigger-based sync between the Anvil User’s table and the MySQL database. This assumes the users are using simple email/password for logins - more variations create complications here. You’ll want to look into a custom login flow (Anvil Docs | Custom user authentication) to set up these triggers.

But… I’d avoid doing this. Passing sensitive data like this between apps is not something I’d do without speaking to an expert.

I’m confused about what you’re doing with uplink. Uplink is used to connect Anvil to Python code running elsewhere. Pushing queries to MySQL can be done in a regular Anvil server function.

My recommendation is you figure out how to implement SSO with your game either using Anvil’s user service as the identity provider or using a provider like Auth0. If your game software doesn’t have APIs for this, I’d try to create them if possible. It beats the workaround you’re trying to do.

2 Likes
7

I’m guessing It is much easier to set up a localhost only MySQL server that has no external exposure (other than through the webserver) and connect to it via uplink, than connecting directly to it using credentials with write access from an external source. Just another reason why Anvil is great.

1 Like
8

That was my thought as well. Building an API endpoint is an interesting idea, but leveraging the anvil uplink and having the queries live in local python scripts seems way easier.

I guess I can explore if there are any security concerns using the anvil uplink, as it would pass the username and password arguments.

9

One way to do it is find out what kind of password hashing your game uses and replicate it on the anvil side, this way you are sending an already hashed password to be inserted directly into the MySQL database.

Here is an example of doing that same kind of thing with anvil, and the anvil users service:

import bcrypt

def make_new_user_pass(input_password):
    hashed = bcrypt.hashpw(input_password.encode(), bcrypt.gensalt())

    return hashed.decode('utf-8')

@anvil.server.callable
def change_current_user_password(new_pass):
    user_row = anvil.users.get_user()

    user_row['password_hash'] = make_new_user_pass(new_pass)

Also, check into the uplink docs, but I am pretty sure the uplink is end-to-end encrypted. Still, I wouldn’t send passwords “in the clear” if I could avoid it.

1 Like