I'm working on a hackathon for my local college and its my first time working with react native, I have some measurable skills in react and I want to learn new things, now I want to create a database table when a "landing page" is opened, it works, just not the way I want to. I want to create the tables once and if they exist I don't want anything to happen, but they get overwritten and then records are cleared each time, which creates frustration in order to create an account over and over again. I want to start integrating the database with my frontend and finish on the application but I'm stuck on this issue. Any ideas

import React, {Component, useEffect} from 'react';
import { NavigationContainer } from '@react-navigation/native';
import { createNativeStackNavigator } from '@react-navigation/native-stack';
import { Image, View, Text, StyleSheet, Dimensions, StatusBar, Layout, TouchableOpacity  } from 'react-native';
import Logo from '../components/assets/logo.png'    
import Login from './Login';
import { openDatabase } from 'react-native-sqlite-storage';    
import SignUp from './SignUp';
import Dash from './Dashboard';
import Reminder from './Reminder';

var db = openDatabase({ name: 'UserDatabase.db' });

function LoginPage(){

}

function Welcome({navigation}){    
    useEffect(() => {
        db.transaction(function (txn) {
          txn.executeSql(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='user' AND name='data'",
            [],
            function (tx, res) {
              console.log('item:', res.rows.length);
              if (res.rows.length == 0) {                  
                  txn.executeSql(
                    'CREATE TABLE IF NOT EXISTS user(user_id VARCHAR(75) PRIMARY KEY, Full_Name VARCHAR(75), Email VARHCAR(75), user_Password VARHCAR(75))',
                    'CREATE TABLE IF NOT EXISTS data(id INTEGER PRIMARY KEY AUTOINCREMENT, content VARCHAR(75), date VARCHAR(75), Type VARCHAR(75), Completed VARCHAR(5))',
                    []
                  );
                  alert('Created database');
                  }else {
                    alert('database failed');
                  }                
              }            
          );
        });
      }, []);
    return (
        <View style={style.root}>
            <Image source={Logo} style={style.icon} /> 
            <Text style={style.title}>Note-ify</Text>  

            <View style={style.container}>
                <Text style={style.caption}>Welcome</Text>
                <Text style={style.message}>
                    Welcome To Note-ify. The all in one platform to manage your time effectively. 
                    To begin your journey please start by logging in or signing up. ITS FREE!
                </Text>

                <View style={style.parent}>
                    <TouchableOpacity 
                        title="Login"
                        style={style.login}
                        onPress={() => navigation.navigate('Login')}>

                        <Text style={style.login_caption}>Login</Text>
                    </TouchableOpacity>

                    <TouchableOpacity 
                        title="Sign Up"
                        style={style.signup}
                        onPress={() => navigation.navigate('Register')}>
                    
                        <Text style={style.signup_content}>Sign Up</Text>
                    </TouchableOpacity>
                </View>
            
            </View>  
        </View>                     

    );
}


const Stack = createNativeStackNavigator();

function App(){
    return (
        <NavigationContainer>
            <Stack.Navigator>
                <Stack.Screen name="Welcome" component={Welcome} options={{headerShown: false}}/>
                <Stack.Screen name="Login" component={Login} options={{headerShown: false}}/>
                <Stack.Screen name="Register" component={SignUp} options={{headerShown: false}}/>
                <Stack.Screen name="Dash" component={Dash} options={{headerShown: false}} />  
                <Stack.Screen name="Reminder" component={Reminder} options={{headerShown: false}}/>          
            </Stack.Navigator>
        </NavigationContainer>
    );
}

export default App;

const style = StyleSheet.create({
    root:{
        width: Dimensions.get('window').width,
        height: Dimensions.get('window').height,
    },
    icon:{          
        alignSelf: 'center',
        justifyContent: 'center',               
        width: 100,        
        height: 100,
        marginTop: 85,
        position:'absolute',        
        zIndex:1,
    },
    title:{
        marginTop: 200,
        fontSize: 26,
        alignSelf: 'center',
        justifyContent: 'center',      
    },
    container:{
        position: 'absolute',
        backgroundColor: '#ffb900',
        width: '100%',
        height: 350,
        borderTopRightRadius: 45,
        borderTopLeftRadius: 45,
        bottom: 0,        
    },
    parent:{
        flex: 1,
        flexDirection: "row",
        justifyContent: 'center',
    },
    caption:{
        fontSize: 28,
        paddingLeft: 25,
        paddingTop: 15,
        fontWeight: 'bold',
    },
    message:{
        fontSize: 18,
        paddingLeft: 20,
        paddingTop: 20,
    },
    login:{
        width: 120,
        height: 45,
        backgroundColor: '#111',  
        marginLeft: 25,      
        marginTop: 25,
        borderRadius: 25
    },
    login_caption:{
        color: '#fff',
        textAlign: 'center',
        justifyContent: 'center',
        paddingTop: 12,        
    },
    signup:{
        width: 120,
        height: 45,
        backgroundColor: '#fff',          
        marginLeft: 25,  
        marginTop: 25,           
        borderRadius: 25
    },
    signup_content:{
        color:'#111',
        textAlign: 'center',
        justifyContent: 'center',
        paddingTop: 12,   
    }
});

CodePudding user response：

If this is your actual code then there is no way to overwrite the tables because you are using IF NOT EXISTS in the CREATE TABLE statements.

A problematic part in your code is this query:

SELECT name 
FROM sqlite_master 
WHERE type='table' AND name='user' AND name='data'

Obviously you are trying to find out if the 2 tables already exist by executing the above query, but your logic is wrong.
This query never returns a row, because there is no way that the column name would be equal to 'user' AND 'data' in the same row.

You could get results if you used the operator OR instead of AND:

SELECT name 
FROM sqlite_master 
WHERE type='table' AND (name='user' OR name='data')

but, this would return 0 or 1 or 2 rows depending on the existence of either of the tables.

In any case, remove this query because it is not needed at all.

All you need is the 2 CREATE TABLE IF NOT EXISTS... statements and nothing more.

Also, check if by any chance, there is code that deletes the database, so when you restart the app, the database is recreated.