Firstly install sqlite3, npm -g install sqlite3 then:

const sqlite3 = require('sqlite3');
var yourdb = new sqlite3.Database('yourdb.sqlite3');
yourdb.all("select * from yourtable", [], function(e, row) { console.log(row) })

Sometimes it’s useful to remove all punctuation from a field.

Take the sentence It's done - and dusted, finally. & that's that.. We’ll use regexp_replace on this.

select regexp_replace(lower($$It's done - and dusted, finally. & that's that. $$),$$\sand\s|[&\.,\s'-]$$,$$$$,$$g$$)

That results in itsdonedustedfinallythatsthat. We’ve also removed the word and.

If we use It's done--and dusted, finally. & that's that. then the \sand\s matcher would fail. It’s an exercise to the reader to work a way around that.

We’ve also not dealt with $ and the like, but that’s also facile to remove using the above.

If you don’t want to enter your password in everytime you use psql, you can use a .pgpass file.

The file itself–.pgpass–is in the format YOUR_HOST:YOUR_PORT:DB_NAME:USER_NAME:PASSWORD. I.e.

localhost:5432:my_db:dave:mypasswordismylife

Importantly, you need to give the password file the 600 permission, or psql will just ignore it, so:

chmod 600 .pgpass

Then psql should, normally, just pick that up. If not do something like

PGPASSFILE=/home/dave/.pgpass psql -h localhost -U dave -d my_db 

Here’s a code sample to quickly add and query an sqlite database. There are better ways. This is the basic.

Here’s the helper that creates your database, and optionally upgrades it:

private static class DatabaseHelper extends SQLiteOpenHelper {
  private static final String SQL_CREATE_ENTRIES =
          "CREATE TABLE whoopwhoop (" +
                  "_id INTEGER PRIMARY KEY NOT NULL," +
                  "t text NOT NULL)";
  public DatabaseHelper(Context context) {
    super(context, "yourdatabasename", null, 1);
  }
  @Override public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL(SQL_CREATE_ENTRIES);
  }
  @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}
}

Next let’s use that to create the database, pass content values into it to insert and then query it.

SQLiteDatabase db = new DatabaseHelper(YOUR_APPLICATION_CONTEXT).getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("t", "woooooo");
long insert = db.insert("whoopwhoop", "null", cv);
Log.d("HIYA", ""+insert);

Here’s the code to query it. You should be using placeholder values in rawQuery to make it sql injection safe.

Cursor query = db.rawQuery("SELECT * FROM whoopwhoop", null);
query.moveToFirst();
Log.d("HIYA", ""+query.getInt(query.getColumnIndex("_id")));
Log.d("HIYA", query.getString(query.getColumnIndex("t")));

If you want to dump all the functions in your schema, public in the case, do this:

SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';

If you issue \o somefile.sql and then issue the above you’ll save the functions to a file.