Re: How to import a database completely?

Thulium · 02-14-2025 02:01 AM

Dear Appsheeters : )

I found, that there's no way to use an ER diagram for modelling a database in appsheet.
Please correct me if this is wrong.

So I have to use another tool for modelling.

How can I import a complete SQL database into appsheet?

I'm only interested in a method, which allows a complete import of all informations. Fore example: CSV does not allow that.

If an import is impossible:
Do you recommend integrating an external SQL database into appsheet?
Which database is recommended?

Thank you : )

JeeHill

Hey @Thulium

Yeah, AppSheet doesn’t have an ERD tool for database modeling, so you’ll need something like Lucidchart or Draw.io.

For importing a full SQL database, there’s no direct way to do it, including schema and relationships. You’d have to manually create the tables in AppSheet and maybe import data via CSV, but that’s limited.

If you want to keep your full database structure, the best option is to connect an external SQL database instead. AppSheet supports SQL Server, MySQL, and PostgreSQL, and services like Google Cloud SQL, Azure SQL, or AWS RDS work well.

So if you’re looking for a smooth setup, linking your existing SQL database is the way to go. Hope that helps !

Jee

Thulium

Hi @JeeHill

Thanks for your helpful answer. Ok, I have to connect an external SQL database.

Can you recommend a database?

Is it correct, that we can ask our webhoster to host the database on our server?

Is it correct, that we do not need a database tool, because we can edit the connected database via appsheet?

AlexM

I am using postgreSQL and you can use any recommended by JeeHill or if you want something cheaper, try Neon.
You cannot create tables or add/delete columns from Appsheet, and views in sql are better than slices in Appsheet.

Thulium

I wanted to follow your advice and asked my friend, who has a server, to install postgreSQL.

He looked at
https://support.google.com/appsheet/answer/10106598
and refused to install it for me.

Because it is not save for him, because Appsheet needs the complete login data.

I have no expertise in this. I don't know if there are alternative methods to make postgreSQL available for Appsheet.

Irrespective of this, I also checked whether the database tool DBeaver provides access to the database integrated in AppSheet. Unfortunately, no.

DBeaver offers a ER Diagram.

AlexM

Try Neon for postgresql

Thulium

Neon is to expensive with $19/month for our nonprofit organisation.

And I do not understand if I can use a local tool like dbeaver to manage the database.

AlexM

Yes, you can use it.
You can instal postgreql on your pc (straight forward process) and use pgadmin or dbeaver to manage your postgresql db.
Then you can connect it to Appsheet using your IP. Just google my ip and you'll have it

Thulium

@AlexM
Installing Postgres on my PC is not an option.
For several reasons. Two of them:
It is not permanently connected.
I have not enough knowlegde to administrate it in a save way.

Bad luck for us, that Postgres is not included as a part of google workspace for nonprofits.
And bad luck, that the standard-database of appsheet cannot be connected to a local management tool like DBeaver.

At the moment I have no idea how to go on.

MultiTech

Here's what I would advise:

MySQL hosted on GCP
Dbeaver locally used to access/manage your database

For importing into the system: it's not necessarily about getting the data into app sheet, but more about getting it into your data source.

Thulium

@MultiTech

Thanks for your advice : )

We are a nonprofit organisation (with a free nonprofit license for appsheet and goog workspace) and have no budget to pay for GCP.

Independent of that about Dbeaver:

Is that your recommendation for modelling a database with an ER diagram when you compare it to other tools?

MultiTech

@Thulium wrote:

modelling a database with an ER diagram

I'll be honest, I don't use these tools. When I first started building things I did; but found over time that it caused more confusion than was worth.

AppSheet is more of a "try it out" type of platform, vs. "plan it out" - main reason being that you won't be able to envision how things go together very well, vs. trying it out you can see it right then and there. Everything becomes extremely clear when you try something, in terms of "we need to connect this to that" type of thing.

--------------------------------------------------------------------------------------------------------------

Could you use Google Sheets for the data source? It's typically the easiest/most flexible of all the options out there.

Steve

@MultiTech wrote:

AppSheet is more of a "try it out" type of platform, vs. "plan it out"

I completely agree!

I also agree about Sheets.

Thulium

Thank you for sharing your experience.

My preference is clearly for planning rather than try it out. I hope I can still get comfortable with AppSheet.

Interesting that you both recommend sheets as a data source. I used to think that for all slightly more serious applications, the rigor and consistency of a database was essential because a spreadsheet can't provide that.

Regardless of AppSheet, however, I'm interested in databases more deeply. So I want to try both a real database and the visual tools like ER diagram. But maybe I'll come to the same conclusion as you and end up back at Sheets : )

MultiTech

Google Sheets is actually incredibly robust. It keeps a version history of literally EVERY CHANGE, and you can review this individually per cell - or the entire thing.

SQL databases don't do that. Accidentally delete something? Well... hope you had a backup! lol

I'll do my best to stay in Gsheets as long as possible, for a myriad of reasons; only finally migrating to a GCP hosted MySQL for the specific tables that are giving me problems. Things are just so much easier in Gsheets, across the board.

Happy Apping!

AlexM

I wish I can triple like this path to success :)))

Thulium

It's really helpful to hear that you so clearly prefer GSheets to a database.

In which situations do you see disadvantages of GSheets compared to a database? In which situations do you use a database instead of GSheets? Are there any criteria for this?

Thulium

@MultiTech wrote:
I'll do my best to stay in Gsheets as long as possible, for a myriad of reasons; only finally migrating to a GCP hosted MySQL for the specific tables that are giving me problems. Things are just so much easier in Gsheets, across the board.

Use cases which are unappropriate for Gsheets
Can someone please mention typical use cases where Gsheets gives problems?

About using Gsheets as Datasource
Is it correct that it is completely unimportant which data type etc. is configured in Gsheets, because the only thing that counts is what is defined as the data type in Appsheet?

How do you define a primary key in a Gsheet?

Is it correct that every linking of 2 tables is done exclusively in Appsheets via "LOOKUP()" and nothing in Gsheets?

You realize I'm still lost. Does anyone have a prototypical template for me where such things become clear? Thanks : )

Steve

@Thulium wrote:

Can someone please mention typical use cases where Gsheets gives problems?

Anything that requires speed for multiple operations will suffer with Google Sheets. To avoid running afoul of Sheets API quotas, AppSheet rate-limits row changes, so multiple pending changes will be processed slowly. This is very painful when the user is racking-up changes quickly (for instance, by doing something that adds or deletes multiple rows at once). To avoid these delays, to the best of my knowledge, an SQL database is the only option.

Is it correct that it is completely unimportant which data type etc. is configured in Gsheets

Cell number formats can have an effect, so conscious management of them can have value. On the other hand, if you format all cells as plain text, AppSheet will work just fine.

How do you define a primary key in a Gsheet?

Spreadsheets have no keys, so you don't "define a primary key in a Gsheet".

Is it correct that every linking of 2 tables is done exclusively in Appsheets via "LOOKUP()" and nothing in Gsheets?

It is correct that there is no formal link between tables in the spreadsheet; the link exists entirely within the app definition.

You realize I'm still lost.

This is so interesting to me. Coming from more of a coding background, using a spreadsheet as a data store is very natural to me. But I can see how odd it is coming from a database background.

Thulium

@Steve

Thank you for your detailed answer. Speed will not play a role at first.

Unfortunately, I have to give up my wish to use a real database as the basis of an app, properly configured, because it is too expensive.

One of the reasons behind my wish was that it would allow me to retain my independence. Because a properly set up database forms the core of an app. And if alternative software comparable to Appsheet is available in the future, you could switch if necessary.

@Steve wrote:
Cell number formats can have an effect, so conscious management of them can have value. On the other hand, if you format all cells as plain text, AppSheet will work just fine.

I don't understand that yet.

Is it completely irrelevant which data type you set in gsheets because you have to set a data type in appsheet anyway?
There are far fewer data types in gsheets than in appsheet.
See screenshot.

One little thing:
Do you use a separate sheet for each table in gsheets or do you put all tables in a single sheet?

sergio357

una consulta neon es una buena opcion para un sistema de 20 apps compartiendo informacion entre tablas y con 100 usuarios usando las 20 apps , intente hacer la conexion con supabase pero no tengo tanto recursos y las conexiones se agotaron rapidamente. me recomendaron neon pero no se si realmente funcione.